By David Wiseman (Administrator)Created 11 Feb 2011, Modified 01 Mar 2011
My Rating:
Vote
Rating:
Not Rated
Views:6345
Downloads:38
Source:

Defrag Indexes For Database

Language:  T-SQL

Compatibility

SQL Server 7 No SQL Server 2000 No
SQL Server 2005 Yes SQL Server 2008 Yes
Description

This script will defrag all the indexes in a database that are above a specified fragmentation threshold. You can control the level of fragmentation that triggers a REORGANIZE and the level of fragmentation that triggers a REBUILD. You can also choose to rebuild your indexes online and sort in tempdb.


Notes

What is Index Fragmentation?

Index fragmentation is categorised in two types internal and external.  Fragmentation also occurs at the file system level, but this is separate from index fragmentation which is the main focus of this article.  The types of fragmentation are discussed in the following sections.

Internal Fragmentation

The level of internal fragmentation in an index is referring to the "fullness" of the index pages.  In some cases it might be desirable to have a certain amount of internal fragmentation as it can improve the performance of inserts/updates. This is because there is room available in the index pages so that new data can be inserted without incurring the performance penalty of a page split.  A page split is what occurs when there is no room on an index page to insert new data and SQL server moves approximately half the data to a new page to make room for the new data.
 
An index that has heavy internal fragmentation will take up additional disk space and incur additional I/O when data is read from the index. Indexes with low internal fragmentation are more compact and more efficient when reading data from the index, but might incur some additional overhead for inserts and updates. 
 
You can set a fill factor to introduce internal fragmentation into your indexes, which you might want to do for certain tables that receive large volumes of insert/update operations.  You wouldn't want to lower the fill factor for indexes on tables that receive relatively few inserts/updates or indexes where new data is logically inserted at the end of an index.  It's quite common to have tables with an identity column and these are often used as the table's primary key and clustered index.  In this case, new data is always inserted at the end of the index so introducing a lower fill factor is only going to have a negative impact on read performance and increase the storage requirements for the index.  You might also have a table that is using a UNIQUEIDENTIFIER as a primary key/clustered index with new values been generate by the NEWID() function.  In this case, new data is inserted randomly throughout the index causing a large number of page splits and poor performance with too high a fill factor.  Note: If you use the UNIQUEIDENTIFIER data type, a better option is to use the NEWSEQUENTIALID() function so that it behaves similar to an identity column where data is inserted at the end of the index. 
 
The fill factor value is specified as a percentage between 1 and 100, with a default server-wide setting of 0.  A fill factor of 0 is the same as a fill factor of 100, where leaf level pages are filled to capacity.  A fill factor value of 80 would leave approximately 20% space free at the leaf level of the index, potentially reducing the amount of page splits and improving insert/update performance.  At the same time this would also increase the size of the index by about 20% and reduce read performance. The fill factor setting is applied when the index is created or rebuilt, but is not maintained as data changes in the index.  If SQL Server was to maintain the fill factor, this would incur additional page splits which would defeat the purpose of the fill factor setting.  If you start out with a high fill factor, the value might also be lowered over time due to page splits.
 
In most situations there is no need to change the server level default setting for the fill factor.  As mentioned previously there might be certain indexes that would benefit from a lower fill factor and this can be set on an index by index basis by rebuilding the index with a lower fill factor.  It's worth noting that in most cases data will be read from the index much more than it is written to so a high fill factor is often a good choice. 

External Fragmentation

External fragmentation refers to the amount of out-of-order leaf pages in an index, normally expressed as a percentage.  Unlike internal fragmentation, there is no benefit in the index leaf pages been out-of-order and a percentage as close to zero as possible is desired.  External fragmentation also occurs as a result of page splits, which maintain the logical order of the index with previous and next pointers but not the physical order.  Out of order pages are not necessarily a problem for all query types but they do reduce the performance of ordered scans, where data in the index needs to be fetched back in logical order.  For example, if you have an index on a date column, a query requesting the range from 1st Jan to 31st of Jan might result in an ordered scan. 

File System Fragmentation

