First, We run the Index_Evaluation.sql every night. It will check all the indexes on the database.
If the index PercentageFragmented between 5 and 30, we will insert this index record to INDEX_REORGANIZE_SCHEDULE table.
If the Non-Clustered index PercentageFragmented >=30, we will insert this index record to NC_INDEX_REBUILD_SCHEDULE table.
If the Clustered index PercentageFragmented >=30, we will insert this index record to C_INDEX_REBUILD_SCHEDULE table.
Second, We schedule to run Index_Reorganize.sql and NC_Index_Rebuild.sql every night, to reorganize indexes which PercentageFragmented between 5 and 30, and reindex Non-Clustered indexes which PercentageFragmented >=30.
Third, We schedule to run C_Index_Rebuild.sql on weekend to reindex Clustered indexes which PercentageFragmented >=30.
Please change the database name and schedule base on your system.
Here are the SQL code.
Index_Evaluation.sql
--------------------------------------------------------------------------------
-- Evaluate every Index on the Database --
-- Put them to Reorganize or Rebuild schedule table --
-- base on the index Fragmentation. --
-- Author: Victor Hu --
--------------------------------------------------------------------------------
--Specify the database that you want to evaluate the indexes.
USE AdventureWorks2012;
GO
-- Set the Dabatbase that will be checked index information.
/* Begin From Here */
--Declare variables
DECLARE @command NVARCHAR(4000);
DECLARE @SchemaName NVARCHAR(100);
DECLARE @TableName NVARCHAR(100);
DECLARE @IndexName NVARCHAR (100);
DECLARE @IndexID INT;
DECLARE @TableID INT;
DECLARE @IndexType NVARCHAR(30);
DECLARE @PercentageFragmented FLOAT;
DECLARE @DB_ID INT;
DECLARE @DatabaseID INT;
DECLARE @ONLINE NVARCHAR(30);
DECLARE @FILLFACTOR NVARCHAR(3);
DECLARE @ProductVersion NCHAR(2);
DECLARE @Log_FileName NVARCHAR(500);
DECLARE @SQL NVARCHAR(1000);
DECLARE @drive VARCHAR(2);
DECLARE @Dir NVARCHAR(500);
DECLARE @FileName NVARCHAR(500);
--SET @DB_ID = 7
SET @DB_ID = DB_ID();
/********************************************************************************/
/*Create IndexMaintenance Database */
/* Drive : Drive letter */
/* Dytectory : like '\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\' */
/* Name : file name on the drive directory , Like 'IndexMaintenance' */
/********************************************************************************/
SELECT @drive = 'C:'
SELECT @Dir = '\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\'
SELECT @FileName ='IndexMaintenance'
--------------------------------------------------------------
---- set produc SQL server version --
---- 8% SQL 2000 --
---- 9% SQL 2005 and up --
--------------------------------------------------------------
SET @ProductVersion = '9%'
--Instantiate @ONLINE for later use
SET @ONLINE = '';
IF CAST(SERVERPROPERTY('edition') AS NVARCHAR(30)) LIKE 'Enterprise%' OR CAST(SERVERPROPERTY('edition') AS NVARCHAR(30)) LIKE 'Developer%' SET @ONLINE = ', ONLINE = ON';
--SET @FILLFACTOR to desired fill factor
SET @FILLFACTOR = '80';
------------------------------
------------------------------
IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = 'IndexMaintenance') AND (@drive <> '' AND @Dir <> '' AND @FileName <>'')
BEGIN
SELECT @FileName = @drive + @Dir + @FileName +'.mdf'
SELECT @Log_FileName = @FileName + '.ldf'
PRINT @FileName
SET @SQL =
N'CREATE DATABASE [IndexMaintenance] ON (NAME = N' + N'''' + N'IndexMaintenance' + N'''' +
N', FILENAME = N' + N'''' + @FileName + N''''+ N', SIZE = 4, FILEGROWTH = 10%)' +
N' LOG ON (NAME = N' + N'''' + N'Maintenance_log' + N'''' + N', FILENAME = N' + N'''' +
@Log_FileName + N'''' + N' , SIZE = 2, FILEGROWTH = 10%) COLLATE Latin1_General_CI_AS'
EXEC (@SQL)
PRINT (@SQL)
END
ELSE
BEGIN
IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = 'IndexMaintenance')
BEGIN
PRINT ('Please provide Driver, Directory and File name to create database.')
PRINT ('The Program is terminated.')
RETURN --Quit the program
END
END
--------------------------------------
-- Create tables on Database --
--------------------------------------
--Check to see if INDEX_REORGANIZE_SCHEDULE exists, and if not, create it
SET @command = '
USE [IndexMaintenance]
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'''+'[dbo].[INDEX_REORGANIZE_SCHEDULE]''' + ') AND TYPE in (N'''+ 'U''' + '))
BEGIN
PRINT ''' + 'Could Not Identify The Index Reorganize Table, Creating Now''' + '
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [dbo].[INDEX_REORGANIZE_SCHEDULE](
[ID] [int] IDENTITY(1,1),
[DatabaseID] [int] NOT NULL,
[SchemaName] [NVARCHAR](100) NOT NULL,
[TableName] [nvarchar](100) NOT NULL,
[IndexName] [nvarchar](100) NOT NULL,
[TableID] [int] NOT NULL,
[IndexID] [int] NOT NULL,
[IndexType] [nvarchar](30) NOT NULL,
[Fragm] [nvarchar](100) NOT NULL,
[Command] [nvarchar] (220) NOT NULL,
[ReorganizeDate] datetime)
ON [PRIMARY]
END'
EXECUTE SP_EXECUTESQL @command;
--Check to see if NC_INDEX_REBUILD_SCHEDULE exists, and if not, create it
SET @command = '';
SET @command = '
USE [IndexMaintenance]
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''' + '[dbo].[NC_INDEX_REBUILD_SCHEDULE]''' + ') AND TYPE in (N''' + 'U''' + '))
BEGIN
PRINT ''' + 'Could Not Identify The NONCLUSTERED Index Rebuild Table, Creating Now''' + '
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [dbo].[NC_INDEX_REBUILD_SCHEDULE](
[ID] [int] IDENTITY(1,1),
[DatabaseID] [int] NOT NULL,
[SchemaName] [NVARCHAR](100) NOT NULL,
[TableName] [nvarchar](100) NOT NULL,
[IndexName] [nvarchar](100) NOT NULL,
[TableID] [int] NOT NULL,
[IndexID] [int] NOT NULL,
[IndexType] [nvarchar](30) NOT NULL,
[Fragm] [nvarchar](100) NOT NULL,
[Command] [nvarchar] (220) NOT NULL,
[ReindexDate] datetime
)
ON [PRIMARY]
END'
EXECUTE SP_EXECUTESQL @command;
--Check to see if C_INDEX_REBUILD_SCHEDULE exists, and if not, create it
SET @command = '';
SET @command = '
USE [IndexMaintenance]
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''' + '[dbo].[C_INDEX_REBUILD_SCHEDULE]''' + ') AND TYPE in (N''' + 'U''' + '))
BEGIN
PRINT '''+ 'Could Not Identify The CLUSTERED Index Rebuild Table, Creating Now''' + '
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [dbo].[C_INDEX_REBUILD_SCHEDULE](
[ID] [int] IDENTITY(1,1),
[DatabaseID] [int] NOT NULL,
[SchemaName] [NVARCHAR](100) NOT NULL,
[TableName] [nvarchar](100) NOT NULL,
[IndexName] [nvarchar](100) NOT NULL,
[TableID] [int] NOT NULL,
[IndexID] [int] NOT NULL,
[IndexType] [nvarchar](30) NOT NULL,
[Fragm] [nvarchar](100) NOT NULL,
[Command] [nvarchar] (220) NOT NULL,
[ReindexDate] datetime
)
ON [PRIMARY]
END'
EXECUTE SP_EXECUTESQL @command;
PRINT 'Checking ' + DB_NAME(@DB_ID) + ' indexes fragmentation.'
--Check to see if temporary work table exists
IF object_id('tempdb..#IndexesEvaluation') is not null
BEGIN
PRINT 'Identified, Temporary Table, Dropping';
DROP TABLE #IndexesEvaluation;
END
--select the Table Name, Index Name, Table ID, Index ID, Index Type, and the fragmentation percentage of the Index into a temporary workspace
SELECT database_id DatabaseID, sch.name SchemaName ,obj.name TableName, ind.name IndexName, stats.object_id TableID, stats.index_id IndexID, stats.index_type_desc IndexType, avg_fragmentation_in_percent PercentageFragmented
INTO #IndexesEvaluation
FROM sys.dm_db_index_physical_stats (@DB_ID,NULL,NULL,NULL,'detailed') stats
JOIN sysindexes ind ON (ind.id = stats.object_id and ind.indid = stats.index_id)
JOIN sysobjects obj ON (obj.id = stats.object_id)
JOIN sys.schemas sch ON (obj.uid = sch.schema_id)
WHERE stats.avg_fragmentation_in_percent > 5 and stats.index_id <> 0;
----------------------------------------------------------------------------------------------------------
--INSERT Rows to INDEX_REORGANIZE_SCHEDULE, NC_INDEX_REBUILD_SCHEDULE, C_INDEX_REBUILD_SCHEDULE table --
----------------------------------------------------------------------------------------------------------
DECLARE IndexCursor CURSOR FOR SELECT * FROM #IndexesEvaluation ORDER BY PercentageFragmented DESC;
OPEN IndexCursor
WHILE ( 1=1)
BEGIN
SET @command = '';
FETCH NEXT FROM IndexCursor INTO @DatabaseID, @SchemaName ,@TableName, @IndexName, @TableID, @IndexID, @IndexType, @PercentageFragmented;
IF @@FETCH_STATUS <> 0
BEGIN
BREAK;
END
IF (@PercentageFragmented < 30.0 )
BEGIN
IF (@ProductVersion = '9%') SET @command = 'ALTER INDEX [' + @IndexName + '] ON [' + @SchemaName + '].[' + @TableName + '] REORGANIZE'
ELSE SET @command = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@TableID) + ',' + RTRIM(@IndexID) + ')'
IF NOT EXISTS (SELECT * FROM [IndexMaintenance].[dbo].[INDEX_REORGANIZE_SCHEDULE] WHERE ([ReorganizeDate] IS NULL AND TableID = @TableID AND IndexID = @IndexID))
BEGIN
INSERT INTO [IndexMaintenance].[dbo].[INDEX_REORGANIZE_SCHEDULE](
[DatabaseID]
,[SchemaName]
,[TableName]
,[IndexName]
,[TableID]
,[IndexID]
,[IndexType]
,[Fragm]
,[Command])
VALUES
(@DatabaseID
,@SchemaName
,@TableName
,@IndexName
,@TableID
,@IndexID
,@IndexType
,@PercentageFragmented
,@command)
PRINT('Inserted record for INDEX_REORGANIZE Table: ' + @SchemaName+ '.'+ @TableName + ', on Index ' + @IndexName)
END
CONTINUE;
END
IF (@PercentageFragmented >= 30.0 AND @IndexType = 'CLUSTERED INDEX')
BEGIN
IF @ProductVersion = '9%' SET @command = 'ALTER INDEX [' + @IndexName + '] ON [' + @SchemaName + '].[' + @TableName + '] REBUILD WITH (FILLFACTOR = ' + @FILLFACTOR + @ONLINE + ')'
ELSE SET @command = 'DBCC DBREINDEX([' + @SchemaName + '].['+ @tableName + '],' + @FILLFACTOR + ')'
IF NOT EXISTS (SELECT * FROM [IndexMaintenance].[dbo].[C_INDEX_REBUILD_SCHEDULE] WHERE ([ReindexDate] IS NULL AND TableID = @TableID AND IndexID = @IndexID))
BEGIN
INSERT INTO [IndexMaintenance].[dbo].[C_INDEX_REBUILD_SCHEDULE]
([DatabaseID]
,[SchemaName]
,[TableName]
,[IndexName]
,[TableID]
,[IndexID]
,[IndexType]
,[Fragm]
,[Command])
VALUES
(@DatabaseID
,@SchemaName
,@TableName
,@IndexName
,@TableID
,@IndexID
,@IndexType
,@PercentageFragmented
,@command)
PRINT('Inserted record for C_INDEX_REBUILD Table: ' + @SchemaName + '.' + @TableName + ', on Index ' + @IndexName)
END
CONTINUE;
END
IF (@PercentageFragmented >= 30.0 AND @IndexType <> 'CLUSTERED INDEX')
BEGIN
IF @ProductVersion = '9%' SET @command = 'ALTER INDEX [' + @IndexName + '] ON [' + @SchemaName + '].[' + @TableName + '] REBUILD WITH (FILLFACTOR = ' + @FILLFACTOR + @ONLINE + ')'
ELSE SET @command = 'DBCC DBREINDEX(' + @tableName + ',' + @FILLFACTOR + ')'
IF NOT EXISTS (SELECT * FROM [IndexMaintenance].[dbo].[NC_INDEX_REBUILD_SCHEDULE] WHERE ([ReindexDate] IS NULL AND TableID = @TableID AND IndexID = @IndexID))
BEGIN
INSERT INTO [IndexMaintenance].[dbo].[NC_INDEX_REBUILD_SCHEDULE]
([DatabaseID]
,[SchemaName]
,[TableName]
,[IndexName]
,[TableID]
,[IndexID]
,[IndexType]
,[Fragm]
,[Command])
VALUES
(@DatabaseID
,@SchemaName
,@TableName
,@IndexName
,@TableID
,@IndexID
,@IndexType
,@PercentageFragmented
,@command)
PRINT('Inserted record for NC_INDEX_REBUILD Table ' + @SchemaName + '.' + @TableName + ', on Index ' + @IndexName)
END
CONTINUE;
END
END
--Close Cursor And Deallocate
CLOSE IndexCursor
DEALLOCATE IndexCursor
Index_Reorganize.sql
--------------------------------------------------------------
-- Doing Index Reorganize --
-- Author: Victor Hu --
--------------------------------------------------------------
--Specify the database that you want to reorganize the indexes.
USE AdventureWorks2012;
GO
DECLARE @Command NVARCHAR(4000);
DECLARE @ID INT;
DECLARE IndexCursor CURSOR FOR
SELECT ID, Command
FROM [IndexMaintenance].[dbo].[INDEX_REORGANIZE_SCHEDULE]
WHERE [ReorganizeDate] IS NULL
OPEN IndexCursor
WHILE 1=1
BEGIN
FETCH NEXT FROM IndexCursor INTO @ID, @Command;
IF @@FETCH_STATUS <> 0
BEGIN
BREAK;
END
BEGIN TRY
EXEC (@command)
UPDATE [IndexMaintenance].[dbo].[INDEX_REORGANIZE_SCHEDULE] SET [ReorganizeDate] = GETDATE() WHERE ID = @ID;
PRINT @command;
END TRY
BEGIN CATCH
PRINT 'Get error when ' + @command;
PRINT 'ErrorMessage: ' + ERROR_MESSAGE();
END CATCH
END
--Close Cursor And Deallocate
CLOSE IndexCursor
DEALLOCATE IndexCursor
EXEC sp_updatestats
EXEC sp_updatestats
NC_Index_Rebuild.sql
--------------------------------------------------------------
-- Doing Nonclustered Index Rebuild --
-- Author: Victor Hu --
--------------------------------------------------------------
--Specify the database that you want to rebuild the indexes.
USE AdventureWorks2012
GO
DECLARE @Command NVARCHAR(4000);
DECLARE @ID INT;
DECLARE IndexCursor CURSOR FOR
SELECT ID, Command
FROM [IndexMaintenance].[dbo].[NC_INDEX_REBUILD_SCHEDULE]
WHERE [ReindexDate] IS NULL
OPEN IndexCursor
WHILE 1=1
BEGIN
FETCH NEXT FROM IndexCursor INTO @ID, @Command;
IF @@FETCH_STATUS <> 0
BEGIN
BREAK;
END
BEGIN TRY
EXEC (@command)
UPDATE [IndexMaintenance].[dbo].[NC_INDEX_REBUILD_SCHEDULE] SET [ReindexDate] = GETDATE() WHERE ID = @ID;
PRINT @command;
END TRY
BEGIN CATCH
PRINT 'Get error when ' + @command;
PRINT 'ErrorMessage: ' + ERROR_MESSAGE();
END CATCH
END
--Close Cursor And Deallocate
CLOSE IndexCursor
DEALLOCATE IndexCursor
C_Index_Rebuild.sql
--------------------------------------------------------------
-- Doing Clustered Index Rebuild --
-- Author: Victor Hu --
--------------------------------------------------------------
--Specify the database that you want to rebuild the indexes.
USE AdventureWorks2012
GO
DECLARE @Command NVARCHAR(4000);
DECLARE @ID INT;
DECLARE IndexCursor CURSOR FOR
SELECT ID, Command
FROM [IndexMaintenance].[dbo].[C_INDEX_REBUILD_SCHEDULE]
WHERE [ReindexDate] IS NULL
OPEN IndexCursor
WHILE 1=1
BEGIN
FETCH NEXT FROM IndexCursor INTO @ID, @Command;
IF @@FETCH_STATUS <> 0
BEGIN
BREAK;
END
BEGIN TRY
EXEC (@command)
UPDATE [IndexMaintenance].[dbo].[C_INDEX_REBUILD_SCHEDULE] SET [ReindexDate] = GETDATE() WHERE ID = @ID;
PRINT @command;
END TRY
BEGIN CATCH
PRINT 'Get error when ' + @command;
PRINT 'ErrorMessage: ' + ERROR_MESSAGE();
END CATCH
END
--Close Cursor And Deallocate
CLOSE IndexCursor
DEALLOCATE IndexCursor
