Defragmenting/Rebuilding Indexes in SQL Server 2005 and 2008
I have modified isp_ALTER_INDEX, which is the stored procedure that I use to defragment/rebuild indexes in SQL Server 2005 and 2008. The new version has a minor bug fix.
Recently I was comparing the fragmentation results between the DETAILED and SAMPLED modes of sys.dm_db_index_physical_stats and noticed that the DETAILED mode had “duplicate” entries for indexes. Due to this, my old code would have ran ALTER INDEX against the same index multiple times if you used DETAILED for @statsMode. To fix this, I simply added “index_level = 0” to the WHERE clause of the sys.dm_db_index_physical_stats query.
I consider this to be a minor bug as most people can not afford to run sys.dm_db_index_physical_stats in DETAILED mode.
You can download the new version of the stored procedure here. Let me know if you run into any issues with it.
Legacy Comments
|
Scott Whigham
2009-09-03 |
re: Defragmenting/Rebuilding Indexes in SQL server 2005 and 2008 I love it - thanks for sharing. One thing I've seen is that my avg_fragmentation_in_percent can sometimes exceed 100! You might want to mod the script as such if you've seen that; I've only seen it on two instances that were both running copies of the same database though. Have you ever seen that? I've not seen it go over 101.xx though. |
|
Tara
2009-09-03 |
re: Defragmenting/Rebuilding Indexes in SQL server 2005 and 2008 Scott, I've never seen even 100 as a value and I'm running hundreds of instances. I wonder if DBCC UPDATEUSAGE will fix the data for you. May want to give that a try. ~Tara |