File system fragmentation occurs at the o/s level and it's completely separate from the internal and external fragmentation that is reported by SQL Server.  It's possible for SQL Server to report very low levels of internal and external fragmentation and for your database files to be severely fragmented at the file system level.  It's also possible for the reverse to be true where the file system reports very low levels of fragmentation, but the internal structure of your database is heavily fragmented.  If you perform an o/s level defragmentation, the internal contents of your database files will remain unchanged including the fragmentation levels of your index.  Defragmenting indexes in SQL Server won't improve the file system fragmentation levels of your database files. 
 
It's worth noting that file system fragmentation can largely be avoided by proper management of the growth of your database files.  You should avoid growing your database files in small increments and pre-allocate space in your database files rather than relying on auto growth.  You should keep your database files on dedicated volumes to avoid the drives been fragmented by other processes.  If you use database snapshots, I strongly advise that these are allocated their own drive separate from your other database files.

Index Maintenance

Indexes can become fragmented over time, both in terms of internal an external fragmentation, resulting in a degradation in performance.  There are two different methods you can use to reduce fragmentation and improve the performance of your indexes; an index rebuild or an index reorganize.  An index reorganize will defragment the leaf levels of clustered an nonclustered indexes by physically moving data so that the physical order of the index matches the logical order of the index.  The process is considered an "online" operation as it doesn't hold any long-term locks that might cause blocking in your database. 
 
An index rebuild is the same as dropping and re-creating the index.  By default this is considered an offline operation as long term locks are held during the index rebuild process, which can cause severe blocking in your database.  If you are using the Enterprise, Developer or Evaluation editions of SQL Server, you have the option to rebuild your indexes online.  Locks are still used during the online index rebuild, but they are held for a much shorter period of time.  There are some limitations that apply to online index rebuilds that need to be considered.  For example, online index rebuilds are not supported for XML indexes or indexes that contain LOB data (image, text, NVARCHAR(MAX), VARCHAR(MAX) etc).  They are also not supported on partitioned indexes and disabled indexes.
 
If the level of fragmentation is high (>30%), you should consider rebuilding your indexes and for lower fragmentation levels, you should consider an index reorganize.

How Often?

You should always attempt to perform index maintenance out of hours or use an online method of index maintenance during periods of very low system activity if your databases are online 24x7.  Your maintenance windows will dictate to a certain extent when and how often you are able to perform index maintenance. 
 
It's worth noting that index fragmentation occurs as a result of insert, update and delete statements and you don't need to perform index maintenance more frequently than the data changes. You might have some databases that are read only or ones that are updated infrequently.  For example, if you have a data warehouse (reporting database) that is updated on a monthly basis, it's unnecessary to perform weekly index rebuilds as the database is essentially read only between data loads.  In the case of a data warehouse, it might be appropriate to perform index maintenance after loading new data into the database. 

Index Maintenance Strategy

If your maintenance window allows for it, the easiest option is simply to rebuild all the indexes in your database.  You could do this using a database maintenance plan or using a script similar to the one below:
 
DECLARE @SQL NVARCHAR(MAX)
SELECT @SQL = (
SELECT N'ALTER INDEX ALL ON ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name) + N' REBUILD
'

FROM sys.tables t
JOIN sys.schemas s on t.schema_id = s.schema_id
FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)')

exec
sp_executesql @SQL
 
The script doesn't specify a fill factor so the fill factor that was set when the index was created or last rebuilt will be used.  This is normally the best option to use for index maintenance scripts that rebuild all of your indexes as you might want to tailor this value for individual indexes as required. 
 
If your database is large, it's likely to take a considerable amount of time to rebuild all your indexes so it's often better to take a more targeted approach.  One option is to  assess the levels of fragmentation by using the "sys.dm_db_index_physical_stats" dynamic management function and decide which indexes to rebuild based on the levels of fragmentation.  For example, the following query returns fragmentation statistics for all the indexes in the current database:
 
SELECT
s.name as schema_name,
            so.name as table_name,
            si.name as index_name,
            partition_number,
            index_type_desc,index_depth,
            index_level,
            avg_fragmentation_in_percent,
            fragment_count,
            avg_fragment_size_in_pages,
            page_count
