In my previous post, I used a rather small table. Clustered index containing 7 rows, 7 data pages + 1 index page. Optimally 1 extent, though not always placed that way. Nonclustered index also of 8 total pages. Optimally 1 extent, but not always placed that way.
You can find that adventure here.
I got a hunch about SQL Server fragmentation Part III
If you want to continue after this post, keep going here.
I got a hunch about SQL Server fragmentation Part V
Let's crank it up a notch, shall we?
4088 data pages may seem like a mighty odd choice, especially given the total number of allocated pages at 4103. But I got my reasons :-)
First note that the avg_fragmentation_in_percent is equal to 14/4088 - the number of level 1 index pages divided by the number of level 0 or data pages.
15 fragments for almost 32mb isn't bad, but I think I can do better.
Yep, that's even better... at least according to what's reported in sys.dm_db_index_physical_stats. I've never been one to stop at "better". After playing around a bit, I found that my best bet for lowest reported fragmentation in a clustered index is to rebuild it without the online option.
I've got to admit, I like the looks of that. Exactly 4096 pages. 0% reported avg_fragmentation_in_percent! Pretty cool.
The reported 0% fragmentation surprised me - I was expecting at least 7/4088 or .17%. Something interesting is happening here.
Well - how about a full scan?
Not bad. 68 reads, 33619968 bytes read - 4104 8k pages.
2 physical reads reported by STATISTICS IO, leaving 66 read operations to cover the 4088 read-ahead pages.
Now, if I were really an expert I'd go straight to my "gaps and islands" query of sys.dm_db_database_page_allocations to show each contiguous range of pages and reconstruct the reads. I'd probably also use procmon to capture each read as an individual call with file offset.
But its late and I'm not all that good at those things. Maybe 3 or 4 posts from now :-)
Lets add a nonclustered index.
Pretty good. Not as good as the clustered index. What if we create the index without the online option?
Now if we do a full scan of the nonclustered index?
68 reads. 33619968 bytes read. 2 physical reads reported by Statistics IO, leaving 66 reads to cover the 4088 read-ahead pages reported. Exactly the same for the nonclustered index as for the clustered index.
So - for today we've seen that the ONLINE option for CREATE INDEX and ALTER INDEX REBUILD can have a slight effect on fragmentation as reported by sys.dm_db_index_physical_stats. If a clustered index is rebuilt without ONLINE = ON, it may have a little bit of fragmentation which may be eliminated by a rebuild with ONLINE = ON. OTOH, creating a nonclustered index seems to be most optimal without ONLINE = ON.
And we've seen that its possible to have a 32 mb clustered index AND a 32 mb nonclustered index which both report 0% average fragmentation via sys.dm_db_index_physical_stats.