Voici un script TRANSACT / SQL fort intéressant et surtout bien pratique.
Dans un premier temps "déclarons" la procédure stockée <dbo.dba_indexDefrag_sp>.
Nous verrons ensuite comment l'utiliser.
Quelques recommandations : soyez attentifs à la taille des TRANSACTION LOG durant le traitement et bien sûr évitez d'exécuter ce traitement durant les heures de travail (consommateur en ressources et en temps CPU).
Script
USE [ma_base]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[dba_indexDefrag_sp]
/* Déclarer les paramètres */
@minFragmentation float = 10.0
/* en pourcentage, si la valeur est inférieure à 10.0 -> aucune défragmentation */
, @rebuildThreshold float = 30.0
/* en pourcentage, une valeur plus importante va résulter plus d'un REBUILD que d'une réorganisation */
, @executeSQL bit = 1
/* 1 = exécuter traitement ; 0 = afficher commandes seulement */
, @defragOrderColumn nvarchar(20) = 'range_scan_count'
/* options valides : range_scan_count, fragmentation, page_count
Permet de définir les priorités dans l'ordre des defrags. Utilisé uniquement si @executeSQL = 1.
range_scan_count = en général, c'est le plus profitable pour la défragmentation
fragmentation = valeur de la fragmentation dans l'index;
plus la valeur est haute, plus la fragmentation est importante
page_count = nombre de pages dans l'index ; effet sur la durée du defrag de l'index
*/
, @defragSortOrder nvarchar(4) = 'DESC'
/* options valides : ASC, DESC */
, @timeLimit int = 720
/* limitation de temps optionnelle : exprimée en minutes */
, @database varchar(128) = N'ma_base'
/* option pour spécifier un nom de base ; null correspond à toutes les bases */
, @tableName varchar(4000) = Null
/* option pour spécifier une table ; null correspond à toutes les tables */
, @forceRescan bit = 0
/* Pour forcer ou pas un rescan des index ; 1 = force, 0 = utilise scan existant si disponible */
, @scanMode varchar(10) = N'LIMITED'
/* Options sont LIMITED (le plus rapide, seul le plus haut niveau <parent> est scanné), SAMPLED (un % des pages DATA), et DETAILED (toutes les pages DATA) */
, @minPageCount int = 8
/* Minimum de pages pour déclencher le traitement - Microsoft recommende > 1 extent (8 pages) */
, @maxPageCount int = Null
/* Maximum de pages pour déclencher le traitement - NULL = pas de limite */
, @excludeMaxPartition bit = 0
/* 1 = exclure partition la plus "populée" ; 0 = ne pas exclure */
, @onlineRebuild bit = 1
/* 1 = rebuild online ; 0 = rebuild offline seulement pour l'édition Entreprise */
, @sortInTempDB bit = 1
/* 1 = exécute opération de tri dans TempDB ; 0 = exécute opération de tri dans la base */
, @maxDopRestriction tinyint = Null
/* option pour réduite le nombre de processeurs pour l'opération seulement dans l'édition Entreprise */
, @printCommands bit = 0
/* 1 = afficher commandes à l'écran ; 0 = ne pas afficher commandes */
, @printFragmentation bit = 0
/* 1 = afficher fragmentation à l'écran;
0 = ne pas imprimer */
, @defragDelay char(8) = '00:00:05'
/* durée d'attente entre 2 commandes defrag */
, @debugMode bit = 0
/* 1 : afficher commentaires debug ; 0 : ne pas afficher commentaires debug */
As
Set NoCount On;
Set XACT_Abort On;
Set Quoted_Identifier On;
Begin
Begin Try
If @minFragmentation Is Null
Or @minFragmentation Not Between 0.00 And 100.0
Set @minFragmentation = 10.0;
If @rebuildThreshold Is Null
Or @rebuildThreshold Not Between 0.00 And 100.0
Set @rebuildThreshold = 30.0;
If @defragDelay Not Like '00:[0-5][0-9]:[0-5][0-9]'
Set @defragDelay = '00:00:05';
If @defragOrderColumn Is Null
Or @defragOrderColumn Not In ('range_scan_count', 'fragmentation', 'page_count')
Set @defragOrderColumn = 'range_scan_count';
If @defragSortOrder Is Null
Or @defragSortOrder Not In ('ASC', 'DESC')
Set @defragSortOrder = 'DESC';
If @scanMode Not In ('LIMITED', 'SAMPLED', 'DETAILED')
Set @scanMode = 'LIMITED';
If @debugMode Is Null
Set @debugMode = 0;
If @forceRescan Is Null
Set @forceRescan = 0;
If @sortInTempDB Is Null
Set @sortInTempDB = 1;
If @debugMode = 1 RaisError('Initialisation et démarrage traitement...', 0, 42) With NoWait;
Declare @objectID int
, @databaseID int
, @databaseName nvarchar(128)
, @indexID int
, @partitionCount bigint
, @schemaName nvarchar(128)
, @objectName nvarchar(128)
, @indexName nvarchar(128)
, @partitionNumber smallint
, @fragmentation float
, @pageCount int
, @sqlCommand nvarchar(4000)
, @rebuildCommand nvarchar(200)
, @dateTimeStart datetime
, @dateTimeEnd datetime
, @containsLOB bit
, @editionCheck bit
, @debugMessage nvarchar(4000)
, @updateSQL nvarchar(4000)
, @partitionSQL nvarchar(4000)
, @partitionSQL_Param nvarchar(1000)
, @LOB_SQL nvarchar(4000)
, @LOB_SQL_Param nvarchar(1000)
, @indexDefrag_id int
, @startDateTime datetime
, @endDateTime datetime
, @getIndexSQL nvarchar(4000)
, @getIndexSQL_Param nvarchar(4000)
, @allowPageLockSQL nvarchar(4000)
, @allowPageLockSQL_Param nvarchar(4000)
, @allowPageLocks int
, @excludeMaxPartitionSQL nvarchar(4000);
Select @startDateTime = GetDate()
, @endDateTime = DateAdd(minute, @timeLimit, GetDate());
Create Table #databaseList
(
databaseID int
, databaseName varchar(128)
, scanStatus bit
);
Create Table #processor
(
[index] int
, Name varchar(128)
, Internal_Value int
, Character_Value int
);
Create Table #maxPartitionList
(
databaseID int
, objectID int
, indexID int
, maxPartition int
);
If @debugMode = 1 RaisError('Début validation...', 0, 42) With NoWait;
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;
/* Vérifions version server ; 1804890536 = Entreprise, 610778273 = Entreprise Evaluation, -2117995310 = Développeur */
If (Select ServerProperty('EditionID')) In (1804890536, 610778273, -2117995310)
Set @editionCheck = 1
Else
Set @editionCheck = 0;
/* liste des tables à traiter */
Insert Into #databaseList
Select database_id
, name
, 0
From sys.databases
Where name = IsNull(@database, name)
And [name] Not In ('master', 'tempdb')
And [state] = 0
And is_read_only = 0;
If Not Exists(Select Top 1 * From dbo.dba_indexDefragStatus Where defragDate Is Null)
Or @forceRescan = 1
Begin
Truncate Table dbo.dba_indexDefragStatus;
// traiter les tables
While (Select Count(*) From #databaseList Where scanStatus = 0) > 0
Begin
Select Top 1 @databaseID = databaseID
From #databaseList
Where scanStatus = 0;
Select @debugMessage = ' traitement table ' + DB_Name(@databaseID) + '...';
If @debugMode = 1
RaisError(@debugMessage, 0, 42) With NoWait;
Insert Into dbo.dba_indexDefragStatus
(
databaseID
, databaseName
, objectID
, indexID
, partitionNumber
, fragmentation
, page_count
, range_scan_count
, scanDate
)
Select
ps.database_id As 'databaseID'
, QuoteName(DB_Name(ps.database_id)) As 'databaseName'
, ps.object_id As 'objectID'
, ps.index_id As 'indexID'
, ps.partition_number As 'partitionNumber'
, Sum(ps.avg_fragmentation_in_percent) As 'fragmentation'
, Sum(ps.page_count) As 'page_count'
, os.range_scan_count
, GetDate() As 'scanDate'
From sys.dm_db_index_physical_stats(@databaseID, Object_Id(@tableName), Null , Null, @scanMode) As ps
Join sys.dm_db_index_operational_stats(@databaseID, Object_Id(@tableName), Null , Null) as os
On ps.database_id = os.database_id
And ps.object_id = os.object_id
and ps.index_id = os.index_id
And ps.partition_number = os.partition_number
Where avg_fragmentation_in_percent >= @minFragmentation
And ps.index_id > 0
And ps.page_count > @minPageCount
And ps.index_level = 0
Group By ps.database_id
, QuoteName(DB_Name(ps.database_id))
, ps.object_id
, ps.index_id
, ps.partition_number
, os.range_scan_count
Option (MaxDop 2);
If @excludeMaxPartition = 1
Begin
Set @excludeMaxPartitionSQL = '
Select ' + Cast(@databaseID As varchar(10)) + ' As [databaseID]
, [object_id]
, index_id
, Max(partition_number) As [maxPartition]
From ' + DB_Name(@databaseID) + '.sys.partitions
Where partition_number > 1
And [rows] > 0
Group By object_id
, index_id;';
Insert Into #maxPartitionList
Execute sp_executesql @excludeMaxPartitionSQL;
End;
Update #databaseList
Set scanStatus = 1
Where databaseID = @databaseID;
End
If @excludeMaxPartition = 1
Begin
Delete ids
From dbo.dba_indexDefragStatus As ids
Join #maxPartitionList As mpl
On ids.databaseID = mpl.databaseID
And ids.objectID = mpl.objectID
And ids.indexID = mpl.indexID
And ids.partitionNumber = mpl.maxPartition;
End;
Update ids
Set ids.exclusionMask = ide.exclusionMask
From dbo.dba_indexDefragStatus As ids
Join dbo.dba_indexDefragExclusion As ide
On ids.databaseID = ide.databaseID
And ids.objectID = ide.objectID
And ids.indexID = ide.indexID;
End
// les index à défragmenter
Select @debugMessage = 'Nous avons ' + Cast(Count(*) As varchar(10)) + ' index à défragmenter !'
From dbo.dba_indexDefragStatus
Where defragDate Is Null
And page_count Between @minPageCount And IsNull(@maxPageCount, page_count);
If @debugMode = 1 RaisError(@debugMessage, 0, 42) With NoWait;
While (Select Count(*)
From dbo.dba_indexDefragStatus
Where (
(@executeSQL = 1 And defragDate Is Null)
Or (@executeSQL = 0 And defragDate Is Null And printStatus = 0)
)
And exclusionMask & Power(2, DatePart(weekday, GetDate())-1) = 0
And page_count Between @minPageCount And IsNull(@maxPageCount, page_count)) > 0
Begin
If IsNull(@endDateTime, GetDate()) < GetDate()
Begin
RaisError('Limite temps dépassé !', 11, 42) With NoWait;
End;
Set @getIndexSQL = N'
Select Top 1
@objectID_Out = objectID
, @indexID_Out = indexID
, @databaseID_Out = databaseID
, @databaseName_Out = databaseName
, @fragmentation_Out = fragmentation
, @partitionNumber_Out = partitionNumber
, @pageCount_Out = page_count
From dbo.dba_indexDefragStatus
Where defragDate Is Null '
+ Case When @executeSQL = 0 Then 'And printStatus = 0' Else '' End + '
And exclusionMask & Power(2, DatePart(weekday, GetDate())-1) = 0
And page_count Between @p_minPageCount and IsNull(@p_maxPageCount, page_count)
Order By + ' + @defragOrderColumn + ' ' + @defragSortOrder;
Set @getIndexSQL_Param = N'@objectID_Out int OutPut
, @indexID_Out int OutPut
, @databaseID_Out int OutPut
, @databaseName_Out nvarchar(128) OutPut
, @fragmentation_Out int OutPut
, @partitionNumber_Out int OutPut
, @pageCount_Out int OutPut
, @p_minPageCount int
, @p_maxPageCount int';
Execute sp_executesql @getIndexSQL
, @getIndexSQL_Param
, @p_minPageCount = @minPageCount
, @p_maxPageCount = @maxPageCount
, @objectID_Out = @objectID OutPut
, @indexID_Out = @indexID OutPut
, @databaseID_Out = @databaseID OutPut
, @databaseName_Out = @databaseName OutPut
, @fragmentation_Out = @fragmentation OutPut
, @partitionNumber_Out = @partitionNumber OutPut
, @pageCount_Out = @pageCount OutPut;
Select @updateSQL = N'Update ids
Set schemaName = QuoteName(s.name)
, objectName = QuoteName(o.name)
, indexName = QuoteName(i.name)
From dbo.dba_indexDefragStatus As ids
Inner Join ' + @databaseName + '.sys.objects As o
On ids.objectID = o.object_id
Inner Join ' + @databaseName + '.sys.indexes As i
On o.object_id = i.object_id
And ids.indexID = i.index_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 ids.databaseID = ' + Cast(@databaseID As varchar(10));
Execute sp_executesql @updateSQL;
Select @objectName = objectName
, @schemaName = schemaName
, @indexName = indexName
From dbo.dba_indexDefragStatus
Where objectID = @objectID
And indexID = @indexID
And databaseID = @databaseID;
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;
/* Déterminer si la table contient des LOB (Large Objects)
Select @LOB_SQL = ' Select @containsLOB_OUT = Count(*)
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;
/* Déterminer si verrous sur page sont autorisés ; pour de tels index nou avons toujours besoin d'effectuer un REBUILD */
Select @allowPageLockSQL = 'Select @allowPageLocks_OUT = Count(*)
From ' + @databaseName + '.sys.indexes
Where object_id = ' + Cast(@objectID As varchar(10)) + '
And index_id = ' + Cast(@indexID As varchar(10)) + '
And Allow_Page_Locks = 0;'
, @allowPageLockSQL_Param = '@allowPageLocks_OUT int OutPut';
Execute sp_executesql @allowPageLockSQL, @allowPageLockSQL_Param, @allowPageLocks_OUT = @allowPageLocks OutPut;
/* S'il y a un taux élevé de fragmentation ou si nous avons un LOB, nous devons réorganiser */
If (@fragmentation < @rebuildThreshold Or @containsLOB >= 1 Or @partitionCount > 1)
And @allowPageLocks = 0
Begin
Set @sqlCommand = N'Alter Index ' + @indexName + N' On ' + @databaseName + N'.'
+ @schemaName + N'.' + @objectName + N' ReOrganize';
/* si notre index est partitionné, nous devons toujours réorganiser */
If @partitionCount > 1
Set @sqlCommand = @sqlCommand + N' Partition = '
+ Cast(@partitionNumber As nvarchar(10));
End
/* Si l'index est très fragmenté et ne contient pas plusieurs partitions ou ne contient pas de LOB,
ou si l'index n'autorise pas les verrous sur page => REBUILD pour index */
Else If (@fragmentation >= @rebuildThreshold Or @allowPageLocks <> 0)
And IsNull(@containsLOB, 0) != 1 And @partitionCount <= 1
Begin
If @onlineRebuild = 1 And @editionCheck = 1
Set @rebuildCommand = N' Rebuild With (Online = On';
Else
Set @rebuildCommand = N' Rebuild With (Online = Off';
If @sortInTempDB = 1
Set @rebuildCommand = @rebuildCommand + N', Sort_In_TempDB = On';
Else
Set @rebuildCommand = @rebuildCommand + N', Sort_In_TempDB = Off';
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
Else
If @printCommands = 1 Or @debugMode = 1
RaisError('Impossible de défragmenter cet index !', 0, 42) With NoWait;
If @executeSQL = 1
Begin
Set @debugMessage = 'Exécuter : ' + @sqlCommand;
If @printCommands = 1 Or @debugMode = 1
RaisError(@debugMessage, 0, 42) With NoWait;
Set @dateTimeStart = GetDate();
Insert Into dbo.dba_indexDefragLog
(
databaseID
, databaseName
, objectID
, objectName
, indexID
, indexName
, partitionNumber
, fragmentation
, page_count
, dateTimeStart
, sqlStatement
)
Select
@databaseID
, @databaseName
, @objectID
, @objectName
, @indexID
, @indexName
, @partitionNumber
, @fragmentation
, @pageCount
, @dateTimeStart
, @sqlCommand;
Set @indexDefrag_id = Scope_Identity();
Begin Try
Execute sp_executesql @sqlCommand;
Set @dateTimeEnd = GetDate();
Update dbo.dba_indexDefragLog
Set dateTimeEnd = @dateTimeEnd
, durationSeconds = DateDiff(second, @dateTimeStart, @dateTimeEnd)
Where indexDefrag_id = @indexDefrag_id;
End Try
Begin Catch
Update dbo.dba_indexDefragLog
Set dateTimeEnd = GetDate()
, durationSeconds = -1
, errorMessage = Error_Message()
Where indexDefrag_id = @indexDefrag_id;
If @debugMode = 1
RaisError('Erreur lors de l'exécution de cette commande - consulter la table dba_indexDefragLog pour plus de détails', 0, 42) With NoWait;
End Catch
WaitFor Delay @defragDelay;
Update dbo.dba_indexDefragStatus
Set defragDate = GetDate()
, printStatus = 1
Where databaseID = @databaseID
And objectID = @objectID
And indexID = @indexID
And partitionNumber = @partitionNumber;
End
Else
Begin
If @printCommands = 1 Or @debugMode = 1
Print IsNull(@sqlCommand, 'erreur !');
Update dbo.dba_indexDefragStatus
Set printStatus = 1
Where databaseID = @databaseID
And objectID = @objectID
And indexID = @indexID
And partitionNumber = @partitionNumber;
End
End
/* résumé du traitement */
If @printFragmentation = 1
Begin
If @debugMode = 1 RaisError(' Afficher résumé traitement...', 0, 42) With NoWait;
Select databaseID
, databaseName
, objectID
, objectName
, indexID
, indexName
, partitionNumber
, fragmentation
, page_count
, range_scan_count
From dbo.dba_indexDefragStatus
Where defragDate >= @startDateTime
Order By defragDate;
End;
End Try
Begin Catch
Set @debugMessage = Error_Message() + ' (Numéro de ligne : ' + Cast(Error_Line() As varchar(10)) + ')';
Print @debugMessage;
End Catch;
Drop Table #databaseList;
Drop Table #processor;
Drop Table #maxPartitionList;
Set NoCount Off;
Return 0
End
Voici un exemple d'utilisation :
Exec dbo.dba_indexDefrag_sp
@executeSQL = 1
, @printCommands = 1
, @debugMode = 1
, @printFragmentation = 1
, @forceRescan = 1
, @maxDopRestriction = 1
, @minPageCount = 8
, @maxPageCount = Null
, @minFragmentation = 1
, @rebuildThreshold = 30
, @defragDelay = '00:00:05'
, @defragOrderColumn = 'page_count'
, @defragSortOrder = 'DESC'
, @excludeMaxPartition = 1
, @timeLimit = Null;
Nous vous conseillons de "packager" cet exemple dans un JOB (SQL Agent JOB) ou de l'exécuter en mode commande.
Aucun commentaire:
Enregistrer un commentaire