New Index Defrag Script
I will be testing a great new index defrag script I found on Michelle Uffords Blog http://sqlfool.com/2009/06/index-defrag-script-v30/. I edited it to include “SortInTempDB=On” because I feel this will minimize logging and thus the growth of my log file due to reindexing.
I ran a DMV script to get the name and current fragmentation of my indexes prior to running the script by Joining sys.dm_db_index_physical_stats with dbo.sysobjects to get object name
SELECT name,* FROM sys.dm_db_index_physical_stats
(DB_ID(N’DatabaseName’), NULL, NULL, NULL , ‘Limited’)
join dbo.sysobjects on sys.dm_db_index_physical_stats.object_id=dbo.sysobjects.id
where page_count > 8 and avg_fragmentation_in_percent > 30
order by avg_fragmentation_in_percent desc
I’ll dump the results in a table in my DBAStuff database, run the SP and rerun the fragmentation analysis to see improvement.
I’ll also track the log file usage and growth from running the reindex script
The Script I will use to monitor log file usage is DBCC SQLPERF(logspace)
I will review log file usage http://www.mssqltips.com/tip.asp?tip=1225