September 13, 2011 at 12:55 am
One of my database table's index fragmentation is 86% and I am trying to rebuild the index, so that it could be defrag. But after running REBUILD WITH(FILLFACTOR=80) using ALTER INDEX, its fragmentation is still upto 70%. Same case is for some other indexes also.
Please tell me the solution to completely defrag an index.
September 13, 2011 at 12:57 am
Alter index ... Rebuild is how you remove fragmentation.
Let me guess, it's a tiny index, probably under 10 pages in size?
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
September 13, 2011 at 1:19 am
yes...Page count for this index is 5
September 13, 2011 at 3:00 am
Way too small to care about. The generally recommended rough guide to the size of index you need to worry about for rebuilding indexes is 1000 pages.
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
September 13, 2011 at 4:16 am
That's mean I need to check both avg_fragmentation_in_percent and page_count in records returned by sys.dm_db_index_physical_stats().
September 13, 2011 at 4:20 am
Yup. Or you can get a nice pre-written script that already does all of that, like the one at http://sqlfool.com/
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply