Binaryworld - Selective Defrag/Reindex and Log ... [ SQL Server -> Server Operations ]

SQL 2000 Only

This script will create a stored procedure and a table in the master database. Simply run the stored procedure and supply a database name as a parameter. It will do the following:

1. Gather/Log DBCC SHOWCONTIG statistics (pre defrag)
2. Defragment all user indexes in the database
3. Gather/Log DBCC SHOWCONTIG statistics (post defrag)
4. DBCC DBREINDEX for all indexes that have %10 logical fragmentation
5. Gather/Log DBCC SHOWCONTIG statistics for reindexed tables.

All activity is logged in [master].[dbo].[fragreport]

You can create a job to schedule this and use the fragreport to identify problem areas with your indexes/database use.

Click here to copy the following block

USE master
GO

----------------------------------------------------------
-- This creates a table to log all activity and results --
----------------------------------------------------------

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fragreport]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[fragreport]
GO

CREATE TABLE [dbo].[fragreport] (
[fid] [int] IDENTITY (1, 1) NOT NULL ,
[timestamp] [datetime] NULL ,
[ObjectName] [sysname] NOT NULL ,
[ObjectId] [int] NULL ,
[IndexName] [sysname] NOT NULL ,
[IndexId] [int] NULL ,
[Level] [int] NULL ,
[Pages] [int] NULL ,
[Rows] [int] NULL ,
[MinimumRecordSize] [int] NULL ,
[MaximumRecordSize] [int] NULL ,
[AverageRecordSize] [float] NULL ,
[ForwardedRecords] [int] NULL ,
[Extents] [int] NULL ,
[ExtentSwitches] [int] NULL ,
[AverageFreeBytes] [float] NULL ,
[AveragePageDensity] [float] NULL ,
[ScanDensity] [float] NULL ,
[BestCount] [int] NULL ,
[ActualCount] [int] NULL ,
[LogicalFragmentation] [float] NULL ,
[ExtentFragmentation] [float] NULL ,
[DBName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PrePost] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[fragreport] WITH NOCHECK ADD
CONSTRAINT [PK_fragreport] PRIMARY KEY CLUSTERED
(
[fid]
) ON [PRIMARY]
GO

-------------------------------------------------------
-- Run this from master and supply any database name --
-------------------------------------------------------

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ap_IndexDefragAndRebuild]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[ap_IndexDefragAndRebuild]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE ap_IndexDefragAndRebuild
@dbname varchar(100)
AS

SET NOCOUNT ON

CREATE TABLE #tables(
rid int identity (1,1),
tabid int,
[name] varchar(100)
)

CREATE TABLE #indexes(
rid int identity (1,1),
indid int,
[name] varchar(100)
)

CREATE TABLE #fragreport(
fid int identity (1,1),
[timestamp] datetime default getdate(),
ObjectName sysname,
ObjectId int,
IndexName sysname,
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,
DBName varchar(100) NULL,
PrePost varchar(20) NULL
)

CREATE TABLE #reindex(
rid int identity (1,1),
ObjectName sysname,
IndexName sysname
)

DECLARE @numtables int,
@numindexes int,
@numreindexes int,
@tabcount int,
@indcount int,
@recount int,
@currtable int,
@tabname varchar(100),
@currind int,
@indname varchar(100)

SET @tabcount = 1

INSERT INTO #tables([tabid], [name])
EXEC ('SELECT [id], [name] FROM ' + @dbname + '.dbo.sysobjects WHERE xtype = ''U'' and [name] <> ''dtproperties''')

SELECT @numtables = count(*) FROM #tables

WHILE @tabcount <= @numtables
BEGIN
SET @indcount = 1

SELECT @currtable = tabid,
     @tabname = ltrim(rtrim([name]))
FROM #tables
WHERE rid = @tabcount

INSERT INTO #fragreport([ObjectName], [ObjectId], [IndexName], [IndexId], [Level], [Pages], [Rows], [MinimumRecordSize], [MaximumRecordSize], [AverageRecordSize], [ForwardedRecords], [Extents], [ExtentSwitches], [AverageFreeBytes], [AveragePageDensity], [ScanDensity], [BestCount], [ActualCount], [LogicalFragmentation], [ExtentFragmentation])
EXEC('USE ' + @dbname + ' DBCC SHOWCONTIG ([' + @tabname + ']) WITH ALL_INDEXES, TABLERESULTS')

UPDATE #fragreport
SET PrePost = 'PRE'
WHERE PrePost is NULL

INSERT #indexes([indid], [name])
EXEC ('SELECT indid, [name] FROM ' + @dbname + '.dbo.sysindexes WHERE [id] = ' + @currtable + ' AND [name] not like ''_WA%'' AND indid NOT IN (0, 255)')

SELECT @numindexes = count(*) FROM #indexes

WHILE @indcount <= @numindexes
BEGIN
SELECT @currind = indid,
     @indname = ltrim(rtrim([name]))
FROM #indexes
WHERE rid = @indcount
DBCC INDEXDEFRAG (@dbname, @tabname, @indname)
SET @indcount = @indcount + 1
   END

INSERT INTO #fragreport([ObjectName], [ObjectId], [IndexName], [IndexId], [Level], [Pages], [Rows], [MinimumRecordSize], [MaximumRecordSize], [AverageRecordSize], [ForwardedRecords], [Extents], [ExtentSwitches], [AverageFreeBytes], [AveragePageDensity], [ScanDensity], [BestCount], [ActualCount], [LogicalFragmentation], [ExtentFragmentation])
EXEC('USE ' + @dbname + ' DBCC SHOWCONTIG ([' + @tabname + ']) WITH ALL_INDEXES, TABLERESULTS')

UPDATE #fragreport
SET PrePost = 'POST'
WHERE PrePost is NULL

SET @tabcount = @tabcount + 1
TRUNCATE TABLE #indexes
END

INSERT INTO #reindex([ObjectName],[IndexName])
SELECT ltrim(rtrim(ObjectName)), ltrim(rtrim(IndexName))
FROM #fragreport
WHERE IndexId NOT IN (0, 255)
AND ScanDensity < 90
AND LogicalFragmentation > 10
AND PrePost = 'POST'

SELECT @numreindexes = count(*) FROM #reindex

SET @recount = 1

WHILE @recount <= @numreindexes
BEGIN
SELECT @tabname = ObjectName,
     @indname = IndexName
FROM #reindex
WHERE rid = @recount

EXEC('DBCC DBREINDEX([' + @dbname + '.dbo.' + @tabname + '],[' + @indname + '])')

INSERT INTO #fragreport([ObjectName], [ObjectId], [IndexName], [IndexId], [Level], [Pages], [Rows], [MinimumRecordSize], [MaximumRecordSize], [AverageRecordSize], [ForwardedRecords], [Extents], [ExtentSwitches], [AverageFreeBytes], [AveragePageDensity], [ScanDensity], [BestCount], [ActualCount], [LogicalFragmentation], [ExtentFragmentation])
EXEC('USE ' + @dbname + ' DBCC SHOWCONTIG ([' + @tabname + '],[' + @indname + ']) WITH TABLERESULTS')

SET @recount = @recount + 1
END

UPDATE #fragreport
SET PrePost = 'REINDEXED'
WHERE PrePost is NULL

UPDATE #fragreport
SET DBName = @dbname

INSERT INTO [master].[dbo].[fragreport]([timestamp], [ObjectName], [ObjectId], [IndexName], [IndexId], [Level], [Pages], [Rows], [MinimumRecordSize], [MaximumRecordSize], [AverageRecordSize], [ForwardedRecords], [Extents], [ExtentSwitches], [AverageFreeBytes], [AveragePageDensity], [ScanDensity], [BestCount], [ActualCount], [LogicalFragmentation], [ExtentFragmentation], [DBName], [PrePost])
SELECT [timestamp], [ObjectName], [ObjectId], [IndexName], [IndexId], [Level], [Pages], [Rows], [MinimumRecordSize], [MaximumRecordSize], [AverageRecordSize], [ForwardedRecords], [Extents], [ExtentSwitches], [AverageFreeBytes], [AveragePageDensity], [ScanDensity], [BestCount], [ActualCount], [LogicalFragmentation], [ExtentFragmentation], [DBName], [PrePost] FROM #fragreport

DROP TABLE #tables
DROP TABLE #indexes
DROP TABLE #fragreport
DROP TABLE #reindex
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO