Automating DBCC INDEXDEFRAG

Shankar Manamalkav

This is the stored procedure that I use to keep the indexes in my databases squeaky clean. It has been fairly well documented.

Click on the following link for more details regarding the Sql Server INDEXPROPERTY function

Also, read the following Microsoft article regarding Index defragmentation to get a better idea about what goes on behind the scenes.

CREATE proc dbo.MyIndexDefrag
AS

–Declare local vars
Declare @TableName varchar(50), @IndexName varchar(50)
declare @SQL nvarchar(200)
declare @rc int

–sql explanation:
–unfortunately, indexes cannot be gathered from the INFORMATION_SCHEMA.. so query system tables sysindexes and sysobjects
–note that this proc assumes there are no text column indexes (indid = 255). They are external to db and cannot be defragged. To exclude them use filter ‘AND (i.indid255)’
–o.type=’U’ restricts our result to ‘User’ databases
–Use IndexProperty to remove system generated indexes from the operation
–remember to remove dtproperties from the resultset.. dtproperties is created by SQL server as a user database to store info regarding db diagrams

Declare C1 cursor for
SELECT o.name as TableName, i.name as IndexName
FROM dbo.sysindexes i
INNER JOIN dbo.sysobjects o
ON i.id = o.id
WHERE
o.type=’U’ AND
INDEXPROPERTY(i.id, i.name, ‘IsStatistics’) = 0 AND
INDEXPROPERTY(i.id, i.name, ‘IsHypothetical’) = 0 AND
o.name<>’dtproperties’

–Open the cursor
OPEN c1

–Fetch initial row
FETCH NEXT FROM c1
INTO @TableName, @IndexName

–Iterate while there are rows to process
WHILE @@FETCH_STATUS = 0
BEGIN

–dynamically generate the DBCC indexdefrag statement
–Note that DBCC indexdefrag is preferred to DBCC dbreindex

SET @SQL = ‘DBCC INDEXDEFRAG(‘ + db_name() + ‘,[‘ + @TableName + ‘], ‘ + @IndexName + ‘) WITH NO_INFOMSGS’

–print @SQL
–Execute the statement against the database
exec @rc = sp_executesql @SQL

–Close the loop
FETCH NEXT FROM c1
INTO @TableName, @IndexName
END

–free up resources. Extremely important with server side cursors
CLOSE c1
DEALLOCATE c1
GO

Advertisements

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