Flexible Index reorganize and rebuild

,

This procedure will find most fragmented and most accessed indexes server wide. and then rebuild or reorganize depending on your parameters.

So it will alter/list only the indexes which you will get the most benefit of fixing defragmentation.

Important Note : This procedure can create extensive IO operations. Please check first in DEV environment and beware that altering indexes can also create locks. That's why please use it with caution in PRD environments. Please try to exclude every unnecessary database name in WHERE clause and try to reduce the load.

  1. Please create the stored procedure
  2. Execute the procedure in debug mode first: EXEC sp_FlexibleReindex @Debug=1  this will only list the indexes sorted by most benefit
  3. Please execute the procedure with the parameters that satisfy your needs
CREATE PROCEDURE sp_FlexibleReindex 
	@MaxExecutionTime int = 10, -- Script will stop if execution time exceeds this parameter (in minutes)
	@Debug tinyint = 1, -- Debug mode = 1, execution mode = 0... It prints only the statements in debug mode
	@ConsoleMode tinyint = 1, -- If 1, It will also print additional messages at execution mode. It should be better to set as 0 when running with SQL Agent Job.
	@MinFragmentationPercent tinyint = 8, -- Script will alter only the indexes if @FragmentationPercent >= @MinFragmentationPercent
	@RebuildPercentage tinyint = 60, -- Script will rebuild the indexes if @FragmentationPercent >=  @RebuildPercentage else reorganize the indexes 
	@AlterClusteredIndexes tinyint = 0, -- If 1 script will rebuild/reorganize also clustered indexes. if 0, it will skip clustered indexes.
	@MinPageCount smallint = 200, -- Script will ignore indexes which hass less page numbers than @MinPageCount. If @@MinPageCount is NULL scprit will ignore this filter...
	@LastAccessed smallint = 7 -- In days... Script will consider the only indexes which are accessed in last @LastAccessed days. If @LastAccessed is NULL scprit will ignore this filter...
AS

