SQL 2005 Maintenance: Re-Index Plan

This goes through all databases and finds any indexes which have fragmentation greater than 15% and reindexes them. The loop near the beginning is because sys.dm_db_index_physical_stats needs a database ID. If you do not pass a database Id and leave it as null it will go thrugh all database – which will cause a problem if it is not writeable (read_only, offline, In Recovery)

To use the null options the account running the command needs: VIEW SERVER STATE

This task will re-index and index where fragmentation is greater than 15% provided that there aare more than 50 pages used in the index. If there are less than this the chances are the index isn’t being used and Query Analyser will perform a table scan.

I took the initial idea of using this function to populate a table for reindexing from a blog but can’t remember which. I have adapted it to suit the tasks I wanted to perform.

I find STATISTICS_NORECOMPUTE = OFF a little unintuitive. It means do not not recomputed stats – or in English: recompute the stats.

On most servers this tasks takes between 5 and 20 minutes to run and I can run it every night. There are a few though where it is run instead on a weekly basis.

I also use an extra line after AND ps.index_type_desc <> 'heap' to exclude some databases. This is used along with AND [Name] NOT IN ('LIST OF DBNAMES TO EXCLUDE'); The names should be like this (‘dbname’,’dbname2’). This is where the there is a database that does a lot of ETL work during the night so it is best to run a separate re-index job for that once it has finished.

If speed is a problem you can replace
FROM sys.dm_db_index_physical_stats (@dbCountID, NULL, NULL, NULL, 'detailed')
With
FROM sys.dm_db_index_physical_stats (@dbCountID, NULL, NULL, NULL, NULL)
This will then use the default Limited for gather statistics on the indexes – of course the faster version does not get such accurate data back.

use tempdb;

if not object_id('indexData') is null
  drop table [indexData];

CREATE TABLE [dbo].[indexData](
[dbName] varchar(250) Not NULL,
[di] int Not NULL,
[oi] varchar(150) NOT NULL,
[Frag] int not null,
[pages] int not null,
[iType] varchar(250) not null,
[oName] varchar(250) NULL,
[schemaName] varchar(250) NULL);

DECLARE @dbCountID as int;
SET @dbCountID = 0;
DECLARE @topID as int;
SELECT @topID = Max(database_id) FROM master.sys.databases;
While @dbCountID < @topID
BEGIN
   SELECT TOP (1) @dbCountID = database_id FROM master.sys.databases
   WHERE database_id > @dbCountID
   AND is_Read_Only = 0 and state_desc = 'ONLINE'
   AND [Name] NOT IN ('LIST OF DBNAMES TO EXCLUDE');

   INSERT INTO [indexData]([dbName],[di],[oi],[Frag],[pages],[iType])
   SELECT Distinct DB_NAME ( @dbCountID ), @dbCountID,    ps.object_id,ps.avg_fragmentation_in_percent,ps.Page_Count,ps.index_type_desc
   FROM sys.dm_db_index_physical_stats (@dbCountID, NULL, NULL, NULL, 'detailed') ps
   LEFT OUTER JOIN master.sys.databases AS sd
   ON ps.database_id = sd.database_id
   Where (ps.database_id > 4) AND sd.is_Read_only = 0 AND sd.state_desc = 'online'
   AND ps.Page_Count > 50
   AND ps.avg_fragmentation_in_percent > 15
   AND ps.index_type_desc <> 'heap'
   AND sd.database_id = @dbCountID;
END
DECLARE @dbName varchar(250);
DECLARE dbCursor CURSOR Local Fast_Forward FOR
SELECT Distinct [dbName]
FROM [indexData];

OPEN dbCursor;
FETCH NEXT FROM dbCursor INTO @dbName;
WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE @sql as Varchar(500)
    SET @sql = 'Use [' + @dbName + ']'
    Set @sql = @sql + 'SET ARITHABORT ON; SET QUOTED_IDENTIFIER ON;'
    Set @sql = @sql + ' Update tempdb..[indexData] '
    Set @sql = @sql + ' SET oName = object_Name(oi), '
    Set @sql = @sql + ' schemaName = (select schema_Name(sobj.schema_id) from sys.objects sobj where sobj.object_id = oi) '
   Set @sql = @sql + ' WHERE dbName = ''' + @dbName + ''';';
   Exec (@sql);
   Use tempdb;
   FETCH NEXT FROM dbCursor INTO @dbName;
END;
CLOSE dbCursor;
DEALLOCATE dbCursor;
DECLARE @dbName2 varchar(5000);
DECLARE dbCursor2 CURSOR Local Fast_Forward FOR
SELECT Distinct '[' + dbName + '].[' + schemaName + '].[' + oName + ']' as databaseObject
FROM [indexData]
WHERE oName not like 'perfValuesForProcess%';

OPEN dbCursor2;
FETCH NEXT FROM dbCursor2 INTO @dbName2;
WHILE @@FETCH_STATUS = 0
BEGIN
     DECLARE @sql2 as Varchar(8000);
     Set @sql2 = 'ALTER INDEX ALL ON ' + @dbName2
     Set @sql2 = @sql2 + ' REBUILD WITH (SORT_IN_TEMPDB = ON,       STATISTICS_NORECOMPUTE = OFF);';
     EXEC(@sql2);
     FETCH NEXT FROM dbCursor2 INTO @dbName2;
END;
CLOSE dbCursor2;
DEALLOCATE dbCursor2;
Use tempdb;
Drop Table [indexData];

Reference
MSDN sys.dm_db_index_physical_stats (http://msdn.microsoft.com/en-us/library/ms188917(SQL.90).aspx)