Tara Kizer Blog

Tara Kizer

DBCC INDEXDEFRAG stored procedure

EDIT: This stored procedure has been updated.

The below stored procedure runs DBCC INDEXDEFRAG for each of the indexes in the user database that is inputted into the sproc.  I recommend putting it into an Admin database (hey just name it Admin!) rather than in master.  Then just schedule isp_DBCC_INDEXDEFRAG for each of the databases you want defragged.  Remember that DBCC INDEXDEFRAG isn't as good as DBCC DBREINDEX in large scale environments (for more information on this, see this article), so see my DBCC DBREINDEX weblog coming soon.  It'll be better than the other one I posted a while back.

CREATE  PROC isp_DBCC_INDEXDEFRAG
(@dbName SYSNAME)
AS
SET NOCOUNT ON

DECLARE @objID INT
DECLARE @idxName SYSNAME
DECLARE @SQL NVARCHAR(4000)

SET @objID = 0

SET @SQL = ''
SET @SQL = @SQL + 'SELECT i.id, i.name '
SET @SQL = @SQL + 'INTO ##Indexes '
SET @SQL = @SQL + 'FROM ' + @dbName + '.dbo.sysindexes i '
SET @SQL = @SQL + 'INNER JOIN ' + @dbName + '.dbo.sysobjects o '
SET @SQL = @SQL + 'ON i.id = o.id '
SET @SQL = @SQL + 'WHERE indid > 0 AND indid < 255 AND '
SET @SQL = @SQL + 'o.type = ''U'' AND '
SET @SQL = @SQL + '(i.status & (64 | 8388608)) <= 0'

EXEC sp_executesql @statement = @SQL

