By David Wiseman (Administrator)Created 11 Feb 2011, Modified 01 Mar 2011
My Rating:
Vote
Rating:
Not Rated
Views:12595
Downloads:44
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
      

Posted On: 10/3/2017 2:22:05 AM

fifa 18 ultimate team hack cheats fifa 18 ultimate team hack cheats fifa 18 ultimate team hack cheats fifa 18 ultimate team hack cheats fifa 18 ultimate team hack cheats fifa 18 ultimate team hack cheats fifa 18 ultimate team hack cheats fifa 18 ultimate team hack cheats fifa 18 ultimate team hack cheats fifa 18 ultimate team hack cheats fifa 18 ultimate team hack cheats fifa 18 ultimate team hack cheats fifa 18 ultimate team hack cheats fifa 18 ultimate team hack cheats fifa 18 ultimate team hack cheats fifa 18 ultimate team hack cheats fifa 18 ultimate team hack cheats fifa 18 ultimate team hack cheats fifa 18 ultimate team hack cheats fifa 18 ultimate team hack cheats fifa 18 ultimate team hack cheats fifa 18 ultimate team hack cheats fifa 18 ultimate team hack cheats fifa 18 ultimate team hack cheats fifa 18 ultimate team hack cheats fifa 18 ultimate team hack cheats fifa 18 ultimate team hack cheats fifa 18 ultimate team hack cheats fifa 18 ultimate team hack cheats fifa 18 ultimate team hack cheats fifa 18 ultimate team hack cheats fifa 18 ultimate team hack cheats fifa 18 ultimate team hack cheats fifa 18 ultimate team hack cheats fifa 18 ultimate team hack cheats fifa 18 ultimate team hack cheats fifa 18 ultimate team hack cheats fifa 18 ultimate team hack cheats fifa 18 ultimate team hack cheats fifa 18 ultimate team hack cheats fifa 18 ultimate team hack cheats fifa 18 ultimate team hack cheats fifa 18 ultimate team hack cheats fifa 18 ultimate team hack cheats fifa 18 ultimate team hack cheats

Homlessman
Posted On: 4/3/2019 7:03:43 AM

i left the house and when i got to a close friend of mine house i told her what was happening and she told me to clean my tears that there is a man who is going to help me i was so happy because i needed this help so we both contacted Dr ikuku and told him the situation he told me not to worry that he assures me that within 24 hours that the divorce would be canceled i was so happy believe when the 24 hours was completed i got a call from the lawyer that my husband his asking him to cancel the divorce https://www.tradingfloor.com/traders/jamesjeel

xiaoou
Posted On: 4/25/2019 9:43:23 AM

