Intelligent Index Reorganize and Rebuild Script
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.
/****** 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] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[ReorgRebuildIndex]
AS
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
--------------------------------------------------------------------------
/********************************************************************************
* 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] ******/
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
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.
/****** 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] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[ReorgRebuildIndex]
AS
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
--------------------------------------------------------------------------
/********************************************************************************
* 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] ******/
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
Comments
Post a Comment