FROM sys.dm_db_index_physical_stats(DB_ID(),NULL, NULL, NULL , 'LIMITED') ips
JOIN sys.indexes si on ips.object_id = si.object_id and ips.index_id = si.index_id
JOIN sys.objects so on ips.object_id = so.object_id
JOIN sys.schemas s on so.schema_id = s.schema_id
 
The "avg_fragmentation_in_percent" column gives you the level of external fragmentation and it's this value that it used for the index maintenance stored procedure in this article to decide which indexes to reorganize or rebuild.  You can find more information about the "sys.dm_db_index_physical_stats" dynamic management function here.

Index Maintenance Stored Procedure

The stored procedure accompanying this article makes use of the  sys.dm_db_index_physical_stats function mentioned previously to identify the levels of external fragmentation in your indexes.  Indexes are then rebuilt or reorganized based on the thresholds you specify to the stored procedure.  The parameters for the stored procedure are described in the table below:
 
Parameter Name Description
@ReorganizeThresold The value specified here indicates the minimum level of fragmentation that will trigger index maintenance.  The type of index maintenance would be an index reorganize unless the level of fragmentation also exceeds the @RebuildThreshold. 
@RebuildThreshold If the level of fragmentation is greater than the rebuild threshold an index rebuild is performed instead of a reorganize.  If you use a value greater than 100 for the rebuild threshold, the stored procedure will always use an index reorganize instead of a rebuild.
If you set the reorganize threshold to the same value as the rebuild threshold, a rebuild will always be used unless the online option is also specified.  If the online option is specified a reorganize would be used in those cases where it is not possible to perform an online rebuild.
@DatabaseName This is the name of the database where you want to defragment your indexes.
@Online If you are running the Enterprise, Developer or Evaluation editions of SQL Server, this option can be used to perform online index rebuilds.  If it's not possible to do an online index rebuild, a reorganize will be used instead.
@Debug This parameter can be set to 1 if you want to see what commands would be issued by the stored procedure without running them.  In most cases this parameter should be ignored or set to the default value of 0.
@SortInTempDB This parameter affects index rebuild operations by including the SORT_IN_TEMPDB=ON option.  This option can be used to improve the performance of index rebuilds if the storage of the tempdb database is appropriately designed.
@PageCountThreshold It can sometimes be useful to exclude small indexes from your index maintenance scripts.  By default the script will exclude indexes with a page count less than 512 (4MB). 
 
Although the stored procedure has a number of parameters, most of them have default values.  In it's simplest form you can execute the script just by passing in the name of the database you want to defragment:

EXEC dbo.DefragIndexesForDB @DatabaseName='AdventureWorks'

This is equivalent to the statement below:
 
EXEC dbo.DefragIndexesForDB 
      @ReorganizeThreshold=15,
      @RebuildThreshold=30,
      @DatabaseName='AdventureWorks',
      @Online =1
 
In addition to the stored procedure, two tables are also created to track your index maintenance; index_maintenance_batch and index_maintenance_log.  A single row is inserted into the index_maintenance_batch table every time you execute the DefragIndexesForDB stored procedure.  This tracks information such as the start time, end time and parameter options chosen for the index maintenance.  A row is inserted into the index_maintenance_log table for every index rebuild or reorganize operation and is linked to the index_maintenance_batch table by the batch_id column.
 
 
 
Code

Line Numbers: On  Off      Plain Text
/* Prerequsites
	Requires dbo.fnSplitString function:
	http://www.wisesoft.co.uk/scripts/t-sql_split_string_function_while_loop.aspx
	or
	http://www.wisesoft.co.uk/scripts/t-sql_cte_split_string_function.aspx
*/
-- Remove objects from DB if they already exists (uncomment if required)
/*
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[index_maintenance_log]') AND type in (N'U'))
DROP TABLE [dbo].[index_maintenance_log]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[index_maintenance_batch]') AND type in (N'U'))
DROP TABLE [dbo].[index_maintenance_batch]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DefragIndexesForDB]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[DefragIndexesForDB]
GO
*/
CREATE TABLE dbo.index_maintenance_batch(
	batch_id INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_index_maintenance_batch PRIMARY KEY(batch_id),
	start_time DATETIME NOT NULL,
	end_time DATETIME NULL,
	reorganize_threshold TINYINT NOT NULL,
	rebuild_threshold TINYINT NOT NULL,
	database_name SYSNAME NOT NULL,
	is_online BIT NOT NULL,
	[sort_in_tempdb] BIT NOT NULL,
	page_count_threshold INT NOT NULL,
	excluded_tables NVARCHAR(MAX)NULL
)
GO
CREATE TABLE dbo.index_maintenance_log(
	log_id INT IDENTITY(1,1) CONSTRAINT PK_index_maintenance_log PRIMARY KEY(log_id),
	batch_id INT NOT NULL CONSTRAINT FK_index_maintenance_log_index_maintenance_batch FOREIGN KEY REFERENCES dbo.index_maintenance_batch(batch_id),
	[schema_name] SYSNAME NOT NULL,
	[object_name] SYSNAME NOT NULL,
	index_name SYSNAME NOT NULL,
	index_type_desc NVARCHAR(60) NOT NULL,
	partition_number INT NOT NULL,
	avg_fragmentation_in_percent float NOT NULL,
	is_rebuild bit NOT NULL,
	is_online bit NOT NULL,
	start_time DATETIME NOT NULL,
	end_time DATETIME NULL
)
GO
CREATE PROC [dbo].[DefragIndexesForDB](
	-- Threshold to perform index maintenance. Fragmentation levels below this value will be ignored.
	@ReorganizeThreshold TINYINT=15,
	-- Threshold to rebuild indexes rather than reorganize. 
	-- If you don't want to use rebuild, set the value to >100.  
	-- If you want to rebuild rather than reorganize, set the value to the same as the ReorganizeThreshold
	@RebuildThreshold TINYINT=30,
	-- Database to defrag
	@DatabaseName SYSNAME,
	-- If specified all rebuilds will be done online.  In cases where that is not possible, the index will be reorganized, regardless of the RebuildThreshold
	-- The online option is only available in enterprise, developer and evaluation editions of SQL Server.  Set the rebuild threhold greater than 100 to use a reorganize instead.
	-- Note: It's strongly recommended to perform index maintenance out of hours, even with the online option set to 1
	@Online BIT=1,
	-- If specified, index rebuild statements will be printed and won't be run
	@Debug BIT=0,
	-- Option to sort index in tempdb
	@SortInTempDB BIT=1,
	-- Used to exclude small indexes.  
	@PageCountThreshold INT=512, -- Default value = 512 pages/4MB
	-- Option to exclude tables from index maintenance
	-- Should be a comma separated string of tables names to exclude
	-- e.g. 'dbo.MyExcludedTable,dbo.MyExcludedTable2,dbo.MyExcludedTable3'
	@ExcludeTables NVARCHAR(MAX)=NULL
)
/*  Created:	13/10/2009
	Updated:	01/03/2011
	Version:	1.02
	Author:		David Wiseman
	Website:	http://www.wisesoft.co.uk
	Purpose:	Procedure to defrag indexes for a given database, based on levels of fragmentation specified.
	Notes:		Ignores small indexes (less than 512 pages/4MB) and disabled indexes.
				Requires dbo.fnSplitString function:
				http://www.wisesoft.co.uk/scripts/t-sql_split_string_function_while_loop.aspx
				or
				http://www.wisesoft.co.uk/scripts/t-sql_cte_split_string_function.aspx
	Example:
	
	/*		Reorganize all indexed in "AdventureWorks" database with an avg_fragmentation_in_percent between 15 and <30.
			Rebuild indexes in "AdventureWorks" database with an avg_fragmentation_in_percent between greater than or equal to 30
			Perform index maintenance online - either a REBUILD(WITH ONLINE=ON) or a REORGANIZE.  Note: Indexes might use a REORGANIZE
			instead of a REBUILD to satisfy the online requirement. Also, you need enterprise, developer or evaluation editions of
			SQL server to use the online option.
	*/
	EXEC dbo.DefragIndexesForDB
		@ReorganizeThreshold=15,
		@RebuildThreshold=30,
		@DatabaseName='AdventureWorks',
		@Online =1
		
	The thresholds and options above are also the default values so the statement could be simplified to:
	
	EXEC dbo.DefragIndexesForDB @DatabaseName='AdventureWorks'
*/
/*	Updated:	10/02/2011
	Version:	1.01
	Author:		David Wiseman
	Changes:	Code refactoring for initial release.
*/
/*	Updated:	01/03/2011
	Version:	1.02
	Author:		David Wiseman
	Changes:	Added option to exclude tables.
				Modified logging so row is inserted before index is reorganized/rebuilt and end_time is updated afterwards.
				Ensures we have a record of all attempted index maintenance, even if it doesn't complete. Also added missing
				index_name data from index_maintenance_log table.
				
*/
AS
SET NOCOUNT ON;
CREATE TABLE #Frag(
	[object_id] INT,
	index_id INT,
	[object_name] SYSNAME,
	[schema_name] SYSNAME,
	index_name SYSNAME,
	partition_number INT,
	avg_fragmentation_in_percent FLOAT,
	index_type_desc NVARCHAR(60)
);

DECLARE @ObjectID INT;
DECLARE @IndexID INT;
DECLARE @IndexName SYSNAME;
DECLARE @ObjectName SYSNAME;
DECLARE @SchemaName SYSNAME;
DECLARE @PartitionNumber INT;
DECLARE @PartitionCount INT;
DECLARE @Frag FLOAT;
DECLARE @IndexType NVARCHAR(60);
DECLARE @IndexSQL NVARCHAR(MAX);
DECLARE @SQL NVARCHAR(MAX);
DECLARE @BatchID INT;
DECLARE @IsRebuild BIT;
DECLARE @IsOnline BIT;
DECLARE @CanRebuildOnline BIT;
DECLARE @EditionID INT;

SET @EditionID = CONVERT(INT,SERVERPROPERTY ('EditionID'));

-- Check input paramters are valid
IF DB_ID(@DatabaseName) IS NULL
BEGIN;
	RAISERROR('Invalid database name specified',11,1);
	RETURN;
END;
IF @ReorganizeThreshold IS NULL
BEGIN;
	RAISERROR('@ReorganizeThreshold parameter is not allowed to be NULL',11,1);
	RETURN;
END;
IF @RebuildThreshold IS NULL
BEGIN;
	RAISERROR('@RebuildThreshold parameter is not allowed to be NULL',11,1);
	RETURN;
END;
IF @Online IS NULL
BEGIN;
	RAISERROR('@Online parameter is not allowed to be NULL',11,1);
	RETURN;
END;
IF @Debug IS NULL
BEGIN;
	RAISERROR('@Debug parameter is not allowed to be NULL',11,1);
	RETURN;
END;
IF @SortInTempDB IS NULL
BEGIN;
	RAISERROR('@SortInTempDB parameter is not allowed to be NULL',11,1);
	RETURN;
END;
IF @ReorganizeThreshold >= 100
BEGIN
	RAISERROR('The @ReorganizeThreshold value should be less than 100',11,1);
	RETURN;
END
IF @RebuildThreshold < @ReorganizeThreshold 
BEGIN;
	RAISERROR('The @RebuildThreshold value should be greater than or equal to the @ReorganizeThreshold',11,1);
	RETURN;
END;
IF @PageCountThreshold IS NULL
BEGIN;
	RAISERROR('@PageCountThreshold parameter is not allowed to be NULL',11,1);
	RETURN;
END;

IF @Debug = 0
BEGIN;
	INSERT INTO dbo.index_maintenance_batch(
		start_time,
		reorganize_threshold,
		rebuild_threshold,
		database_name,
		is_online,
		[sort_in_tempdb],
		page_count_threshold,
		excluded_tables
	)
	SELECT GETDATE() as start_time,
		@ReorganizeThreshold,
		@RebuildThreshold,
		@DatabaseName,
		@Online,
		@SortInTempDB,
		@PageCountThreshold,
		@ExcludeTables;
END;

SET @BatchID = SCOPE_IDENTITY();

SET @SQL = 'INSERT INTO #Frag(object_id,index_id,object_name,schema_name,index_name,partition_number,avg_fragmentation_in_percent,index_type_desc)
SELECT ps.object_id,
	ps.index_id,
	o.name,
	s.name,
	si.name,
	ps.partition_number,
	ps.avg_fragmentation_in_percent,
	ps.index_type_desc
FROM sys.dm_db_index_physical_stats(DB_ID(@DatabaseName),NULL, NULL, NULL , ''LIMITED'') ps
JOIN ' + QUOTENAME(@DatabaseName) + '.sys.objects o ON ps.object_id = o.object_id 
JOIN ' + QUOTENAME(@DatabaseName) + '.sys.schemas s ON o.schema_id = s.schema_id
JOIN ' + QUOTENAME(@DatabaseName) + '.sys.indexes si on ps.index_id = si.index_id and ps.object_id = si.object_id
WHERE ps.index_id >0
AND ps.page_count > @PageCountThreshold --Exclude small indexes
AND ps.avg_fragmentation_in_percent >= @ReorganizeThreshold
AND si.is_disabled = 0 -- Ignore disabled indexes.
AND index_type_desc IN(''NONCLUSTERED INDEX'',''CLUSTERED INDEX'',''XML INDEX'',''PRIMARY XML INDEX'')' 
+ CASE WHEN @ExcludeTables IS NULL THEN '' ELSE
'
AND NOT EXISTS(SELECT 1
				FROM dbo.fnSplitString(@ExcludeTables,'','') ss 
				WHERE (s.name + ''.'' + o.name) = ss.value
				)' END + '
ORDER BY o.name,ps.index_type_desc';

-- Get index fragmentation stats for specified database
EXEC sp_executesql @sql,N'@DatabaseName SYSNAME,@ReorganizeThreshold INT,@PageCountThreshold INT,@ExcludeTables NVARCHAR(MAX)',
						@DatabaseName,@ReorganizeThreshold,@PageCountThreshold,@ExcludeTables;

DECLARE cFrag CURSOR FAST_FORWARD
	FOR SELECT [object_id],index_id,[object_name],[schema_name],index_name,partition_number,avg_fragmentation_in_percent,index_type_desc
	FROM #Frag;
OPEN cFrag;

FETCH NEXT FROM cFrag INTO @ObjectID,@IndexID,@ObjectName,@SchemaName,@IndexName,@PartitionNumber,@Frag,@IndexType;
WHILE @@FETCH_STATUS = 0
BEGIN;
	-- Check if edition of SQL Server supports online indexing
	IF @EditionID IN(1804890536, -- Enterprise
					-2117995310, -- Developer
					610778273) --Enterprise Evaluation
	BEGIN
		SET @CanRebuildOnline = 1
	END
	ELSE
	BEGIN
		SET @CanRebuildOnline = 0
	END
	
	-- Get Partition Count
	SET @SQL = N'SELECT @PartitionCount = COUNT(*)
				FROM ' + QUOTENAME(@DatabaseName) + '.sys.partitions
				WHERE [object_id] = @objectid 
				AND index_id = @indexid'
				
	EXEC sp_executesql @SQL,N'@ObjectID INT,@IndexID INT,@PartitionCount INT OUT',@ObjectID,@IndexID,@PartitionCount OUT
						
    
    SET @IndexSQL = 'ALTER INDEX ' +  QUOTENAME(@IndexName) + ' ON ' + QUOTENAME(@DatabaseName) + '.' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@ObjectName);
 		
	IF @IndexType = 'NONCLUSTERED INDEX' 
	BEGIN;
		-- Check for LOB data types.  Online Rebuilds not supported for LOB data
		SET @SQL = 'IF EXISTS(select * 
								FROM ' + QUOTENAME(@DatabaseName) + '.sys.index_columns ic
								JOIN ' + QUOTENAME(@DatabaseName) + '.sys.columns c on ic.column_id = c.column_id and ic.object_id = c.object_id
								JOIN ' + QUOTENAME(@DatabaseName) + '.sys.types t ON c.user_type_id = t.user_type_id
								where ( t.name IN (''xml'',''text'', ''ntext'',''image'')
										 OR (t.name IN (''varchar'',''nvarchar'',''varbinary'')
											AND c.max_length = -1)
										)
								AND ic.object_id = @ObjectID 
								and ic.index_id = @IndexID
								)
							SET @CanRebuildOnline = 0';
		EXEC sp_executesql @SQL,N'@IndexID int,@ObjectID int,@CanRebuildOnline bit OUT',@IndexID,@ObjectID,@CanRebuildOnline OUT;
	
	END;
	IF @IndexType = 'CLUSTERED INDEX'
	BEGIN;
		-- Check for LOB data types.  Online Rebuilds not supported for LOB data
		SET @SQL =  'IF EXISTS(
					-- This will check is a table contains lob types
					SELECT 1 
					FROM ' + QUOTENAME(@DatabaseName) + '.sys.tables 
					WHERE lob_data_space_id >0
					AND object_id = @ObjectID
					UNION ALL
					-- Also need to account for indexed views. (Also works with tables)
					-- Ensure we don''t have text,ntext, image or xml columns
					SELECT 1 
					FROM ' + QUOTENAME(@DatabaseName) + '.sys.columns c
					JOIN ' + QUOTENAME(@DatabaseName) + '.sys.types t ON t.system_type_id = c.system_type_id
					WHERE OBJECT_ID = @ObjectID
					AND t.name IN(''text'',''ntext'',''image'',''xml'')
					UNION ALL
					-- ensure we don''t have varchar(max), nvarchar(max) or vibinary(max) columns
					SELECT 1 
					FROM ' + QUOTENAME(@DatabaseName) + '.sys.columns
					WHERE OBJECT_ID = @ObjectID
					AND max_length = -1

					)
					SET @CanRebuildOnline=0';
		EXEC sp_executesql @SQL,N'@ObjectID int,@CanRebuildOnline bit OUT',@ObjectID,@CanRebuildOnline out;
					
	END;
	-- Online index rebuild not supported for XML indexes
	IF @IndexType='XML INDEX'
	BEGIN;
		SET @CanRebuildOnline = 0;
	END;
	-- Online index rebuild not supported for partitioned indexes
	IF @PartitionCount >1
	BEGIN;
		SET @CanRebuildOnline=0;
	END;

	IF @CanRebuildOnline = 1 AND @Online =1 AND @Frag >= @RebuildThreshold
	BEGIN
		-- Online rebuild is performed if the online option is requested, online rebuild is supported and the fragmentation level is greater than the 
		-- rebuild threshold
		SET @IndexSQL = @IndexSQL + ' REBUILD' + 
									CASE WHEN @PartitionCount >1 THEN ' PARTITION = ' + CAST(@PartitionNumber as nvarchar(10)) ELSE '' END + 
										' WITH(ONLINE=ON' + 
											CASE WHEN @SortInTempDB = 1 THEN ',SORT_IN_TEMPDB=ON' ELSE '' END + 
											')';
		SET @IsOnline=1;
		SET @IsRebuild=1;
	END
	ELSE IF @Online = 0 AND @Frag >= @RebuildThreshold
	BEGIN
		-- An "offline" rebuild is performed if the fragmentation level is greater than the rebuild threshold and the user didn't request the online option
		SET @IndexSQL = @IndexSQL + ' REBUILD' +
									CASE WHEN @PartitionCount >1 THEN ' PARTITION = ' + CAST(@PartitionNumber as nvarchar(10)) ELSE '' END + 
									CASE WHEN @SortInTempDB=1 THEN ' WITH(SORT_IN_TEMPDB=ON)' ELSE '' END;
		SET @IsOnline=0;
		SET @IsRebuild=1;
	END
	ELSE
	BEGIN
		-- Reorganize if fragmentation is lower than rebuild threshold or online mode is requested, but is not supported 
		-- (e.g. not running SQL Enterprise, Developer or Evaluation, index contains LOB data etc.)
		SET @IndexSQL = @IndexSQL + ' REORGANIZE';
		SET @IsRebuild=0;
		SET @IsOnline=1;
	END
	
	-- If debug mode is specified, just print the code for the index maintenance
	IF @Debug = 1
	BEGIN;
		PRINT @IndexSQL;
	END;
	ELSE
	BEGIN;
		DECLARE @LogID INT
		INSERT INTO dbo.index_maintenance_log(
			batch_id,
			[schema_name],
			[object_name],
			index_name,
			index_type_desc,
			partition_number,
			avg_fragmentation_in_percent,
			is_rebuild,
			is_online,
			start_time
		)
		SELECT @BatchID,
			@SchemaName,
			@ObjectName,
			@IndexName,
			@IndexType,
			@PartitionNumber,
			@Frag,
			@IsRebuild,
			@IsOnline,
			GETDATE();
		
		SET @LogID = SCOPE_IDENTITY()

		-- Run Index Rebuild
		EXEC sp_executesql @IndexSQL;
		
		UPDATE dbo.index_maintenance_log
			SET end_time = GETDATE()
		WHERE log_id = @LogID;
			
	END;
	
	FETCH NEXT FROM cFrag INTO @ObjectID,@IndexID,@ObjectName,@SchemaName,@IndexName,@PartitionNumber,@Frag,@IndexType;
