Ramblings of a DBA

Tara Kizer
posts - 166, comments - 835, trackbacks - 75

My Links

Advertisement

News

Subscribe
Search this Blog

Archives

Post Categories

Work

August 2009 Blog Posts

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...

posted @ Monday, August 31, 2009 9:50 AM | Feedback (2) |

Powered by:
Powered By Subtext Powered By ASP.NET