October 8, 2008 at 5:10 pm
I'm using the 2005 DMV sys.DM_DB_INDEX_PHYSICAL_STATS() to read the level of fragmentation in a table's Indexes to determine if they need rebuilding.
I then use the ALTEX INDEX .... REBUILD ... command to rebuld the indexes and while the command does run, it appears to have done little rebuilding. IN the case of one small table, approximately 1200 rows, the fragmentation on 2 non-clustered indexes are 80 & 75 per the DMV sys.DM_DB_INDEX_PHYSICAL_STATS() . When I run the Rebuild command for that table, nothing happens. A re-check of the DMV shows the fragmentation is still the same value as before.
Any idea how this would happen and or why? Maybe I'm a nut for assumng this but I thought the rebuild would get the fragmentation down to at least less then 10 percent.
Ideas, Comments, Thoughts, Insults?
Thanks
Kindest Regards,
Just say No to Facebook!October 8, 2008 at 6:25 pm
The table is too small. No - really. There is something about the rebuild process that makes it essentially not work hardly at all on small tables, essentially because they don't benefit from it.
Paul Randal (who at one point ran the team that put these things together in SQL Server) [reviously mentioned it wouldn't do much on tables that are <1000 pages in size, since the entire index would be loaded into memory anyway.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 9, 2008 at 8:18 am
Thanks for the info Matt; I would have never thought of that even though it makes a lot of sense.
Kindest Regards,
Just say No to Facebook!October 9, 2008 at 10:10 am
OK here is one for you with regards to the table being to small.
I am seeing the same problem on a table that has millions of rows and is hundreads of MB in size. When I use the DMV to get the fragmentation value it shows 4 rows for the same index with the difference between them being the fragmentation value and the Index level. THe Index Level for the 4 rows for this index are 0,1,2 & 3 with the frag values being 0, 3, 100 & 0 percent respectively. Now how can I tell the true fragmentation value of this index with 4 different rows and each showing a different value? I assume it has to do with the Index Level but nothing in BOL 2005 says anything with regards to that nor that this DMV will give multiple rows per index.
Any ideas?
Thanks
Kindest Regards,
Just say No to Facebook!Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply