The article has been updated successfully.Defragmenting Database Table Indexes
SQL Server 2000 introduced a new DBCC INDEXDEFRAG statement to defragment clustered and nonclustered indexes on tables and views. This statement defragments the leaf level of the index so that the physical order of the index pages match the left-to-right logical order of the leaf nodes. The DBCC INDEXDEFRAG statement will report to the user an estimated percentage completed every five minutes and can be terminated at any point in the process, so that any completed work is retained.The main advantage of using DBCC INDEXDEFRAG in comparison with DBCC DBREINDEX or with dropping and re-creating indexes is that the DBCC INDEXDEFRAG is an online operation. This means that the DBCC INDEXDEFRAG statement does not hold locks for a long time and thus will not block any running queries or updates. As the time to defragment is related to the amount of fragmentation, you can use the DBCC INDEXDEFRAG statement to reduce fragmentation if the index is not very fragmented. For a very fragmented index, rebuilding (using DBCC DBREINDEX statement) can take less time.
N.B. Though the DBCC INDEXDEFRAG statement is an online operation, try to schedule it during CPU idle time and slow production periods as other maintenance tasks.
Use the following SQL script to defragment the table indexes in the specified database:
USE
DECLARE @TableName sysname
DECLARE @indid int
DECLARE cur_tblfetch CURSOR FOR
SELECT table_name FROM information_schema.tables WHERE table_type = 'base table'
OPEN cur_tblfetch
FETCH NEXT FROM cur_tblfetch INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE cur_indfetch CURSOR FOR
SELECT indid FROM sysindexes WHERE id = OBJECT_ID (@TableName) and keycnt > 0
OPEN cur_indfetch
FETCH NEXT FROM cur_indfetch INTO @indid
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT 'Derfagmenting index_id = ' + convert(char(3), @indid) + 'of the '
+ rtrim(@TableName) + ' table'
IF @indid <> 255 DBCC INDEXDEFRAG (pubs, @TableName, @indid)
FETCH NEXT FROM cur_indfetch INTO @indid
END
CLOSE cur_indfetch
DEALLOCATE cur_indfetch
FETCH NEXT FROM cur_tblfetch INTO @TableName
END
CLOSE cur_tblfetch
DEALLOCATE cur_tblfetchRelated Articles
Attachments
No attachments were found.Visitor Comments
Article Details
Last Updated
7th of March, 2011