WHILE @objID < (SELECT MAX(id) FROM ##Indexes)
BEGIN

 SELECT TOP 1 @objID = id, @idxName = name
 FROM ##Indexes
 WHERE id > @objID
 ORDER BY id

 SET @SQL = 'DBCC INDEXDEFRAG(' + @dbName + ', ' + CONVERT(VARCHAR(50), @objID) + ', ' + @idxName + ') WITH NO_INFOMSGS'

 EXEC sp_executesql @statement = @SQL

END

DROP TABLE ##Indexes

RETURN 0

 


GO

 

Legacy Comments


Seventhnight
2004-06-23
re: DBCC INDEXDEFRAG stored procedure
Here is what I use for index defrag/recreate:

CREATE PROCEDURE dbo.ManageIndex
@Action nvarchar(100),
@curTable nvarchar(100),
@showMsgs bit
AS

Declare @curDb nvarchar(100)

Set @curDb = 'yourDb'

Set NoCount On
Declare @curId int,
@curIndex int

Set @curId = (Select Id From sysobjects where xtype='U' and Name = @curTable)
-- Drop table #tempIndexes
Create Table #tempIndexes ([Index] nvarchar(100), [Column] nvarchar(100), KeyNo int)

if (@Action = 'Primary')
Begin
Insert Into #tempIndexes
Select
[Index] = sysindexes.name,
[Column] = (Select name from syscolumns where id = sysindexes.Id and colId = sysindexkeys.colId),
KeyNo
From sysindexes
Inner Join sysindexkeys
On sysindexes.Id = sysindexkeys.Id
and sysindexes.indId = sysindexkeys.indId
Where sysindexes.Id = @curId and Rows>0 and Status = 18450
Order By sysindexes.indId, KeyNo
End
Else
Begin
Insert Into #tempIndexes
Select
[Index] = sysindexes.name,
[Column] = (Select name from syscolumns where id = sysindexes.Id and colId = sysindexkeys.colId),
KeyNo
From sysindexes
Inner Join sysindexkeys
On sysindexes.Id = sysindexkeys.Id
and sysindexes.indId = sysindexkeys.indId
Where sysindexes.Id = @curId and Status = 0
Order By sysindexes.indId, KeyNo

End

-- Select * From #tempIndexes

-- Drop Table #ProcessIndexes
Select [Index], cnt = count(*) Into #ProcessIndexes From #TempIndexes Group By [Index]
Select * From #ProcessIndexes

-- Create Table #temp ([Pages Scanned] int, [Pages Moved] int, [Pages Removed] int)

Declare @counter int,
@processIndex nvarchar(100),
@curCnt int,
@curColumn nvarchar(100),
@cmdStr nvarchar(1000)
While exists(Select * from #ProcessIndexes)
Begin
Set @counter = 0
Select
@processIndex = [Index],
@curCnt = Cnt
From #ProcessIndexes

If (@action = 'Drop/Create')
Begin

Set @cmdStr = 'Drop Index ' + @curTable + '.' + @processIndex
-- Select cmdStr = @cmdStr
Execute(@cmdStr)

Set @cmdStr = 'Create Index ' + @processIndex + ' On ' + @curTable + ' ('
While @counter < @curCnt
Begin
set @counter = @counter + 1
Set @cmdStr = @cmdStr + (Select [column] From #TempIndexes Where [Index] = @processIndex and KeyNo = @counter) + ','
End
Set @cmdStr = left(@cmdStr,len(@cmdStr)-1) + ')'

-- Select cmdStr = @cmdStr
Execute(@cmdStr)
End

If (@action = 'Defrag')
Begin
Set @cmdStr = 'DBCC INDEXDEFRAG (' + @curDb + ',' + @curTable + ',' + @processIndex + ')'
if (@showMsgs = 0)
Begin
Set @cmdStr = @cmdStr + ' WITH NO_INFOMSGS'
End
-- Select cmdStr = @cmdStr

Execute(@cmdStr)
End

If (@action = 'Primary')
Begin
Set @cmdStr = 'DBCC INDEXDEFRAG (' + @curDb + ',' + @curTable + ',' + @processIndex + ')'
if (@showMsgs = 0)
Begin
Set @cmdStr = @cmdStr + ' WITH NO_INFOMSGS'
End
-- Select cmdStr = @cmdStr

Execute(@cmdStr)
End


Delete From #ProcessIndexes Where [Index] = @processIndex
End
Set NoCount Off
GO

And I loop through the tables with:

CREATE PROCEDURE dbo.ManageTables
@Primary bit = 0,
@DefragAll bit = 0,
@DropCreateAll bit = 0
As

Insert Into SP_Log (spid) Select SPId = @@ProcId


Select tableName = Name Into #tableList From sysobjects Where xtype='U' and Status > 0

Declare @curTable nvarchar(100)

While exists(Select * From #tableList)
Begin

Set @curTable = (Select top 1 tableName From #tableList)

if (@Primary=1)
Begin
exec ManageIndex 'Primary', @curTable, 1
End

if (@DefragAll=1)
Begin
exec ManageIndex 'Defrag', @curTable, 1
End

if (@DropCreateAll=1)
Begin
exec ManageIndex 'Drop/Create', @curTable, 1
End

Delete From #tableList Where tableName = @curTable
End
GO

Corey

Pat Wright
2004-06-25
re: DBCC INDEXDEFRAG stored procedure
I'd have two suggestions in this. This is just my opinion so you can ignore me if you want. :)

1. On the first query you can use a REplace on your @SQL instead of having to add them all together it might be easier for you so it might just be a style but something like this will work also. I find it's a little easier to not have to worry about where the '' are.

Set @cmd = 'Select <column> from <table>'
Set @cmd = replace(@cmd,'<column>',@column)
Set @cmd = replace(@cmd,'<table>',@table)

2. I use a showcontig to decide if it needs to be defraged and i store what the results were so in the future i know how often it's happening. You'd have to email me if you want a copy of the proc it's to long to paste in. patrick.wright@aruplab.com
3. you should avoid hitting system tables if possible if microsoft changes something about them in a release it can break your code.

Love the blog thanks!

pat

Tara
2004-06-25
re: DBCC INDEXDEFRAG stored procedure
2. I've got one similar. But thanks for the offer
3. Yes I am very aware of this. I often warn people about it when replying in forums. This information just isn't stored anywhere else. I would obviously test it out if I moved it to 2005.

Terry Crosby
2004-07-26
re: DBCC INDEXDEFRAG stored procedure
Would you happen to have a script that looks at the percent of fragmentation and either does a DEFRAG or a REINDEX?

Tara
2004-07-26
re: DBCC INDEXDEFRAG stored procedure
No I don't.

Pete Adrian
2005-12-15
re: DBCC INDEXDEFRAG stored procedure
does anyone have a stored proc or script that will run through the dbcc showcontig and will dbcc indexdefrag looping thorugh tables but also will truncate the transaction log after each table is indexdefrag

peteadrian@cox.net

andru123
2006-06-23
Does INDEXDEFRAG actually work?
Hi,

I executed the following on my DB:
dbcc showcontig ('spt_values')

DBCC SHOWCONTIG scanning 'spt_values' table...
Table: 'spt_values' (85575343); index ID: 1, database ID: 1
TABLE level scan performed.
- Pages Scanned................................: 5
- Extents Scanned..............................: 2
- Extent Switches..............................: 3
- Avg. Pages per Extent........................: 2.5
- Scan Density [Best Count:Actual Count].......: 25.00% [1:4]
- Logical Scan Fragmentation ..................: 20.00%
- Extent Scan Fragmentation ...................: 0.00%
- Avg. Bytes Free per Page.....................: 502.0
- Avg. Page Density (full).....................: 93.80%

Then I do:
DBCC INDEXDEFRAG (0, spt_values,1)

Pages Scanned Pages Moved Pages Removed
------------- ----------- -------------
0 0 0

(1 row(s) affected)


Nothing changes! It still says the logical fragmentation is 20%.

:(