Skip to content

New Index Defrag Script

February 22, 2010

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

USE DatabaseName;
GO
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
GO

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

Advertisements

From → Indexing

Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: