Defragmentation script for SQL Server

When you SQL server database becomes slower over time without apparent reasons, probably you’re facing a fragmentation problem. Database table fragmentation is one of the major performance killers.

With the following maintenance script you defrag you tables and indexes.

-- =============================================
-- Copyright by Author Sajal Dam, ISBN 1590594215 
-- Description: Performs defragmentation actions
-- tables and indexes in your database.
-- Execute this script once a month or more often 
-- if necessary
-- =============================================
CREATE PROC [dbo].[pr_Sys_DefragDatabase]
--Create temporary table to hold DBCC SHOWCONTIG output
CREATE TABLE #FragmentationResult(
    ObjectName VARCHAR(255), ObjectId INT, IndexName VARCHAR(255),
    IndexId INT, [Level] INT, Pages INT, [Rows] INT,
    MinimumRecordSize INT, MaximumRecordSize INT,
    AverageRecordSize FLOAT, ForwardedRecords INT, Extents INT,
    ExtentSwitches INT, AverageFreeBytes FLOAT,
    AveragePageDensity FLOAT, ScanDensity FLOAT, BestCount INT,
    ActualCount INT, LogicalFragmentation FLOAT,
    ExtentFragmentation FLOAT
--Create temporary table to hold tables/indexes that require
-- defragmentation
CREATE TABLE #Defragmentation(
    [id] INT IDENTITY,
    ObjectName VARCHAR(255),
    IndexName VARCHAR(255),
    ScanDensity FLOAT

--Identify all user tables in the current database to analyze
-- fragmentation
SELECT as [id], as [name] INTO #UserTables
    FROM sysobjects SO
    INNER JOIN sysusers SU
    ON SO.uid = SU.uid
    WHERE type = 'U'
    AND = 'dbo' 
    ORDER BY [id]
--Determine fragmentation of every user table/index
DECLARE @id INT, @name VARCHAR(255), @TableCnt INT
SET @id = 0
SELECT @TableCnt = COUNT(*) FROM #UserTables
    WHILE @TableCnt > 0
        SELECT TOP 1 @id=[id], @name=[name]
        FROM #UserTables
        WHERE [id] > @id
        INSERT INTO #FragmentationResult
        EXEC('DBCC SHOWCONTIG([' + @name + '])
        SET @TableCnt = @TableCnt - 1
--Determine user tables/indexes that require defragmentation
INSERT INTO #Defragmentation
SELECT ObjectName, IndexName, ScanDensity
    FROM #FragmentationResult
    WHERE ScanDensity < 50 --Scan Density is low
    AND LogicalFragmentation > 9 --Logical Scan Fragmentation is high
    AND PAGES > 8 --Not a very small table
DROP TABLE #FragmentationResult

--Defragment tables/indexes with high fragmentation
DECLARE @oname VARCHAR(255), @iname VARCHAR(255), @sdensity FLOAT
SET @id = 0
SELECT @TableCnt = COUNT(*) FROM #Defragmentation
WHILE @TableCnt > 0
    SELECT TOP 1 @id=[id], @oname = ObjectName, 
        @iname = IndexName, @sdensity = ScanDensity
        FROM #Defragmentation
        WHERE [id] > @id
    PRINT '** De-fragmentation #' + CAST(@id AS VARCHAR(15))+ ' **'
    PRINT 'DBCC DBREINDEX on [' + @oname + '].[' + @iname
            + '] with ScanDensity = ' + CAST(@sdensity AS VARCHAR(15)) + '%'
    DBCC DBREINDEX(@oname, @iname)
    SET @TableCnt = @TableCnt - 1

--Release resources
DROP TABLE #UserTables
DROP TABLE #Defragmentation


Hope this help,

Add comment