Intelligent Index Reorganize and Rebuild Script - v1.0
Description:
This script will provide a dynamic mechanism to decide between REBUILDING an index or simply REORGANIZING an index. This will improve performance when this critical maintenance process executes. This version of the script provided has been tested and approved for use on SQL Server 2005 & 2008 R2 Standard or Enterprise or R2. This script is very thorough and provides several intelligence mechanisms for determining when and how to perform index maintenance on a given table.
Rules For Index Maintenance:
1.) Are there open cursors in the database, if so skip the database.
2.) Index Size is greater than 5 MB's.
3.) Reorganize = fragmentation level is between 5% and 30%
4.) Rebuild = fragmentation level is greater than 30%
In addition to the index maintenance script provided, there is a table named IndexMaintenanceHistory that will collect the historical runs of the index maintenance job. This is useful for auditing purposes when you need to find out if certain indexes are having maintenance completed as required.
Results:
I have found this script to provide a significant improvement in runtime for index maintenance on our SQL Servers across the board. Because the script makes the decision between rebuilding or reorganizing the index, I can execute the index maintenance job more often without over burdening the SQL Server during the nightly maintenance window.
As always, I highly recommend running an Update Statics job separately from this script to ensure that all index statistics are up to date.
/****** Object: StoredProcedure [dbo].[ReorgRebuildIndex] Script Date: 10/18/2012 09:23:50 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ReorgRebuildIndex]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[ReorgRebuildIndex]
GO
/****** Object: StoredProcedure [dbo].[ReorgRebuildIndex] Script Date: 10/18/2012 09:23:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[ReorgRebuildIndex]
AS
/**********************************************************************************************
Description: This script will provide a dynamic mechanism to decide between REBUILDING an
index or simply REORGANIZING an index. This will improve performance
when this critical maintenance process executes. Tested and approved for
SQL Server 2005 & 2008 Standard or Enterprise or R2.
-----------------------------------------------------------------------------------------------
DateTime Developer Revision Purpose
02/08/2012 Tim Parker 1.0.0.0 Initial Release
03/07/2012 Tim Parker 1.2.0.0 Modified to collect the table, index command and
type of of maintenance performed. This will
provide a historical log of index maintenenace.
03/16/2012 Tim Parker 1.3.0.0 Add the statement
REBUILD WITH (FILLFACTOR = 90, SORT_IN_TEMPDB = ON)
to increase efficiency of Index rebuilds.
03/19/2012 Tim Parker 1.4.0.0 Added "page_count" to the filter to make sure we
are only working with indexes that countain 640
pages (roughly 5 MB's). Rebuilding or reoganizing
small indexes is a waste of time since the pages
will be stored in mixed extents.
03/26/2012 Tim Parker 1.4.1.0 Removed the SORT_IN_TEMPDB = ON statement after
the TEMPDB on the IPRO SQL Server swelled to over
16 GB's. This may not have caused the problem, but
I would rather be safe then sorry.
04/23/2012 Tim Parker 2.0.0.0 Modified script to check for open cursors on databases
prior to executing an index maintenance task. Primarily
this addresses issues found with the Law application here
at the LTSC. In addition, we will capture which databases
are being skipped due to open cursors.
***********************************************************************************************/
DECLARE @indexCommandString VARCHAR(5000),
@dynSQL VARCHAR(5000),
@databaseName VARCHAR(255),
@executionStartTime DATETIME,
@executionCompleteTime DATETIME
CREATE TABLE #indexCommands
(
indexCommand VARCHAR(4000)
)
--Record which databases are being skipped for index maintenance due to open cursors.
SET @dynSQL = 'INSERT INTO dbo.IndexMaintenanceHistory
(
DatabaseName,
IndexCommandString,
DateTimeExecuted,
DateTimeCompleted
)
SELECT NAME,
''Index maintenance skipped for database ''+ NAME +'' for an active CURSOR statement.'',
GETDATE(),
GETDATE()
FROM sys.sysdatabases
WHERE DBID > 4 AND dbid IN (
SELECT DISTINCT PRO.dbid
FROM sys.dm_exec_cursors(0) CURS INNER JOIN sys.sysprocesses PRO
ON CURS.session_ID = PRO.spid
WHERE is_open =1
)
AND dbid NOT IN (
SELECT DISTINCT SP.[dbid]
FROM sys.sysprocesses SP CROSS APPLY sys.dm_exec_sql_text(sql_handle)
WHERE [TEXT] LIKE ''FETCH%''
)
ORDER BY NAME '
EXEC (@dynSQL)
--Begin our maintenance tasks.
DECLARE curDatabase CURSOR FOR
--This statement filters out system databases and databases with open cursors.
SELECT NAME
FROM sys.sysdatabases
WHERE DBID > 4 AND dbid NOT IN (
SELECT DISTINCT PRO.dbid
FROM sys.dm_exec_cursors(0) CURS INNER JOIN sys.sysprocesses PRO
ON CURS.session_ID = PRO.spid
WHERE is_open =1
)
AND dbid NOT IN (
SELECT DISTINCT SP.[dbid]
FROM sys.sysprocesses SP CROSS APPLY sys.dm_exec_sql_text(sql_handle)
WHERE [TEXT] LIKE 'FETCH%'
)
ORDER BY NAME
OPEN curDatabase
FETCH NEXT FROM curDatabase INTO @databaseName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @executionStartTime = GETDATE()
/**************************************************************
* Begin Index Maintenance
**************************************************************/
TRUNCATE TABLE #indexCommands
--This looks for fragmented indexes that have atleast 5 MB's of data stored.
SET @dynSQL = '
USE [' +@databaseName + ']
--Lightweight method for checking index fragmentation in a given database.
SELECT CASE WHEN avg_fragmentation_in_percent BETWEEN 5 AND 30 THEN
''ALTER INDEX ['' + name + ''] ON '' + (SELECT TOP 1 TABLE_SCHEMA FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = OBJECT_NAME(b.[OBJECT_ID]) AND TABLE_TYPE = ''BASE TABLE'')
+ ''.['' + OBJECT_NAME(b.[OBJECT_ID]) + ''] REORGANIZE ;''
WHEN avg_fragmentation_in_percent > 30 THEN
''ALTER INDEX ['' + name + ''] ON '' + (SELECT TOP 1 TABLE_SCHEMA FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = OBJECT_NAME(b.[OBJECT_ID]) AND TABLE_TYPE = ''BASE TABLE'')
+ ''.['' + OBJECT_NAME(b.[OBJECT_ID]) + ''] REBUILD WITH (FILLFACTOR = 90) ;''
END AS Index_Statement
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL,
NULL, NULL, NULL) AS a
JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id
WHERE avg_fragmentation_in_percent > 5
AND index_type_desc <> ''HEAP''
AND page_count > 640
ORDER BY avg_fragmentation_in_percent DESC'
INSERT INTO #indexCommands(indexCommand)
EXEC (@dynSQL)
DECLARE curIndex CURSOR FOR
SELECT indexCommand
FROM #indexCommands
OPEN curIndex
FETCH NEXT FROM curIndex INTO @indexCommandString
WHILE @@FETCH_STATUS = 0
BEGIN
SET @dynSQL = 'USE [' +@databaseName+ ']
' + @indexCommandString
EXEC(@dynSQL)
SET @executionCompleteTime = GETDATE()
INSERT INTO dbo.IndexMaintenanceHistory
(
DatabaseName,
IndexCommandString,
DateTimeExecuted,
DateTimeCompleted
)
VALUES
(
@databaseName,
@indexCommandString,
@executionStartTime,
@executionCompleteTime
)
FETCH NEXT FROM curIndex INTO @indexCommandString
END
CLOSE curIndex
DEALLOCATE curIndex
/**************************************************************
* End Index Maintenance
**************************************************************/
FETCH NEXT FROM curDatabase INTO @databaseName
END
CLOSE curDatabase
DEALLOCATE curDatabase
DROP TABLE #indexCommands
GO
--------------------------------------------------------------------------
/********************************************************************************
* DATE: 2012-10-18
* AUTHOR: Tim Parker
* PURPOSE: This table is used to collect index maintenance commands created and
* and executed by the stored procedure dbo.ReorgRebuildIndex. This will
* provide a historical record of index maintenance routines performed in
* the given SQL Server instance.
********************************************************************************/
/****** Object: Table [dbo].[IndexMaintenanceHistory] Script Date: 10/18/2012 09:21:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[IndexMaintenanceHistory](
[IndexCommandID] [int] IDENTITY(1,1) NOT NULL,
[DatabaseName] [varchar](255) NULL,
[IndexCommandString] [varchar](6000) NULL,
[DateTimeExecuted] [datetime] NULL,
[DateTimeCompleted] [datetime] NULL,
CONSTRAINT [PK_IndexMaintenanceHistory] PRIMARY KEY CLUSTERED
(
[IndexCommandID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
