-- =============================================
-- 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]
AS
SET NOCOUNT ON
--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 SO.id as [id], SO.name as [name] INTO #UserTables
FROM sysobjects SO
INNER JOIN sysusers SU
ON SO.uid = SU.uid
WHERE type = 'U'
AND SU.name = '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
BEGIN
SELECT TOP 1 @id=[id], @name=[name]
FROM #UserTables
WHERE [id] > @id
INSERT INTO #FragmentationResult
EXEC('DBCC SHOWCONTIG([' + @name + '])
WITH ALL_INDEXES, TABLERESULTS')
SET @TableCnt = @TableCnt - 1
END
--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
BEGIN
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
END
--Release resources
DROP TABLE #UserTables
DROP TABLE #Defragmentation
SET NOCOUNT OFF
|