guess clothing cheap jordans ferragamo outlet store true religion jeans ed hardy pandora jewelry outlet prada handbags giants jerseys nike air max 90 oakley sunglasses outlet seahawks jerseys kobe shoes birkenstock outlet michael kors outlet clearance rolex replica watches for sale yeezy shoes hermes bags nobis jackets red bottoms yeezy shoes uggs outlet louis vuitton purses michael kors outlet air jordan shoes air max 97 coach factorty outlet store maui jim sunglasses nike factory store salvatore ferragamo shoes patriots jerseys nike outlet online air jordan uk ralph lauren outlet air max plus barcelona jersey oakley sunglasses outlet columbia outlet store ralph lauren uk prada sunglasses coach outlet store cheap jordan shoes jimmy choo shoes ed hardy shoes birkin handbags nike factory store coach outlet online revolution 3 coach outlet store valentino shoes adidas superstars tigers jerseys curry 5 shoes coach outlet canada coach outlet store ubiq shoes nike outlet online salomon shoes hydro flask lids coach outlet store online mbt shoes birkenstocks canada goose pandora charms converse shoes bathing ape asics outlet moncler pas cher louboutin shoes pandora jewelry outlet jaguars jersey true religion jeans chaussures christian louboutin adidas outlet online nba jerseys true religion jeans mcm handbags adidas yeezy boost 350 stone island sale air max 2017 ed hardy outlet discount oakley sunglasses canada goose coats adidas outlet michael kors outlet clearance supra shoes broncos jerseys adidas ultra boost michael kors outlet online air max 2017 true religion outlet giuseppe zanotti shoes adidas uk david yurman bracelet mont blanc pandora charms coach outlet clearance coach bags michael kors outlet online pittsburgh steelers jersey coach outlet online kate spade outlet jets jersey cincinnati bengals jersey lions jerseys birkenstock sandals jordans air jordan pandora outlet fred perry uggs cheap mbt shoes nike outlet store converse???? pandora jewelry canada canada goose outlet store pandora jewelry outlet maui jim moncler jacken toms superdry clothing hydro flask cup fitflops outlet north face jackets louis vuitton pandora sale clearance pandora rings coach outlet kate spade outlet store christian louboutin sale persol kate spade outlet online ugg outlet sale yankees jerseys louboutin outlet uggs outlet fitflops outlet raiders jerseys ugg boots clearance indianapolis colts jerseys adidas outlet online coach bags ray ban sunglasses moncler jacka ugg boots cheap jordans fitflops adidas outlet store jordan 12 oakley sunglasses outlet balenciaga shoes ralph lauren outlet online gymshark clothing converse???? coach factory outlet online nike sneakers christian louboutin christian louboutin shoes fitflops sale clearance prada outlet cheap handbags yeezy shoes nike hyperdunk moncler jackets oakley sunglasses wholesale coach purse fitflops curry 4 shoes true religion outlet store ralph lauren polo yeezy boost 350 cheap jordans adidas nmd r1 gucci outlet online uggs canada air jordan 4 pandora jewelry canada ugg italia tods outlet online coach outlet online puma shoes michael kors outlet clearance off-white clothing under armour shoes coach outlet online coach factory outlet adidas neo shoes dolce & gabbana Outlet coach factory outlet pandora jewelry outlet mulberry bags sale coach factory outlet online coach factory outlet fjallraven kanken eagles jersey nhl jerseys coach outlet online lebron 13 rolex replica watches timberland boots kate spade outlet true religion outlet store adidas shoes off white clothing canada goose outlet store soccer jerseys snapback hats miu miu shoes rolex watches cheap nike shoes coach outlet store online instyler ionic styler michael kors outlet clearance polo ralph lauren outlet online christian louboutin sale coach outlet store online hydro flask sale miu miu handbags clarks shoes coach outlet sale converse shoes coach outlet online ugg outlet nike kd 10 falcons jerseys true religion jeans sale christian louboutin outlet air max ralph lauren outlet coach factory outlet polo ralph lauren outlet online air max 2015 canada goose coats pandora jewelry outlet nike basketball shoes titans jersey coach factory outlet online louboutin outlet coach outlet online nba jerseys for sale michael kors outlet clearance air jordan retro moncler outlet online pandora jewelry outlet bape hoodie lacoste outlet ralph lauren outlet nike air force valentino outlet christian louboutin shoes moncler outlet online polo outlet store kate spade persol sunglasses sale coach factory outlet online philipp plein outlet canada goose coats christian louboutin shoes fitflop sale coach outlet online michael kors outlet coach factory outlet air max outlet coach outlet online adidas nmd runner mcm outlet michael kors outlet online nike outlet store online coach outlet online jordan shoes air jordan coach factory outlet online pandora charms cheap snapbacks david yurman ugg outlet clearance gucci bags coach factory outlet online kevin durant shoes fitflops ralph lauren pas cher ugg boots sale ralph lauren polo true religion outlet converse shoes michael kors factory outlet coach factory outlet birkenstocks nike roshe cazal outlet coach handbags clearance polo ralph lauren outlet online coach factory outlet online ugg yeezy shoes air jordan retro coach outlet store online clearance yeti tumbler canada goose jackets uggs outlet true religion outlet store camisetas futbol baratas adidas sneakers ugg boots canada goose sale off white clothing coach factory outlet online polo outlet pandora outlet michael kors outlet online coach outlet adidas slides malone souliers shoes pandora canada y3 shoes pandora charms outlet chrome hearts outlet store cheap mlb jerseys ferragamo outlet parajumpers jacket nike outlet store coach outlet clearance jordan pas cher ralph lauren polo coach factory outlet cheap jordan shoes cnaada goose outlet store nike sneakers coach factorty outlet online true religion moncler jackets toms coach outlet store pandora charms sale clearance michael kors outlet store coach outlet online celine outlet valentino coach factory outlet online ed hardy jimmy choo shoes ugg outlet uggs outlet online patriots jerseys mcm outlet store nike outlet store north face outlet adidas outlet online true religion jeans christian louboutin outlet coach outlet store mac cosmetics vans shoes kobe shoes kate spade outlet online swarovski jewelry outlet uggs outlet air jordan 14 coach outlet online nike outlet store jordan 31 cheap oakley sunglasses kate spade outlet coach outlet online cowboys jerseys coach outlet online fred perry michael kors outlet canada christian louboutin shoes adidas shoes polo outlet store coach outlet online birkenstock outlet store pandora charms coach outlet online louis vuitton outlet online ysl outlet uboutin shoes toms outlet online marc jacobs handbags louboutin outlet off white shirt louboutin outlet coach outlet online stone island sale hermes polo ralph lauren outlet cheap jordan shoes michael kors outlet online coach outlet online moncler sito ufficiale fitflops sale clearance christian louboutin outlet louboutin outlet cheap ugg boots ferragamo outlet louboutin outlet canada goose coach outlet store online coach outlet online fjallraven backpack coach factory outlet online air max ed hardy outlet seattle seahawks jersey ferragamo belts true religion outlet kate spade outlet uggs outlet cheap soccer jerseys asics running shoes cardinals jerseys fitflops david yurman coach outlet online mac makeup cubs jerseys durant shoes coach outlet online costa sunglasses sale fitflops outlet mlb jerseys coach outlet online adidas nmd christian louboutin outlet toms outlet store ugg pas cher jordan shoes manolo blahnik outlet pandora charms sale ralph lauren uk prada outlet online kendra scott valentino outlet store north face outlet store mbt shoes polo ralph lauren outlet prada outlet phillies jerseys michael kors bags gymshark sale ecco outlet asics shoes coach outlet online true religion jeans outlet fitflops sale clearance coach outlet pandora charms sale clearance pandora charms sale adidas yeezy shoes coach handbags outlet coach factory outlet online supreme clothing kate spade outlet sale air jordan shoes north face outlet cardinals jerseys nike outlet store nike shoes browns jerseys fivefingers shoes jordans air huarache gucci outlet online kobe 12 ugg outlet lacoste polo shirts coach outlet clearance air max nike outlet online coach factorty outlet online kate spade handbags pandora charms outlet pandora charms canada goose coats polo ralph lauren outlet christian louboutin outlet michael kors outlet clearance coach outlet store online david yurman jewelry pandora jewelry ugg boots pandora jewelry outlet gucci outlet online air max 2017 coach factory outlet yeti cheap jordans for sale le coq sportif cheap air jordans coach outlet online fitflops sale clearance louis vuitton outlet store kobe 11 uggs outlet suicoke sandals kate spade outlet online adidas yeezy boost coach factory outlet online louboutin outlet cheap maui jim sunglasses the north face jackets coach outlet store vans sneakers minnesota vikings jersey ralph lauren outlet online david yurman coach outlet store coach outlet canada chelsea jersey juicy couture valentino outlet nike max coach outlet online michael kors outlet canada adidas nmd ralph lauren outlet online canada goose true religion outlet store cincinnati reds jerseys michael kors outlet store pandora charms coach outlet store michael kors outlet clearance air jordan 3 ed hardy coach factory outlet store pandora charms outlet celine outlet cheap jordan shoes gymshark sale ed hardy canada goose outlet north face outlet store coach factory outlet coach factory outlet true religion jeans giuseppe zanotti outlet store san diego chargers jerseys mbt shoes outlet ugg outlet store coach outlet clearance nike flip-flops nike shoes ralph lauren polo prada outlet adidas store coach handbags coach factory outlet online michael kors outlet online nike free flyknit gucci outlet store ray ban sunglasses outlet pandora charms sale clearance miu miu shoes coach outlet online coach outlet online coach outlet store kate spade outlet online dolce & gabbana sunglasses kate spade outlet online coach factorty outlet store nike air max 2018 louboutin outlet air max pas cher christian louboutin outlet nike shoes cheap ray ban sunglasses converse shoes canada goose outlet kappa clothing red sox jerseys soccer shoes cheap air jordans hermes bags pandora jewelry store nike blazer coach outlet online nike free run fila shoes cheap snapbacks yeezy shoes nike outlet ralph lauren uk coach outlet online nike air force 1 ronaldo jerseys fjallraven kanken ugg boots outlet air max 270 ugg boots coach factory outlet online oakley sunglasses outlet cheap jordan shoes converse shoes chi flat iron coach outlet online asics shoes nike outlet store north face outlet online polo ralph lauren outlet canada goose coats air max shoes michael kors handbags outlet coach factory outlet coach outlet jordan shoes for sale nike tn michael kors outlet online nike outlet kate spade outlet online suicoke sandals hermes belt nike outlet twins jerseys canada goose coats nike outlet online nike roshe run kate spade outlet online fitflops yeti cup true religion outlet nike air max schweiz coach outlet sale cheap air max christian louboutin outlet pandora jewelry juicy couture coach handbags adidas yeezy boost coach outlet canada ralph lauren outlet coach outlet online versace clothing pandora charms pandora charms coach canada outlet polo ralph lauren outlet online kendra scott coach factory outlet online mulberry handbags ralph lauren outlet coach outlet online converse???? jordan retro 11 pandora charms sale clearance coach handbags coach outlet store coach outlet online nike tn ugg italia mont blanc pen air jordan shoes true religion outlet mbt shoes toms shoes coach outlet online air jordan shoes cheap mlb jerseys denver broncos jersey costa sunglasses sale prada outlet kate spade handbags kate spade handbags hydro flask sale kyrie 2 cheap jordans free shipping coach outlet store true religion coach outlet store louis vuitton outlet online true religion outlet store coach outlet canada goose outlet store gucci outlet online moncler coach outlet online retro 11 givenchy handbags nike blazer nike factory store cheap snapbacks foamposite shoe coach outlet store online kendra scott houston texans jerseys fitflops sale clearance air max adidas ultra boost vibram fivefingers outlet adidas wings shoes packers jersey birkenstock shoes louboutin pas cher canada goose outlet persol sunglasses coach outlet online moncler outlet online coach outlet store online toms shoes stone island hoodie pandora outlet hermes outlet store coach outlet online ralph lauren sale clearance uk christian louboutin shoes buffalo bills jersey ravens jerseys yolo