Index Maintenance for SQL2k

Once again, the “smart indexing in SQL2000” question has raised its ugly head on AskSSC.  Those of you in SQL2005 and above have the opportunity to use some fine index maintenance scripts from Ola Hallengren and Michelle Ufford (among others, I’m sure).  Those of us running SQL Server 2000 don’t have those options, so I spent a few quiet hours and put together the following Index maintenance stored procedure.

Please note: This is supplied without warranty, and is worth what you paid for it. Having said that, it has been in use on several servers for six months without incident…

Instructions for use

  1. Create the procedure provided below.
  2. Call the procedure either directly through T-SQL or as a scheduled task. I schedule it to run every night for an hour – your requirements may vary.

Parameters

It takes the following parameters:

  • @DatabaseName – Name of a specific database to work on; if blank or NULL will work on all databases
  • @RunDurationMinutes – number of minutes to run. If blank, or a negative value, or > 300, will default / revert to 60

Apologies – it’s badly formatted, and I haven’t yet found a suitable way of colour-coordinating code here except by hand, and that would be *really* tedious.  Oh, and it doesn’t log what it’s done anywhere, which is something I might add at some point.  Maybe.

CREATE   Procedure SmartIndex
(
    @DatabaseName varchar(255) = NULL,
    @RunDurationMinutes INT = 60
) WITH ENCRYPTION
AS

SET NOCOUNT ON

If (@RunDurationMinutes < 0 ) OR (@RunDurationMinutes > 300)
  BEGIN
    SELECT @RunDurationMinutes = 60
  END

IF @DatabaseName = ''
  BEGIN
    SET @DatabaseName = NULL
  END

Declare @EndDate datetime
SELECT @EndDate = dateadd(minute, @RunDurationMinutes, getdate())

CREATE TABLE #ConsFragList (
   DatabaseName CHAR (255),
   DatabaseID INT,
   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, IndexDepth INT, Owner char(255))

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)

IF @DatabaseName IS NULL
  BEGIN
exec sp_msforeachdb
'use [?]
INSERT INTO #fraglist
exec (''DBCC showcontig WITH TABLERESULTS, FAST'')
INSERT INTO #ConsFragList
SELECT db_name(), db_id(), f.*, INDEXPROPERTY (ObjectId, IndexName, ''IndexDepth''), u.Name  AS Owner
FROM #FragList f LEFT JOIN sysobjects o on f.ObjectID = o.ID LEFT JOIN sysusers u ON o.uid = u.uid
WHERE o.xtype <> ''s''
TRUNCATE TABLE #FragList
'
  END
ELSE
  BEGIN
exec ('use [' + @DatabaseName + ']
INSERT INTO #fraglist
exec (''DBCC showcontig WITH TABLERESULTS, FAST'')
INSERT INTO #ConsFragList
SELECT db_name(), db_id(), f.*, INDEXPROPERTY (ObjectId, IndexName, ''IndexDepth''), u.Name  AS Owner
FROM #FragList f LEFT JOIN sysobjects o on f.ObjectID = o.ID LEFT JOIN sysusers u ON o.uid = u.uid
WHERE o.xtype <> ''s''
TRUNCATE TABLE #FragList')
  END

--select * from #ConsFragList
--WHERE LogicalFrag > 10 -- TO DO
--AND Extents > 1 AND IndexID > 0 AND IndexDepth > 0 AND CountRows > 1000
--ORDER BY LogicalFrag DESC

DECLARE @DBName varchar(255)
DECLARE @ObjName varchar(255)
DECLARE @IndName varchar(255)
Declare @Owner varchar(255)
DECLARE @LogicalFrag int
declare @sqlcmd varchar(1024)

declare DefragThese CURSOR FOR
SELECT RTRIM(DatabaseName), RTRIM(ObjectName), RTRIM(IndexName), RTRIM(Owner), LogicalFrag FROM #ConsFragList
WHERE LogicalFrag > 10
--AND Extents > 1
--AND IndexID > 0
AND IndexDepth > 0
AND CountPages > 100
ORDER BY LogicalFrag DESC

OPEN DefragThese
FETCH NEXT FROM DefragThese INTO @DBName, @ObjName, @IndName, @Owner, @LogicalFrag
WHILE @@FETCH_STATUS = 0
BEGIN
    SELECT @DBName = '[' + @DBName + ']'
    SELECT @ObjName = '[' + @ObjName + ']'
    SELECT @IndName = '[' + @IndName + ']'
    SELECT @Owner = '[' + @Owner + ']'

    IF @LogicalFrag > 30 --TODO
      BEGIN
        SELECT @SQLCmd = 'DBCC DBREINDEX (''' + @DBName + '.' + @Owner + '.' + @ObjName + ''', ' + @IndName + ') WITH NO_INFOMSGS'
      END
    ELSE IF @LogicalFrag > 10 -- TODO
      BEGIN
        SELECT @SQLCmd = 'DBCC INDEXDEFRAG (' + @DBName + ', ''' + @Owner + '.' + @ObjName + ''', ' + @IndName + ') WITH NO_INFOMSGS'
        SELECT @SQLCmd = @SQLCmd + '    USE ' + @DBName + '    UPDATE STATISTICS ' + @ObjName + ' ' + @IndName
      END
--    print @SQLCmd
    exec (@SQLCmd)
    WAITFOR DELAY '00:00:05'
    IF getdate() > @EndDate
      BEGIN
        WHILE @@FETCH_STATUS = 0
          BEGIN
            FETCH NEXT FROM DefragThese INTO @DBName, @ObjName, @IndName, @Owner, @LogicalFrag
          END
      END
    ELSE
      BEGIN
        FETCH NEXT FROM DefragThese INTO @DBName, @ObjName, @IndName, @Owner, @LogicalFrag
      END
END
CLOSE DefragThese
DEALLOCATE DefragThese

drop table #ConsFragList
drop table #FragList

GO
Advertisements
This entry was posted in SQLServerPedia Syndication and tagged , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s