INDEXES USING SCRIPT IN SQL SERVER 2005/2008 R2
HOW TO DEFRAGMENT INDEXES USING SCRIPT IN SQL SERVER 2005/2008
Background:
When a query is executed on a database, indexes are used to physically locate the data on disk. Due to frequent insert, update and delete statements this data can become fragmented. This is a serious performance issue. To mitigate this schedeule this script in a SQL Agent Job to frequently defragment you indexes.
Steps:
1. Create stored procedure Defragment Indexes in master database. This stored procedure defragments the indexes in your database:
USE masterGOIf (Object_ID('dbo.DefragmentIndexes')>0 )Begin Drop Procedure dbo.DefragmentIndexesEnd;GoCreate Procedure dbo.DefragmentIndexes @minSize float = 50.0 -- MB , @minFragmentation float = 5.0 -- % , @rebuildThreshold float = 30.0 -- % , @database varchar(128) = Null , @onlineRebuild bit = 1 , @maxDopRestriction tinyint = NullAs/********************************************************************************* Author: Arthur Graus, http://www.sqlserveroptimizer.com Michelle Ufford, http://sqlfool.com/ Purpose: Defrags all indexes for one or more databases Notes: CAUTION: TRANSACTION LOG SIZE MUST BE MONITORED CLOSELY WHEN DEFRAGMENTING. Parameters: @minSize defaulted to 50 MB, will not defrag if size of index is less than that @minFragmentation defaulted to 10%, will not defrag if fragmentation is less than that @rebuildThreshold defaulted to 30% as recommended by Microsoft in BOL; greater than 30% will result in rebuild instead @database Optional, specify specific database name to defrag; If not specified, all non-system databases will be defragged. @onlineRebuild 1 = online rebuild; 0 = offline rebuild @maxDopRestriction Option to specify a processor limit for index rebuilds @defragDelay time to wait between defrag commands; gives the server a little time to catch up Sample: Exec dbo.DefragmentIndexes @minSize = 1 , @minFragmentation = 10 , @database = 'AdventureWorks'*********************************************************************************/ Set NoCount On;Set XACT_Abort On;Set Quoted_Identifier On;Begin /* Declare our variables */ Declare @objectID int , @databaseID int , @databaseName nvarchar(128) , @indexID int , @partitionCount bigint , @schemaName nvarchar(128) , @objectName nvarchar(128) , @indexName nvarchar(128) , @partitionNumber smallint , @partitions smallint , @fragmentation float , @pageCount int , @sqlCommand nvarchar(4000) , @rebuildCommand nvarchar(200) , @dateTimeStart datetime , @dateTimeEnd datetime , @containsLOB bit , @editionCheck bit , @debugMessage varchar(128) , @updateSQL nvarchar(4000) , @partitionSQL nvarchar(4000) , @partitionSQL_Param nvarchar(1000) , @LOB_SQL nvarchar(4000) , @LOB_SQL_Param nvarchar(1000); /* Create our temporary tables */ Create Table #indexes ( databaseID int , databaseName nvarchar(128) , objectID int , indexID int , partitionNumber smallint , fragmentation float , page_count int , defragStatus bit , schemaName nvarchar(128) Null , objectName nvarchar(128) Null , indexName nvarchar(128) Null ); Create Table #databases ( databaseID int , databaseName varchar(128) ); Create Table #processor ( [index] int , Name varchar(128) , Internal_Value int , Character_Value int ); Insert Into #processor Execute xp_msver 'ProcessorCount'; If @maxDopRestriction Is Not Null And @maxDopRestriction > (Select Internal_Value From #processor) Select @maxDopRestriction = Internal_Value From #processor; /* Check our server version; 3 = Enterprise, Enterprise Evaluation or Developer */ If ((Select ServerProperty('EngineEdition')) = 3 ) Set @editionCheck = 1 -- supports online rebuilds Else Set @editionCheck = 0; -- does not support online rebuilds Insert Into #databases Select database_id , name From sys.databases Where name = IsNull(@database, name) And database_id > 4 -- exclude system databases And [state] = 0; -- state must be ONLINE /* Loop through our list of databases */ While (Select Count(*) From #databases) > 0 Begin Select Top 1 @databaseID = databaseID From #databases; /* Determine which indexes to defrag*/ Insert Into #indexes Select database_id As databaseID , QuoteName(DB_Name(database_id)) As 'databaseName' , [object_id] As objectID , index_id As indexID , partition_number As partitionNumber , avg_fragmentation_in_percent As fragmentation , page_count , 0 As 'defragStatus' /* 0 = unprocessed, 1 = processed */ , Null As 'schemaName' , Null As 'objectName' , Null As 'indexName' From sys.dm_db_index_physical_stats (@databaseID, Null, Null , Null, N'Limited') Where avg_fragmentation_in_percent >= @minFragmentation And index_id > 0 -- ignore heaps And page_count > @minSize * 1024 / 8 -- ignore small objects Option (MaxDop 1); Delete From #databases Where databaseID = @databaseID; End Create Clustered Index CIX_temp_indexDefragList On #indexes(databaseID, objectID, indexID, partitionNumber); /* Begin our loop for defragging */ While (Select Count(*) From #indexes Where defragStatus = 0) > 0 Begin /* Start with the most fragmented index first to defrag */ Select Top 1 @objectID = objectID , @indexID = indexID , @databaseID = databaseID , @databaseName = databaseName , @fragmentation = fragmentation , @partitionNumber = partitionNumber , @pageCount = page_count From #indexes Where defragStatus = 0 Order By fragmentation Desc; /* Look up index information */ Select @updateSQL = N'Update idl Set schemaName = QuoteName(s.name) , objectName = QuoteName(o.name) , indexName = QuoteName(i.name) From #indexes As idl Inner Join ' + @databaseName + '.sys.objects As o On idl.objectID = o.object_id Inner Join ' + @databaseName + '.sys.indexes As i On o.object_id = i.object_id Inner Join ' + @databaseName + '.sys.schemas As s On o.schema_id = s.schema_id Where o.object_id = ' + Cast(@objectID As varchar(10)) + ' And i.index_id = ' + Cast(@indexID As varchar(10)) + ' And i.type > 0 And idl.databaseID = ' + Cast(@databaseID As varchar(10)); Execute sp_executeSQL @updateSQL; Select @objectName = objectName , @schemaName = schemaName , @indexName = indexName From #indexes Where objectID = @objectID And indexID = @indexID And databaseID = @databaseID; /* Determine if the index is partitioned */ Select @partitionSQL = 'Select @partitionCount_OUT = Count(*) From ' + @databaseName + '.sys.partitions Where object_id = ' + Cast(@objectID As varchar(10)) + ' And index_id = ' + Cast(@indexID As varchar(10)) + ';' , @partitionSQL_Param = '@partitionCount_OUT int OutPut'; Execute sp_executeSQL @partitionSQL, @partitionSQL_Param, @partitionCount_OUT = @partitionCount OutPut; /* Determine if the table contains LOBs */ Select @LOB_SQL = ' Select Top 1 @containsLOB_OUT = column_id From ' + @databaseName + '.sys.columns With (NoLock) Where [object_id] = ' + Cast(@objectID As varchar(10)) + ' And (system_type_id In (34, 35, 99) Or max_length = -1);' /* system_type_id --> 34 = image, 35 = text, 99 = ntext max_length = -1 --> varbinary(max), varchar(max), nvarchar(max), xml */ , @LOB_SQL_Param = '@containsLOB_OUT int OutPut'; Execute sp_executeSQL @LOB_SQL, @LOB_SQL_Param, @containsLOB_OUT = @containsLOB OutPut; If @fragmentation < @rebuildThreshold Or @containsLOB = 1 Or @partitionCount > 1 Begin Set @sqlCommand = N'Alter Index ' + @indexName + N' On ' + @databaseName + N'.' + @schemaName + N'.' + @objectName + N' ReOrganize'; /* If our index is partitioned, we should always reorganize */ If @partitionCount > 1 Set @sqlCommand = @sqlCommand + N' Partition = ' + Cast(@partitionNumber As nvarchar(10)); End; /* If the index is heavily fragmented and doesn't contain any partitions or LOB's, rebuild it */ If @fragmentation >= @rebuildThreshold And IsNull(@containsLOB, 0) != 1 And @partitionCount <= 1 Begin /* Set online rebuild options; requires Enterprise Edition */ If @onlineRebuild = 1 And @editionCheck = 1 Set @rebuildCommand = N' Rebuild With (Online = On'; Else Set @rebuildCommand = N' Rebuild With (Online = Off'; /* Set processor restriction options; requires Enterprise Edition */ If @maxDopRestriction Is Not Null And @editionCheck = 1 Set @rebuildCommand = @rebuildCommand + N', MaxDop = ' + Cast(@maxDopRestriction As varchar(2)) + N')'; Else Set @rebuildCommand = @rebuildCommand + N')'; Set @sqlCommand = N'Alter Index ' + @indexName + N' On ' + @databaseName + N'.' + @schemaName + N'.' + @objectName + @rebuildCommand; End; /* Executing the SQL? */ Execute sp_executeSQL @sqlCommand; WaitFor Delay '00:00:05'; Update #indexes Set defragStatus = 1 Where databaseID = @databaseID And objectID = @objectID And indexID = @indexID And partitionNumber = @partitionNumber; End Drop Table #indexes; Drop Table #databases; Drop Table #processor; Set NoCount Off; Return 0EndGo Exec dbo.DefragmentIndexes @minSize = 0.01 , @minFragmentation = 5 , @database = 'AdventureWorks2008' |