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%. :( |