October 10, 2008 at 4:21 pm
I posted this in reply to a reply to another post and it got 0 response which is very unusual and because of that I'm hoping that posting it again as the main topic item and not as a reply will get more feedback because I am at a loss with DM_DB_INDEX_PHYSICAL_STATS()and index fragmentation.
I use the DMV DM_DB_INDEX_PHYSICAL_STATS()to get Fragmentation info on indexes throughout the DB. I use the command ALTER INDEX idx_MtyIndex ON TableName REBUILD... to rebuild the indexes.
The problem is that I can't tell if the Indexes are actually getting rebuilt. Idid found out from a reply to my last post that if the table is small then the index is not rebuilt because, well for performance vs size reasons. In any event, I tested this next against the largest table I have that consists of millions of rows of data and the table alone is several hundread megebytes in size. Therefore the indexes on this table should be large enough to warrant getting rebuilt.
When I run the DMV DMV DM_DB_INDEX_PHYSICAL_STATS() and look at a single index on this table there are 4 rows retruned by this DMV for this 1 index. The only difference in each of the 4 rows aside from the fragmentation specific items is the Index Level. Each of the 4 rows from this DMV has a value within the set 0,1,2 & 3 so that each row has a different INdex_Level value and they range between 0 and 3.
The fragmentation percentage in each of these is drastically different. For example one row will have %0 frag while another has perhaps %1 and the last 2 will have very high values like %89 & %95. I've even had some indexes have frag values of %100.
My problem is that these frag values do not change when I run the ALTER INDEX REBUILD command.
Now I have not tested this on all the tables or indexes, just those on the largest tables. But if I can't get the Index fragmentation fixed on these large tables then the rest of the tables indexes are irrelevant.
Any idea why I may be having this problem? What I don't understand is:
1) How can the DMV return 4 rows of info on each index
2) Why the frag values returned by the DMV do not change even after the ALTER INDEX REBUILD command has been run.
Note: When I run the Rebuild command, the thing does take several minutes to run so I know something is being doen.
PLease help! BOL seems to have nothing in the pages on this DMV about this multlpe rows per Index problem.
Thanks!
Kindest Regards,
Just say No to Facebook!October 11, 2008 at 8:35 am
Look in Books Online for the index architecture. You'll notice that the index is in the form of a tree. The 4 rows refer to different levels of the index. Level 0 is the leaf level (the lowest level of the index with the most pages) and the highest level refers to the root (which will only have 1 page)
There should be an entry in there showing you the number of pages for each level. It should confirm what I said
I would guess that what you're seeing is 0% at the leaf level (meaning there's no fragmentation of the index leaf level), but have higher % at the higher levels. Same reason (kinda) as why smaller tables (< 100 pages) show fragmentation even after a rebuild. There will be very few pages at the higher levels, and hence the fragmentation is harder to remove. Same recommendations as for smaller tables - don't worry about it, the higher levels of the index (especially the root and the level below that) will almost always be in memory if the table is used, and hence the fragmentation doesn't matter much.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 13, 2008 at 10:50 am
Gail,
Thank you for the reply and info. I'm reading thru more in BOL about Indexes in general but it's not made very clear or at least not easily clear, how to use the DMV DM_DB_INDEX_PHYSICAL_STATS to determine fragmentation. It would be nice if the BOL page that defines this function included sample code on how to handle this when multiple rows for the same index are returned; something about the Leaf level and pages like you've mentioned, but at a greater level of detail is needed.
Thanks again
Kindest Regards,
Just say No to Facebook!Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply