SLQ 2000 Script to Maintain Indexes

There seem to be a lot of problems with the maintenance of indexes in SQL 2000 using the inbuilt Database Maintenance Plans.

The following script creates 2 tables and a stored procedure that can be run as a periodic job. It will defrag any indexes > 30% fragmentation in its default state.

Just be wary if you are log shipping though. Index work of any kind can blow out your log file size.

To install replace "Database Name Here" with your database name - open query analyzer and run the script.

The handling of the indexes can be controlled via the maint_index table. Be aware that the stored procedure processes the indexes in this table then repopulates it with the current state of the database. It is designed this way for a set and forget operation that will also allow you to make modifications to the indexes or force a rebuild by changing values in the maint_index table. This means in practice that if you are running it once per week then the indexes will be rebuilt bases on their state the previous week when the stored procedure was last run.
Fragmenting indexes will bubble up to the top and get processed but the maximum they will be processed is every second run.

Script Variables that can be set

• @db_name - self explanitory - this needs to be set
  
• @maxfrag - changes the fragmentation level at which an index is
  
• @maxminutes - This is a safety setting to limit how long the defrag process will run. If the script is still defraging after this period of time it will finish the current index and exit the loop
  

Database Fields that can be changed
You can go to the maint_index table and change operation performed on individual indexes the next time the script is run.

• Force_Defrag_REINDEX By default the script runs a defrag on the index (0). If you set this to 1 then a full reindex will be performed (but be aware that the table might be offline during this operation)
  
• SetFillFactor This will change the fill factor of the index. the default is its existing value.
  

I have had great success with this script improving "Doggy" servers performance.

Enjoy

Gerard


CREATE TABLE [maint_index] (
[Id] [int] IDENTITY (1, 1) NOT NULL ,
[ObjectName] [char] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IndexName] [char] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LogicalFrag] [decimal](18, 0) NULL ,
[Force_Defrag_REINDEX_1or2] [int] NULL CONSTRAINT [DF_maint_index_Force_Defrag_REINEX] DEFAULT (0),
[SetFillFactor] [int] NULL CONSTRAINT [DF_maint_index_FillFactor] DEFAULT (0),
[ObjectId] [int] NULL ,
[IndexId] [int] NULL ,
[Lvl] [int] NULL ,
[CountPages] [int] NULL ,
[CountRows] [int] NULL ,
[MinRecSize] [int] NULL ,
[MaxRecSize] [int] NULL ,
[AvgRecSize] [int] NULL ,
[ForRecCount] [int] NULL ,
[Extents] [int] NULL ,
[ExtentSwitches] [int] NULL ,
[AvgFreeBytes] [int] NULL ,
[AvgPageDensity] [int] NULL ,
[ScanDensity] [decimal](18, 0) NULL ,
[BestCount] [int] NULL ,
[ActualCount] [int] NULL ,
[ExtentFrag] [decimal](18, 0) NULL ,
[Defrag] [int] NULL CONSTRAINT [DF_maint_index_Defrag] DEFAULT (0),
[Fill_Factor] [int] NULL CONSTRAINT [DF_maint_index_Fill_Factor] DEFAULT (0)
) ON [PRIMARY]
GO

CREATE PROCEDURE [dbo].[maint_index_process] AS

SET NOCOUNT ON

--maint_index

SET NOCOUNT ON

DECLARE @db_name VARCHAR (128)

DECLARE @tablename VARCHAR (128)

DECLARE @execstr VARCHAR (255)

DECLARE @indexname VARCHAR (255)

DECLARE @objectid INT

DECLARE @indexid INT

DECLARE @frag DECIMAL

DECLARE @reindex INT

DECLARE @fill INT

DECLARE @maxfrag DECIMAL

DECLARE @maxminutes INT

DECLARE @maxruntime DATETIME

DECLARE @TempId int

--specify for each database it is run on

SELECT @db_name = 'CMS'

--maximum fragmentation to allow

SELECT @maxfrag = 30.0

--maximum time for the process to run, it will exit the loop if this time is exceeded

--Note1 if reindex run over they will not be stopped

SELECT @maxminutes = 90--1.5 hours

select @maxruntime = DATEADD(minute,@maxminutes,GETDATE()) --2 hours max run

--process reindexes, marked defrags, defrags over @maxfrag

DECLARE indexes CURSOR FOR

SELECT ObjectName, ObjectId, IndexName, IndexId, LogicalFrag, Force_Defrag_REINDEX_1or2, SetFillFactor

FROM maint_index

WHERE indexid >0

AND IndexId <> 255 --this indicates image data and cannot be defragged

AND (LogicalFrag>=@maxfrag

OR Force_Defrag_REINDEX_1or2 > 0)

ORDER BY Force_Defrag_REINDEX_1or2 DESC --do the reindexing first, indexes specifed to be defragmented then general fragmented over @maxfrag

-- Open the cursor

OPEN indexes

-- loop through the indexes

FETCH NEXT

FROM indexes

INTO @tablename,@objectid, @indexname, @indexid, @frag, @reindex, @fill

WHILE (@@FETCH_STATUS = 0) AND (GETDATE()< @maxruntime)--while we still have records and we havn't exceeded out times

BEGIN

IF @reindex = 2

BEGIN

IF @fill<1

BEGIN

SELECT @execstr = '])'--go with the default fill factor

SELECT @fill = -1 --indicate in history table that existing fill was used

END

ELSE

BEGIN

SELECT @execstr = '],' + CAST(@fill AS VARCHAR) + ')'

END

--create the command and insert initial history record

SELECT @execstr = 'DBCC DBREINDEX (''[' + @db_name + '].dbo.[' + RTRIM(@tablename) + ']'',[' + RTRIM(@indexname) + @execstr

INSERT INTO maint_index_history (maint_Database,maint_Table,maint_Index,StartTime,RequestedAction,FillUsed,CommandString)

VALUES (@db_name,RTRIM(@tablename),RTRIM(@indexname),GETDATE(),@reindex,@fill,@execstr)

SELECT @TempId=@@identity

--Do a reindex

EXEC (@execstr)

--update the history record

UPDATE maint_index_history SET EndTime= GETDATE() WHERE Id = @TempId

SELECT @TempId=0

END

ELSE

BEGIN

--create the command and insert initial history record

SELECT @execstr = 'DBCC INDEXDEFRAG ([' + @db_name + '], [' + RTRIM(@tablename) + '],[' + RTRIM(@indexname) + '])'

INSERT INTO maint_index_history (maint_Database,maint_Table,maint_Index,StartTime,RequestedAction,CommandString)

VALUES (@db_name,RTRIM(@tablename),RTRIM(@indexname),GETDATE(),@reindex,@execstr)

SELECT @TempId=@@identity

--Do a Defrag

EXEC (@execstr)

--update the history record

UPDATE maint_index_history SET EndTime= GETDATE() WHERE Id = @TempId

SELECT @TempId=0

END

FETCH NEXT FROM indexes

INTO @tablename,@objectid, @indexname, @indexid, @frag, @reindex, @fill

END

-- Close and deallocate the cursor

CLOSE indexes

DEALLOCATE indexes

--create temporary 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)

-- Declare cursor

DECLARE tables CURSOR FOR

SELECT TABLE_NAME

FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_TYPE = 'BASE TABLE'

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

--insert the temporary table into the maint_index then drop it

TRUNCATE TABLE maint_index--clear out existing data

INSERT INTO maint_index

(ObjectName,IndexName,LogicalFrag,ObjectId,IndexId,Lvl,CountPages,CountRows,MinRecSize,MaxRecSize,AvgRecSize,ForRecCount,Extents,ExtentSwitches,AvgFreeBytes,AvgPageDensity,ScanDensity,BestCount,ActualCount,ExtentFrag )

select

ObjectName,IndexName,LogicalFrag,ObjectId,IndexId,Lvl,CountPages,CountRows,MinRecSize,MaxRecSize,AvgRecSize,ForRecCount,Extents,ExtentSwitches,AvgFreeBytes,AvgPageDensity,ScanDensity,BestCount,ActualCount,ExtentFrag

from #fraglist

where indexid >0

DROP TABLE #fraglist