How can I defragment database indexes on SQL 2000?

Products

Management Platform (Formerly known as Notification Server)

Issue/Introduction

Resolution

Question
My Altiris Notification Server database is under performing (it runs slower). How can I defrag my database indexes to improve performance?

Answer

Update: A read-only Notification Server report (SQL Index Fragmentation.xml) is now available (see attachments on the right side of this article). The report will indicate if significant fragmentation exists. It does not require SQL knowledge to execute. Note: Be aware that running this report is very intense on SQL and should only be run off hours and not during any Notification Server maintenance windows.

For long term remediation, please utilize the SQL Server maintenance wizard to create a weekly maintenance task to perform an index rebuild.

Sample report screenshot

SQL query for Index Defrag
The script provided below is primarily intended for a quick one-time fix. As indexes will re-fragment over time, a regularly scheduled maintenance task is the best solution.

Before running this script, back up your database (just in case); however, this script should not damage anything. It should only remove empty spaces in the database. Read the article about DBCC SHOWCONTIG (http://msdn2.microsoft.com/en-us/library/aa258803(SQL.80).aspx), and you will find more information about this script. The process will take few minutes, since the database is large, but if you want to run the defrag with less percentage of increase, change SELECT @maxfrag = 30.0 to 10.0 or something like that.

/*
/   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        numeric(8,3)
DECLARE @maxfrag  numeric(8,3)

-- Decide on the maximum fragmentation to allow
-- Below is the percentage the index has to be fragmented
-- Suggest to start at 30.0 (30%)
SELECT @maxfrag = 30.0

-- Declare cursor
DECLARE tables CURSOR FOR
   SELECT TABLE_NAME
   FROM INFORMATION_SCHEMA.TABLES
   WHERE TABLE_TYPE = 'BASE TABLE'
-- Do not scan common table indexes
        AND TABLE_NAME NOT LIKE 'xdl%'
        AND TABLE_NAME NOT LIKE 'Prf_%'

-- Create the table
CREATE TABLE #fraglist (
   ObjectName varchar (255),
   ObjectId int,
   IndexName varchar (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 numeric(8,3),
   BestCount int,
   ActualCount int,
   LogicalFrag numeric(8,3),
   ExtentFrag numeric(8,3)

)

-- 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

If you are using SQL 2005, please refer to KB for an updated method of accomplishing this:

How can I reorganize or rebuild my Altiris database indexes on a SQL 2005 server for improved performance?
http://www.symantec.com/business/support/index?page=content&id=HOWTO4644

Attachments

SQL Index Fragmentation.xml get_app