END;

CLOSE cFrag;
DEALLOCATE cFrag;

IF @Debug=0
BEGIN
	-- set end time
	UPDATE index_maintenance_batch 
		SET end_time = GETDATE()
	WHERE batch_id = @BatchID;

	-- display stats for index maintenance
	SELECT imb.batch_id,
		imb.start_time,
		imb.end_time,
		CONVERT(CHAR(8),DATEADD(s,DATEDIFF(s,imb.start_time,imb.end_time),0),108) AS total_time,
		CONVERT(CHAR(8),DATEADD(ms,ISNULL(SUM(DATEDIFF(ms,iml.start_time,iml.end_time)),0),0),108) AS index_rebuild_reorg_time,
		COUNT(iml.log_id) as index_rebuild_reorg_count,
		SUM(CASE WHEN iml.index_type_desc = 'CLUSTERED INDEX' AND iml.is_rebuild = 1 THEN 1 ELSE 0 END) as clustered_index_rebuilds,
		SUM(CASE WHEN iml.index_type_desc = 'CLUSTERED INDEX' AND iml.is_rebuild = 0 THEN 1 ELSE 0 END) as clustered_index_reorgs,
		SUM(CASE WHEN iml.index_type_desc = 'NONCLUSTERED INDEX' AND iml.is_rebuild = 1 THEN 1 ELSE 0 END) as nonclustered_index_rebuilds,
		SUM(CASE WHEN iml.index_type_desc = 'NONCLUSTERED INDEX' AND iml.is_rebuild = 0 THEN 1 ELSE 0 END) as nonclustered_index_reorgs,
		SUM(CASE WHEN iml.index_type_desc = 'XML INDEX' AND iml.is_rebuild = 1 THEN 1 ELSE 0 END) as xml_index_rebuilds,
		SUM(CASE WHEN iml.index_type_desc = 'XML INDEX' AND iml.is_rebuild = 0 THEN 1 ELSE 0 END) as xml_index_reorgs
	FROM dbo.index_maintenance_batch imb 
	LEFT JOIN dbo.index_maintenance_log iml on imb.batch_id = iml.batch_id
	WHERE imb.batch_id = @BatchID
	GROUP BY imb.batch_id,
		imb.start_time,
		imb.end_time;
END

DROP TABLE #Frag;


GO

 


Got a useful script? Click here to upload!


 

  Post Comment
Order By:  
User Comments
      
Be the first to post a comment!