USE master
GO
If (Object_ID('dbo.DefragmentIndexes')>0 )
Begin
Drop Procedure dbo.DefragmentIndexes
End;
Go
Create Procedure dbo.DefragmentIndexes
@minSize float = 50.0
, @minFragmentation float = 5.0
, @rebuildThreshold float = 30.0
, @database varchar(128) = Null
, @onlineRebuild bit = 1
, @maxDopRestriction tinyint = Null
As
/*********************************************************************************
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
Else
Set @editionCheck = 0;
Insert Into #databases
Select database_id
, name
From sys.databases
Where name = IsNull(@database, name)
And database_id > 4
And [state] = 0;
/* 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
And page_count > @minSize * 1024 / 8
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
max_length = -1
, @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 0
End
Go
Exec dbo.DefragmentIndexes
@minSize = 0.01
, @minFragmentation = 5
, @database = 'AdventureWorks2008'