Defragmenting indexes

The Transact-SQL DBCC INDEXDEFRAG statement defragments indexes of a specified table, improving index-scanning performance.

DBCC INDEXDEFRAG (database_name, table_name, index_name)

The script below uses DBCC INDEXDEFRAG and DBCC SHOWCONTIG to defragment all indexes in a database fragmented above a declared threshold of 30 percent. The script is from Microsoft’s Transact-SQL Reference, copyright © 2004 Microsoft Corporation, One Microsoft Way, Redmond, Washington 98052-6399 U.S.A.; all rights reserved.

Note that you must specify a database before you run the script.

/*Perform a 'USE <database name>' to select the database in which to run the script.*/
-- Declare variables
SET NOCOUNT ON
DECLARE @tablename VARCHAR (128)
DECLARE @execstr  VARCHAR (255)
DECLARE @objectid INT
DECLARE @indexid  INT
DECLARE @frag   DECIMAL
DECLARE @maxfrag  DECIMAL

-- Decide on the maximum fragmentation to allow
SELECT @maxfrag = 30.0

-- Declare cursor
DECLARE tables CURSOR FOR
  SELECT TABLE_NAME
  FROM INFORMATION_SCHEMA.TABLES
  WHERE TABLE_TYPE = 'BASE TABLE'

-- Create the table
CREATE TABLE #fraglist (
  ObjectName CHAR (255),
  ObjectId INT,
  IndexName CHAR (255),
  IndexId INT,
  Lvl INT,
  CountPages INT,
  CountRows INT,
  MinRecSize INT,
  MaxRecSize INT,
  AvgRecSize INT,
  ForRecCount INT,
  Extents INT,
  ExtentSwitches INT,
  AvgFreeBytes INT,
  AvgPageDensity INT,
  ScanDensity DECIMAL,
  BestCount INT,
  ActualCount INT,
  LogicalFrag DECIMAL,
  ExtentFrag DECIMAL)

-- Open the cursor
OPEN tables

-- Loop through all the tables in the database
FETCH NEXT
  FROM tables
  INTO @tablename

WHILE @@FETCH_STATUS = 0
BEGIN
-- Do the showcontig of all indexes of the table
  INSERT INTO #fraglist 
  EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''') 
   WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
  FETCH NEXT
   FROM tables
   INTO @tablename
END

-- Close and deallocate the cursor
CLOSE tables
DEALLOCATE tables

-- Declare cursor for list of indexes to be defragged
DECLARE indexes CURSOR FOR
  SELECT ObjectName, ObjectId, IndexId, LogicalFrag
  FROM #fraglist
  WHERE LogicalFrag >= @maxfrag
   AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0

-- Open the cursor
OPEN indexes

-- loop through the indexes
FETCH NEXT
  FROM indexes
  INTO @tablename, @objectid, @indexid, @frag

WHILE @@FETCH_STATUS = 0
BEGIN
  PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@tablename) + ',
   ' + RTRIM(@indexid) + ') - fragmentation currently '
    + RTRIM(CONVERT(varchar(15),@frag)) + '%'
  SELECT @execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@objectid) + ',
    ' + RTRIM(@indexid) + ')'
  EXEC (@execstr)

  FETCH NEXT
   FROM indexes
   INTO @tablename, @objectid, @indexid, @frag
END

-- Close and deallocate the cursor
CLOSE indexes
DEALLOCATE indexes

-- Delete the temporary table
DROP TABLE #fraglist
GO