BEGIN


	-- Internal variables
	DECLARE @Counter int = 0
	DECLARE @FragmentationPercent float
	DECLARE @DatabaseId int 
	DECLARE @DatabaseName sysname 
	DECLARE @AllStartTime datetime = GETDATE()
	DECLARE @AlterIndexStartTime datetime 
	DECLARE @FullTableName	NVARCHAR(500)
	DECLARE @SQL NVARCHAR(MAX)
	DECLARE @IndexSQL NVARCHAR(MAX)
	DECLARE @LastAccessedDate VARCHAR(100) = CONVERT(VARCHAR(100), DATEADD(DAY, -@LastAccessed, GETDATE()), 120);

	SET NOCOUNT ON;
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

	CREATE TABLE #IndexStats(
		[DatabaseId] [int] NOT NULL,
		[DatabaseName] [sysname] NOT NULL,
		[SchemaName] [sysname] NOT NULL,
		[TableName] [sysname] NOT NULL,
		[IndexName] [sysname] NULL,
		[AvgFragmentationPercent] [float] NULL,
		[PageCounts] [bigint] NULL,
		[IndexType] [nvarchar](60) NULL,
		[ObjectId] [int] NOT NULL,
		[UserSeeks] [bigint] NULL,
		[UserScans] [bigint] NULL,
		[UserLookups] [bigint] NULL,
		[LastUserSeek] [datetime] NULL,
		[LastUserScan] [datetime] NULL,
		[LastUserLookup] [datetime] NULL,
		[LastUserUpdate] [datetime] NULL,
		[FullTableName] AS ('[' + DatabaseName + '].[' + SchemaName  +'].[' + TableName + ']')
	)

	-- Here you can change the query to fetch the only databases which we want to alter their indexes
	DECLARE Db_Cursor CURSOR FOR  
	SELECT database_id, name FROM sys.databases
	WHERE name NOT IN ('master','model','msdb','tempdb', 'distribution')   AND is_read_only <> 1

	OPEN Db_Cursor  
	FETCH NEXT FROM Db_Cursor INTO @DatabaseId, @DatabaseName  

	WHILE @@FETCH_STATUS = 0  
	BEGIN  

		SET @SQL = '
		INSERT #IndexStats
		SELECT 
			DatabaseId = ' + CONVERT (VARCHAR(10), @DatabaseId) + ',
			DatabaseName = ''' + @DatabaseName +''',
			SchemaName = dbschemas.[name], 
			TableName = dbtables.[name], 
			IndexName = dbindexes.[name],
			AvgFragmentationPercent = indexstats.avg_fragmentation_in_percent,
			PageCounts = indexstats.page_count,
			IndexType = dbindexes.type_desc,
			ObjectId = dbindexes.object_id,
			UserSeeks = IndexUsage.user_seeks,
			UserScans = IndexUsage.user_scans,
			UserLookups = IndexUsage.user_lookups,
			LastUserSeek = IndexUsage.last_user_seek,
			LastUserScan = IndexUsage.last_user_scan,
			LastUserLookup = IndexUsage.last_user_lookup,
			LastUserUpdate = IndexUsage.last_user_update
		FROM sys.dm_db_index_physical_stats (' + CONVERT(VARCHAR(10), @DatabaseId)  +', NULL, NULL, NULL, NULL) AS indexstats 
		INNER JOIN ' + @DatabaseName + '.sys.tables dbtables WITH (NOLOCK) on dbtables.[object_id] = indexstats.[object_id]
		INNER JOIN ' + @DatabaseName + '.sys.schemas dbschemas WITH (NOLOCK) on dbtables.[schema_id] = dbschemas.[schema_id]
		INNER JOIN ' + @DatabaseName + '.sys.indexes AS dbindexes WITH (NOLOCK) ON dbindexes.[object_id] = indexstats.[object_id] AND indexstats.index_id = dbindexes.index_id
		LEFT JOIN sys.dm_db_index_usage_stats IndexUsage WITH (NOLOCK) ON IndexUsage.database_id = ' + CONVERT(VARCHAR(10), @DatabaseId) + ' AND IndexUsage.object_id = dbtables.object_id AND IndexUsage.index_id = dbindexes.index_id
		'	
		IF @AlterClusteredIndexes = 1
			BEGIN
				SET @SQL += 'WHERE dbindexes.type_desc IN (''CLUSTERED'', ''NONCLUSTERED'')'
			END
		ELSE
			BEGIN
				SET @SQL += 'WHERE dbindexes.type_desc = ''NONCLUSTERED'''
			END
		--PRINT @SQL
		SET @SQL += ' AND indexstats.avg_fragmentation_in_percent >= ' + CONVERT(VARCHAR(10), @MinFragmentationPercent)
		IF @MinPageCount is not NULL 
			BEGIN
				SET @SQL += ' AND indexstats.page_count >= ' + CONVERT(VARCHAR(10), @MinPageCount)
			END
		IF @LastAccessed is not NULL 
			BEGIN
				SET @SQL += ' AND (IndexUsage.last_user_seek >= ''' +  @LastAccessedDate + ''' '
				SET @SQL += ' OR IndexUsage.last_user_scan >= ''' +  @LastAccessedDate + ''' '
				SET @SQL += ' OR IndexUsage.last_user_lookup >= ''' +  @LastAccessedDate + ''') '
			END

		EXECUTE sp_executesql  @SQL
		FETCH NEXT FROM Db_Cursor INTO @DatabaseId, @DatabaseName  
	END  

	CLOSE Db_Cursor  
	DEALLOCATE Db_Cursor 

	IF @Debug = 1
		BEGIN
			SELECT *, IndexAction = CASE WHEN AvgFragmentationPercent >= @MinFragmentationPercent THEN CASE WHEN AvgFragmentationPercent >= @RebuildPercentage THEN 'REBUILD' ELSE 'REORGANIZE' END ELSE NULL END FROM #IndexStats S
			ORDER BY (AvgFragmentationPercent * PageCounts * (UserSeeks + UserScans + UserLookups) ) / 100 DESC
		END

	-- Reorganize the indexes by the most fragmented and most accessed
	DECLARE Index_Cursor CURSOR FOR  
	SELECT 
		FullTableName,
		AvgFragmentationPercent, 
		AlterIndexStatement = 'ALTER INDEX [' + IndexName + + '] ON ' + S.FullTableName + CASE WHEN AvgFragmentationPercent >= @RebuildPercentage THEN ' REBUILD' ELSE ' REORGANIZE' END
	FROM #IndexStats S
	ORDER BY (AvgFragmentationPercent * PageCounts * (UserSeeks + UserScans + UserLookups) ) / 100 DESC
	OPEN Index_Cursor
	FETCH NEXT FROM Index_Cursor INTO @FullTableName, @FragmentationPercent, @IndexSQL


	WHILE @@FETCH_STATUS = 0  
	BEGIN  

		IF DATEDIFF(second, @AllStartTime, GETDATE()) >= (@MaxExecutionTime * 60)
			BEGIN
				BREAK;
			END

		SET @Counter += 1;

		IF @Debug = 1
			BEGIN
				PRINT @IndexSQL
				PRINT '---------------------------------'
				PRINT ''
			END
		ELSE
			BEGIN
				SET @AlterIndexStartTime = GETDATE()
				IF @ConsoleMode = 1
					BEGIN
						PRINT CONVERT(VARCHAR(23), @AlterIndexStartTime, 120) + ' altering index ' + @FullTableName
						PRINT 'Index statement: ' + @IndexSQL
					END

				EXECUTE sp_executesql  @IndexSQL;


				IF @ConsoleMode = 1
					BEGIN
						PRINT CONVERT(VARCHAR(23), GETDATE(), 120) + ' index altered in ' + CONVERT(VARCHAR(10), DATEDIFF(second, @AlterIndexStartTime, GETDATE())) + ' seconds'
						PRINT '---------------------------------------------------------------------------------------'
						PRINT ''
					END

			END

		FETCH NEXT FROM Index_Cursor INTO  @FullTableName, @FragmentationPercent, @IndexSQL

	END  
	CLOSE Index_Cursor
	DEALLOCATE Index_Cursor


	DROP TABLE #IndexStats;

END

Rate

3 (6)

Share

Share

Rate

3 (6)