WinTill :: Knowledge Base



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_tblfetch




Related Articles

Attachments

No attachments were found.

Visitor Comments

Article Details

Last Updated
7th of March, 2011

Would you like to...

Print this page  Print this page

Email this page  Email this page

Post a comment  Post a comment

 Subscribe me

Subscribe me  Remove from favorites

Remove Highlighting Remove Highlighting

Edit this Article

Quick Edit

Export to PDF


User Opinions

No users have voted.

How would you rate this answer?




Thank you for rating this answer.

Continue