SQL Script to Rebuild Indexes more intelligently

Hi,

I been pretty busy last few weeks, there are instances when you want to create a Intelligent system to understand the underlying databse system and rebuild index based on that :

Here is the one which worked for me :
Step 1 :
Create the below table DbIndexPreferences in Central database, with the same structure.

Step2:

Create a log table IndexDefragLog to store details for defragmentation/Fillfactor

STEP 3:

Create Below procedure :

USE Ur_centralDBName_WhereUDeplpyedTable
GO

CREATE PROCEDURE dbo.USP_ManageIndexes
(
@minFragmentation FLOAT = 5.0
/* in percent, will not defrag if fragmentation less than specified */
, @rebuildThreshold FLOAT = 30.0
/* in percent, greater than @rebuildThreshold will result in rebuild instead of reorg */
, @executeSQL BIT = 1
/* 1 = execute; 0 = print command only */
, @DATABASE VARCHAR(128) = Null
/* Option to specify a database name; null will return all */
, @tableName VARCHAR(4000) = Null -- databaseName.schema.tableName
/* Option to specify a table name; null will return all */
, @onlineRebuild
BIT = 1
/* 1 = online rebuild; 0 = offline rebuild; only in Enterprise */
, @maxDopRestriction TINYINT = Null
/* Option to restrict the number of processors for the operation; only in Enterprise */
, @printCommands BIT = 0
/* 1 = print commands; 0 = do not print commands */
, @printFragmentation BIT = 0
/* 1 = print fragmentation prior to defrag;
0 = do not print */
, @defragDelay CHAR(8) = '00:00:05'
/* time to wait between defrag commands */
, @scanMode NVARCHAR(8) = N'Limited'
/* scan level to be used with dm_db_index_physical_stats. Options are DEFAULT, NULL, LIMITED, SAMPLED, or DETAILED. The default (NULL) is LIMITED */
, @debugMode BIT = 1
/* display some useful comments to help determine if/where issues occur */
, @FillFactor NVARCHAR(100) = '90'
)
AS
SET NOCOUNT ON;
SET XACT_Abort ON;
SET Quoted_Identifier ON;

BEGIN

IF @debugMode = 1 RAISERROR('Script Started...', 0, 42) WITH NoWait;

/* Declare our variables */
DECLARE @objectID INT
, @databaseID INT
, @databaseName NVARCHAR(128)
, @indexID INT
, @partitionCount BIGINT
, @schemaName NVARCHAR(128)
, @objectName NVARCHAR(128)
, @indexName NVARCHAR(128)
, @partitionNumber SMALLINT
, @partitions SMALLINT
, @fragmentation FLOAT
, @pageCount INT
, @sqlCommand NVARCHAR(4000)
, @rebuildCommand NVARCHAR(200)
, @dateTimeStart DATETIME
, @dateTimeEnd DATETIME
, @containsLOB BIT
, @editionCheck BIT
, @debugMessage VARCHAR(128)
, @updateSQL NVARCHAR(4000)
, @partitionSQL NVARCHAR(4000)
, @partitionSQL_Param NVARCHAR(1000)
, @LOB_SQL NVARCHAR(4000)
, @LOB_SQL_Param NVARCHAR(1000)
, @URN NVARCHAR(4000)
, @WithURN INT
, @URN_SQL_Param NVARCHAR(1000)
, @IDENT NVARCHAR(4000)
, @IS_PRI_IDENT INT
, @IDENT_SQL_Param NVARCHAR(1000)

/* Create our temporary tables */
CREATE TABLE #indexDefragList
(
databaseID INT
, databaseName NVARCHAR(128)
, objectID INT
, indexID INT
, partitionNumber SMALLINT
, fragmentation FLOAT
, page_count INT
, defragStatus BIT
, schemaName NVARCHAR(128) Null
, objectName NVARCHAR(128) Null
, indexName NVARCHAR(128) Null
);

CREATE TABLE #databaseList
(
databaseID INT
, databaseName VARCHAR(128)
);

CREATE TABLE #processor
(
[INDEX] INT
, Name VARCHAR(128)
, Internal_Value INT
, Character_Value INT
);

IF @debugMode = 1 RAISERROR('Beginning validation...', 0, 42) WITH NoWait;

/* Just a little validation... */
IF @minFragmentation Not Between 0.00 And 100.0
SET @minFragmentation = 5.0;

IF @rebuildThreshold Not Between 0.00 And 100.0
SET @rebuildThreshold = 30.0;

IF @defragDelay Not Like '00:[0-5][0-9]:[0-5][0-9]'
SET @defragDelay = '00:00:05';

/* Make sure we're not exceeding the number of processors we have available */
INSERT INTO #processor
EXECUTE XP_MSVER 'ProcessorCount';

IF @maxDopRestriction IS Not Null And @maxDopRestriction > (SELECT Internal_Value FROM #processor)
SELECT @maxDopRestriction = Internal_Value
FROM #processor;

/* Check our server version; 1804890536 = Enterprise, 610778273 = Enterprise Evaluation, -2117995310 = Developer */
IF (SELECT SERVERPROPERTY('EditionID')) In (1804890536, 610778273, -2117995310)
SET @editionCheck = 1 -- supports online rebuilds
ELSE
SET @editionCheck = 0; -- does not support online rebuilds

IF @debugMode = 1 RAISERROR('Grabbing a list of our databases...', 0, 42) WITH NoWait;

/* Retrieve the list of databases to investigate */
INSERT INTO #databaseList
SELECT database_id
, name
FROM sys.databases
WHERE name = IsNull(@DATABASE, name)
And database_id > 4 -- exclude system databases
And [STATE] = 0; -- state must be ONLINE

IF @debugMode = 1 RAISERROR('Looping through our list of databases and checking for fragmentation...', 0, 42) WITH NoWait;

/* Loop through our list of databases */
WHILE (SELECT COUNT(*) FROM #databaseList) > 0
BEGIN

SELECT TOP 1 @databaseID = databaseID
FROM #databaseList;

SELECT TOP 1 @databaseName = databaseName
FROM #databaseList;

SELECT @debugMessage = ' working on ' + DB_NAME(@databaseID) + '...';

IF @debugMode = 1
RAISERROR(@debugMessage, 0, 42) WITH NoWait;

/*If the database is present in the DbIndexPreferences then take the parameters values from there else the default*/
IF EXISTS (SELECT 1 FROM DbIndexPreferences WHERE DatabaseName = @databaseName)
BEGIN

SELECT @minFragmentation = isnull(MinFragmentation,@minFragmentation) from DbIndexPreferences where DatabaseName = @databaseName
SELECT @RebuildThreshold = isnull(MinFragmentation,@minFragmentation) from DbIndexPreferences where DatabaseName = @databaseName
END

/* Determine which indexes to defrag using our user-defined parameters */
INSERT INTO #indexDefragList
SELECT
database_id AS databaseID
, QUOTENAME(DB_NAME(database_id)) AS 'databaseName'
, [OBJECT_ID] AS objectID
, index_id AS indexID
, partition_number AS partitionNumber
, avg_fragmentation_in_percent AS fragmentation
, page_count
, 0 AS 'defragStatus' /* 0 = unprocessed, 1 = processed */
, Null AS 'schemaName'
, Null AS 'objectName'
, Null AS 'indexName'
FROM sys.dm_db_index_physical_stats (@databaseID, OBJECT_ID(@tableName), Null , Null, @scanMode)
WHERE avg_fragmentation_in_percent >= @minFragmentation
And index_id > 0 -- ignore heaps
And page_count > 8 -- ignore objects with less than 1 extent
OPTION (MaxDop 1);

DELETE FROM #databaseList
WHERE databaseID = @databaseID;

END

CREATE CLUSTERED INDEX CIX_temp_indexDefragList
ON #indexDefragList(databaseID, objectID, indexID, partitionNumber);

SELECT @debugMessage = 'Looping through our list... there''s ' + CAST(COUNT(*) AS VARCHAR(10)) + ' indexes to defrag!'
FROM #indexDefragList;

IF @debugMode = 1 RAISERROR(@debugMessage, 0, 42) WITH NoWait;

/* Begin our loop for defragging */
WHILE (SELECT COUNT(*) FROM #indexDefragList WHERE defragStatus = 0) > 0
BEGIN

IF @debugMode = 1 RAISERROR(' Picking an index to beat into shape...', 0, 42) WITH NoWait;

/* Grab the most fragmented index first to defrag */
SELECT TOP 1
@objectID = objectID
, @indexID = indexID
, @databaseID = databaseID
, @databaseName = databaseName
, @fragmentation = fragmentation
, @partitionNumber = partitionNumber
, @pageCount = page_count
FROM #indexDefragList
WHERE defragStatus = 0
ORDER BY fragmentation DESC;

IF @debugMode = 1 RAISERROR(' Looking up the specifics for our index...', 0, 42) WITH NoWait;

/* Look up index information */
SELECT @updateSQL = N'Update idl
Set schemaName = QuoteName(s.name)
, objectName = QuoteName(o.name)
, indexName = QuoteName(i.name)
From #indexDefragList As idl
Inner Join ' + @databaseName + '.sys.objects As o
On idl.objectID = o.object_id
Inner Join ' + @databaseName + '.sys.indexes As i
On o.object_id = i.object_id
Inner Join ' + @databaseName + '.sys.schemas As s
On o.schema_id = s.schema_id
Where o.object_id = ' + CAST(@objectID AS VARCHAR(10)) + '
And i.index_id = ' + CAST(@indexID AS VARCHAR(10)) + '
And i.type > 0
And idl.databaseID = ' + CAST(@databaseID AS VARCHAR(10));

EXECUTE SP_EXECUTESQL @updateSQL;

/* Grab our object names */
SELECT @objectName = objectName
, @schemaName = schemaName
, @indexName = indexName
FROM #indexDefragList
WHERE objectID = @objectID
And indexID = @indexID
And databaseID = @databaseID;

IF @debugMode = 1 RAISERROR(' Grabbing the partition count...', 0, 42) WITH NoWait;

/* Determine if the index is partitioned */
SELECT @partitionSQL = 'Select @partitionCount_OUT = Count(*)
From ' + @databaseName + '.sys.partitions
Where object_id = ' + CAST(@objectID AS VARCHAR(10)) + '
And index_id = ' + CAST(@indexID AS VARCHAR(10)) + ';'
, @partitionSQL_Param = '@partitionCount_OUT int OutPut';

EXECUTE SP_EXECUTESQL @partitionSQL, @partitionSQL_Param, @partitionCount_OUT = @partitionCount OUTPUT;

IF @debugMode = 1 RAISERROR(' Seeing if there''s any LOBs to be handled...', 0, 42) WITH NoWait;

/* Determine if the table contains LOBs */
SELECT @LOB_SQL = ' Select Top 1 @containsLOB_OUT = column_id
From ' + @databaseName + '.sys.columns With (NoLock)
Where [object_id] = ' + CAST(@objectID AS VARCHAR(10)) + '
And (system_type_id In (34, 35, 99)
Or max_length = -1);'
/* system_type_id --> 34 = image, 35 = text, 99 = ntext
max_length = -1 --> varbinary(max), varchar(max), nvarchar(max), xml */
, @LOB_SQL_Param = '@containsLOB_OUT int OutPut';

EXECUTE SP_EXECUTESQL @LOB_SQL, @LOB_SQL_Param, @containsLOB_OUT = @containsLOB OUTPUT;

IF @debugMode = 1 RAISERROR(' Building our SQL statements...', 0, 42) WITH NoWait;

/* If there's not a lot of fragmentation, or if we have a LOB, we should reorganize */
IF @fragmentation < @rebuildThreshold Or @containsLOB = 1 Or @partitionCount > 1
BEGIN

SET @sqlCommand = N'Alter Index ' + @indexName + N' On ' + @databaseName + N'.'
+ @schemaName + N'.' + @objectName + N' ReOrganize';

/* If our index is partitioned, we should always reorganize */
IF @partitionCount > 1
SET @sqlCommand = @sqlCommand + N' Partition = '
+ CAST(@partitionNumber AS NVARCHAR(10));

END;

/*If the database is present in the DbIndexPreferences then take the FillFactor value from there else the default*/
IF EXISTS (SELECT 1 FROM DbIndexPreferences WHERE DatabaseName = @databaseName)
BEGIN

SELECT @FillFactor = isnull([FillFactor],@FillFactor) from DbIndexPreferences where DatabaseName = @databaseName

END

SELECT @URN = ' Select Top 1 @WithURN_OUT = column_id
From ' + @databaseName + '.sys.columns a With (NoLock) join ' + @databaseName + '.sys.indexes b With (NoLock) on a.object_id = b.object_id
Where a.[object_id] = ' + CAST(@objectID AS VARCHAR(10)) + 'and b.is_primary_key = 1
And a.name like ''urn'+'%'';'
,@URN_SQL_Param = '@WithURN_OUT int OutPut';

EXECUTE SP_EXECUTESQL @URN,@URN_SQL_Param,@WithURN_OUT = @WithURN OUTPUT;

/* Check if the table has a field starting with Urn% which is part of a primary key index then make the fillfactor 80% */
IF(ISNULL(@WithURN,0) !=0)
BEGIN

SET @FillFactor = '80'
END
ELSE
BEGIN
SET @FillFactor = '75'
END

SELECT @IDENT = ' Select Top 1 @IS_PRI_IDENT_OUT = column_id
From ' + @databaseName + '.sys.columns a With (NoLock) join ' + @databaseName + '.sys.indexes b With (NoLock) on a.object_id = b.object_id
Where a.[object_id] = ' + CAST(@objectID AS VARCHAR(10)) + 'and a.is_identity = 1 and b.is_primary_key = 1
and b.type_desc = ''CLUSTERED'';'
,@IDENT_SQL_Param = '@IS_PRI_IDENT_OUT int OutPut';

EXECUTE SP_EXECUTESQL @IDENT,@IDENT_SQL_Param,@IS_PRI_IDENT_OUT = @IS_PRI_IDENT OUTPUT;

IF(ISNULL(@IS_PRI_IDENT,0) !=0)
BEGIN

SET @FillFactor = '90'
END

SELECT @LOB_SQL = ' Select Top 1 @containsLOB_OUT = column_id
From ' + @databaseName + '.sys.columns With (NoLock)
Where [object_id] = ' + CAST(@objectID AS VARCHAR(10)) + '
And (system_type_id In (34, 35, 99)
Or max_length = -1);'
/* system_type_id --> 34 = image, 35 = text, 99 = ntext
max_length = -1 --> varbinary(max), varchar(max), nvarchar(max), xml */
, @LOB_SQL_Param = '@containsLOB_OUT int OutPut';

EXECUTE SP_EXECUTESQL @LOB_SQL, @LOB_SQL_Param, @containsLOB_OUT = @containsLOB OUTPUT;

/* If the index is heavily fragmented and doesn't contain any partitions or LOB's, rebuild it */
IF @fragmentation >= @rebuildThreshold And IsNull(@containsLOB, 0)!= 1 And @partitionCount BEGIN

/* Set online rebuild options; requires Enterprise Edition */
IF @onlineRebuild = 1 And @editionCheck = 1
SET @rebuildCommand = N' Rebuild With (Online = On, FillFactor = '+@FillFactor;
ELSE
SET @rebuildCommand = N' Rebuild With (Online = Off, FillFactor = '+@FillFactor;

/* Set processor restriction options; requires Enterprise Edition */
IF @maxDopRestriction IS Not Null And @editionCheck = 1
SET @rebuildCommand = @rebuildCommand + N', MaxDop = ' + CAST(@maxDopRestriction AS VARCHAR(2)) + N')';
ELSE
SET @rebuildCommand = @rebuildCommand + N')';

SET @sqlCommand = N'Alter Index ' + @indexName + N' On ' + @databaseName + N'.'
+ @schemaName + N'.' + @objectName + @rebuildCommand;

END;

/* Are we executing the SQL? If so, do it */
IF @executeSQL = 1
BEGIN

IF @debugMode = 1 RAISERROR(' Executing SQL statements...', 0, 42) WITH NoWait;

/* Grab the time for logging purposes */
SET @dateTimeStart = GETDATE();
EXECUTE SP_EXECUTESQL @sqlCommand;
SET @dateTimeEnd = GETDATE();

/* Log our actions */
INSERT INTO dbo.IndexDefragLog
(
databaseID, databaseName, objectID, objectName, indexID, indexName, partitionNumber,
fragmentation, [FillFactor], page_count, dateTimeStart, durationSeconds
)
SELECT
@databaseID, @databaseName, @objectID, @objectName, @indexID, @indexName
, @partitionNumber, @fragmentation, @FillFactor, @pageCount, @dateTimeStart, DATEDIFF(SECOND, @dateTimeStart, @dateTimeEnd);

/* Just a little breather for the server */
WAITFOR Delay @defragDelay;

/* Print if specified to do so */
IF @printCommands = 1
PRINT N'Executed: ' + @sqlCommand;
END
ELSE
/* Looks like we're not executing, just printing the commands */
BEGIN
IF @debugMode = 1 RAISERROR(' Printing SQL statements...', 0, 42) WITH NoWait;

IF @printCommands = 1 PRINT IsNull(@sqlCommand, 'error!');
END

IF @debugMode = 1 RAISERROR(' Updating our index defrag status...', 0, 42) WITH NoWait;

/* Update our index defrag list so we know we've finished with that index */
UPDATE #indexDefragList
SET defragStatus = 1
WHERE databaseID = @databaseID
And objectID = @objectID
And indexID = @indexID
And partitionNumber = @partitionNumber;

END

/* Do we want to output our fragmentation results? */
IF @printFragmentation = 1
BEGIN

IF @debugMode = 1 RAISERROR(' Displaying fragmentation results...', 0, 42) WITH NoWait;

SELECT databaseID, databaseName, objectID, objectName, indexID, indexName,
fragmentation, page_count
FROM #indexDefragList;

END;

/* When everything is said and done, make sure to get rid of our temp table */
DROP TABLE #indexDefragList;
DROP TABLE #databaseList;
DROP TABLE #processor;

IF @debugMode = 1 RAISERROR('DONE!:)', 0, 42) WITH NoWait;

SET NOCOUNT OFF;
RETURN 0

END

STEP 4:

Create a SQL Job to run this once in a day or week as per your preference that executes this procedure

Below is the Logic of the stored procedure USP_ManageIndexes

1. Take the list of databases in a temporary table.
2. Loop through the database (While loop) and do following
3. If the database name is present in the DbIndexPreferences then take the parameters values of MinFragmentation, FillFactor, RebuildThreshold from there else we the default values passed to
   the stored procedure.
4. If the server version in Enterprise it will do an ONLINE rebuild else OFFLINE rebuild for non-enterprise.
5. Determine which indexes to defrag using our parameters.
6. If there’s not a lot of fragmentation, or if a table has a LOB, it will reorganize the index.
7. If the index is heavily fragmented and doesn’t contain any partitions or LOB’s, rebuild it.
8. If the database is present in the DbIndexPreferences then take the FillFactor value from there else the default values.
9. If the table has a field starting with Urn% which is part of a primary key index then it will make the
   fillfactor 80% else 75%.
10. If the table has a clustered identity primary key index then it will set the fillfactor to 90%.
11. If @executeSQL = 1, we will run
    the Rebuild/Reorganize script and log the details in IndexDefragLog
12. If @executeSQL = 0 and @printCommands = 1, it will just print the “Alter Index “statements.
13. If @debugMode = 1, it will
    print all the comments which will be helpful while tracing an error

Hope this helps

Happy Coding