Le but de cet ensemble de procédures est de fournir un service simple et efficace pour vérifier l’intégrité des structures de stockage, défragmenter ou reconstruire les index et recalculer les statistiques des index.
La défragmentation des index consiste à nettoyer les « espaces morts » figurant dans les index et les tables. Pour plus d’information, voir : http://sqlpro.developpez.com/optimisation/MaintenanceIndex/ La défragmentation procère de deux méthodes : la reconstruction des index par un « REBUILD » lorsque le taux de fragmentation est important (> 30%) et la réorganisation des données dans les pages et des pages d’index, par un DEFRAG ou REBUILD.
La vérification de l’intégrité des structures de stockage consiste à vérifier l’intégrité physique par un code de redondance (CHECKSUM) et logique par vérification des contraintes.
Le présent système de maintenance système est organisé en deux procédures et une table :
La table S_MAINT.T_HISTORIQUE_MAINTENANCE_HMT doit être créée dans la base MSDB et reçoit toutes les opérations de maintenance à effectuer pour toutes les bases concernées.
La procédure S_MAINT.P_SET_MAINTENANCE_BASE alimente la table S_MAINT.T_HISTORIQUE_MAINTENANCE_HMT avec les opérations de maintenance à effectuer pour la base considérée. A ce titre elle doit être présente dans toutes les bases que l’on veut traiter.
la procédure S_MAINT.P_EXEC_MAINTENANCE_BASE effectue tous les travaux de maintenance à faire indiqués dans la table S_MAINT.T_HISTORIQUE_MAINTENANCE_HMT.
Le tous devant être planifié, par exemple pour être joué tous les jours, si possible aux heures creuses.
1 – table S_MAINT.T_HISTORIQUE_MAINTENANCE_HMT
USE MSDB ;
GO
CREATE SCHEMA S_MAINT;
GO
CREATE TABLE S_MAINT.T_HISTORIQUE_MAINTENANCE_HMT
( HMT_ID BIGINT IDENTITY NOT NULL PRIMARY KEY ,
HMT_DATABASE sysname ,
HMT_DATATYPE VARCHAR(16),
HMT_SCHEMA sysname ,
HMT_NAME sysname,
HMT_SUB NVARCHAR(128) ,
HMT_MEASURE FLOAT ,
HMT_ACTION VARCHAR(16) ,
HMT_PARAMETRE FLOAT,
HMT_REQUEST_DATE DATETIME DEFAULT CURRENT_TIMESTAMP,
HMT_BEGIN_DATE DATETIME ,
HMT_END_DATE DATETIME ,
HMT_DURATION_MS AS DATEDIFF(ms, HMT_BEGIN_DATE, HMT_END_DATE) PERSISTED,
HMT_ERROR_MESSAGE NVARCHAR(2048)
)
2 Рproc̩dure S_MAINT.P_SET_MAINTENANCE_BASE
USE <mabase>;
GO
CREATE SCHEMA S_MAINT;
GO
CREATE PROCEDURE S_MAINT.P_SET_MAINTENANCE_BASE
@VERIF BIT = 1, -- propose de vérifier l'intégrité physique de la base
@INDEX BIT = 1, -- propose de maintenir les index (et statistiques)
@SEUIL_DEFRAG FLOAT = 10, -- seuil minimal en % de fragmentation pour défragmenter la table ou l'index
@SEUIL_REBUILD FLOAT = 30, -- seuil minimal en % pour la reconstruction (doit être supérieur au seuil de defrag
@SEUIL_PAGE INT = 16, -- seuil minimal de page pour prendre en compte la maintenance de l'index
@SEUIL_STAT INT = 1000 -- seuil maximal au delà duquel seul un échantillon des données est utilisé pour les stats.
AS
/******************************************************************************
* Maintenance basique : intégrité physique et nettoyage des index *
* Procédure d'alimentation des opérations de maintenance à effectuer *
*******************************************************************************
* Frédéric Brouard - SQLpro - MVP SQL Server - www.sqlspot.com - 2010-03-24 *
*******************************************************************************
* Cette procédure alimente une table des opérations de maintenance à réaliser *
* notamment la défragmentation et la reconstruction des index, ainsi que la *
* vérification physique des pages de stockage des données (index et table) de *
* la base. Elle est à placer dans toutes les bases à maintenir et a exécuter *
* régulièrement (une fois par jour par exemple) *
* Un seconde procédure à planifier aux heures creuses P_EXEC_MAINTENANCE_BASE *
* scrute la table et exécute la maintenance. *
* *
* NOTA : description de la table de maintenance spécialement créée dans la *
* base msdb afin de centraliser toutes les opérations de maintenance a *
* effectuer. *
*******************************************************************************/
SET NOCOUNT ON;
-- contrôle la validité des paramètres de seuils de maintenance des index
IF @SEUIL_REBUILD <= @SEUIL_DEFRAG
BEGIN
RAISERROR('Le seuil de reconstruction (ici %d) doit être supérieur au seuil de défragmentaion qui est de %d.', 16, 1);
RETURN;
END;
-- insertion des index et des stats à maintenir
INSERT INTO msdb.S_MAINT.T_HISTORIQUE_MAINTENANCE_HMT
(HMT_DATABASE ,
HMT_DATATYPE,
HMT_SCHEMA ,
HMT_NAME,
HMT_SUB ,
HMT_MEASURE ,
HMT_ACTION ,
HMT_PARAMETRE)
SELECT DB_NAME (),
CASE i.type_desc
WHEN 'CLUSTERED' THEN 'TABLE CLUSTERED'
WHEN 'NONCLUSTERED' THEN 'INDEX'
WHEN 'HEAP' THEN 'TABLE'
ELSE 'INDEX ' + i.type_desc
END ,
OBJECT_SCHEMA_NAME(i.object_id) AS TableSchema,
OBJECT_NAME(i.object_id) AS TableName,
i.name AS IndexName,
PAGE_count,
CASE WHEN avg_fragmentation_in_percent >= @SEUIL_REBUILD
AND PAGE_count >= @SEUIL_PAGE THEN 'REBUILD'
WHEN avg_fragmentation_in_percent >= @SEUIL_DEFRAG
AND PAGE_count >= @SEUIL_PAGE THEN 'DEFRAG'
ELSE 'STAT'
END AS TRAITEMENT,
CAST(CEILING(CASE WHEN PAGE_count < @SEUIL_STAT THEN 100
WHEN PAGE_count >= @SEUIL_STAT
THEN 100.0 - (100.0 * ((CAST(PAGE_count AS FLOAT)
- CAST(@SEUIL_STAT AS FLOAT))
/ CAST(PAGE_count AS FLOAT) ) ) / LOG10(PAGE_count)
ELSE 0
END) AS INT) AS PercentPAGEs
FROM sys.indexes AS i
INNER JOIN sys.objects o
ON o.object_id = i.object_id
LEFT OUTER JOIN sys.dm_db_index_physical_stats(DB_ID(),
NULL,
NULL,
NULL,
NULL) AS ips
ON i.object_id = ips.object_id
AND i.index_id = ips.index_id
WHERE o.type = 'U'
AND COALESCE(alloc_unit_type_desc, '') <> 'LOB_DATA'
AND i.is_disabled = 0;
--> insertion des données pour vérification des structures de stockage
-- pour les pages
INSERT INTO msdb.S_MAINT.T_HISTORIQUE_MAINTENANCE_HMT
( HMT_DATABASE,
HMT_DATATYPE,
HMT_ACTION,
HMT_SCHEMA ,
HMT_NAME)
SELECT DB_NAME() ,
'DBCC',
'CHECKALLOC',
'',
''
UNION ALL
-- pour les tables systèmes
SELECT DB_NAME() ,
'DBCC',
'CHECKCATALOG',
'',
''
UNION ALL
-- pour les tables de production
SELECT DB_NAME() ,
'DBCC',
'CHECKTABLE' ,
OBJECT_SCHEMA_NAME(o.object_id) AS TableSchema ,
OBJECT_NAME(o.object_id) AS TableName
FROM sys.objects o
WHERE o.type = 'U'
UNION ALL
-- pour les vues indexées de production
SELECT DB_NAME() ,
'DBCC',
'CHECKTABLE' ,
OBJECT_SCHEMA_NAME(v.object_id) AS TableSchema ,
OBJECT_NAME(v.object_id) AS TableName
FROM sys.views AS v
INNER JOIN sys.indexes AS i ON v.object_id = i.object_id;
GO
3 – procédure d’exécution de la maintenance
USE msdb;
GO
CREATE PROCEDURE [S_MAINT].[P_EXEC_MAINTENANCE_BASE]
@RETENTION_DAYS INT = 1000, -- limite de rétention des lignes de la table de maintenance en profondeur de jours
@RETENTION_DATE DATETIME -- limite de rétention des lignes de la table de maintenance en date limite
AS
/******************************************************************************
* Maintenance basique : exécution des opérations de maintenance *
* Procédure de traitement des opérations de maintenance à effectuer *
*******************************************************************************
* Frédéric Brouard - SQLpro - MVP SQL Server - www.sqlspot.com - 2010-03-24 *
*******************************************************************************
* Cette procédure lit la une table des opérations de maintenance à réaliser *
* et les exécutent une à une *
* Elle est à placer dans une base de maintenance (msdb à défaut et a exécuter *
* régulièrement (une fois par nuit par exemple) *
* *
* NOTA : description de la table de maintenance spécialement créée dans la *
* base msdb afin de centraliser toutes les opérations de maintenance a *
* effectuer. Constitution de cette table : *
USE MSDB ;
GO
CREATE SCHEMA S_MAINT;
GO
CREATE TABLE S_MAINT.T_HISTORIQUE_MAINTENANCE_HMT
( HMT_ID BIGINT IDENTITY NOT NULL PRIMARY KEY ,
HMT_DATABASE sysname ,
HMT_DATATYPE VARCHAR(16),
HMT_SCHEMA sysname ,
HMT_NAME sysname,
HMT_SUB NVARCHAR(128) ,
HMT_MEASURE FLOAT ,
HMT_ACTION VARCHAR(16) ,
HMT_PARAMETRE FLOAT,
HMT_REQUEST_DATE DATETIME DEFAULT CURRENT_TIMESTAMP,
HMT_BEGIN_DATE DATETIME ,
HMT_END_DATE DATETIME ,
HMT_DURATION_MS AS DATEDIFF(ms, HMT_BEGIN_DATE, HMT_END_DATE) PERSISTED,
HMT_ERROR_MESSAGE NVARCHAR(2048)
)
*******************************************************************************/
SET NOCOUNT ON;
DECLARE @HMT_ID BIGINT ,
@HMT_DATABASE SYSNAME ,
@OBJET NVARCHAR(261) ,
@HMT_SUB NVARCHAR(128) ,
@HMT_ACTION VARCHAR(16) ,
@ERROR_MSG VARCHAR(2048) ,
@HMT_PARAMETRE FLOAT ,
@SQL NVARCHAR(MAX) ,
@DT1 DATETIME ,
@DT2 DATETIME ;
-- tant qu'il y a quelque chose à faire
WHILE EXISTS ( SELECT *
FROM msdb.S_MAINT.T_HISTORIQUE_MAINTENANCE_HMT
WHERE HMT_BEGIN_DATE IS NULL )
BEGIN
-- prendre la première chose à faire
SELECT TOP 1
@HMT_ID = HMT_ID ,
@HMT_DATABASE = HMT_DATABASE ,
@OBJET = '[' + HMT_SCHEMA + '].[' + HMT_NAME + ']' ,
@HMT_SUB = HMT_SUB ,
@HMT_ACTION = HMT_ACTION ,
@HMT_PARAMETRE = HMT_PARAMETRE
FROM msdb.S_MAINT.T_HISTORIQUE_MAINTENANCE_HMT
WHERE HMT_BEGIN_DATE IS NULL
ORDER BY HMT_ID;
-- statistique à recalculer sur index
IF @HMT_ACTION = 'STAT'
AND @HMT_SUB IS NOT NULL
SET @SQL = 'UPDATE STATISTICS [' + @HMT_DATABASE + '].' + @OBJET
+ ' ( [' + @HMT_SUB + '] ) WITH SAMPLE '
+ CAST(COALESCE(NULLIF(@HMT_PARAMETRE, 0), 100) AS NVARCHAR(32))
+ ' PERCENT ; ' ;
-- statistique à recalculer sans index
IF @HMT_ACTION = 'STAT'
AND @HMT_SUB IS NULL
SET @SQL = 'UPDATE STATISTICS [' + @HMT_DATABASE + '].' + @OBJET
+ ' WITH SAMPLE '
+ CAST(COALESCE(NULLIF(@HMT_PARAMETRE, 0), 100) AS NVARCHAR(32))
+ ' PERCENT ; ' ;
-- index à reconstruire
IF @HMT_ACTION = 'REBUILD'
AND @HMT_SUB IS NOT NULL
SET @SQL = 'ALTER INDEX [' + @HMT_SUB + '] ON [' + @HMT_DATABASE
+ '].' + @OBJET + ' REBUILD; ' ;
-- table à reconstruire
IF @HMT_ACTION = 'REBUILD'
AND @HMT_SUB IS NULL
SET @SQL = 'ALTER INDEX ALL ON [' + @HMT_DATABASE + '].' + @OBJET
+ ' REBUILD ; ' ;
-- index à défragmenter (et recalcul des statistiques)
IF @HMT_ACTION = 'DEFRAG'
AND @HMT_SUB IS NOT NULL
SET @SQL = 'ALTER INDEX [' + @HMT_SUB + '] ON [' + @HMT_DATABASE + '].' + @OBJET
+ ' REORGANIZE ' + '; UPDATE STATISTICS [' + @HMT_DATABASE
+ '].' + @OBJET + ' ( [' + @HMT_SUB + '] ) WITH SAMPLE '
+ CAST(COALESCE(NULLIF(@HMT_PARAMETRE, 0), 100) AS NVARCHAR(32))
+ ' PERCENT ; ' ;
-- table à défragmenter (et recalcul des statistiques)
IF @HMT_ACTION = 'DEFRAG'
AND @HMT_SUB IS NULL
SET @SQL = 'ALTER INDEX ALL ON [' + @HMT_DATABASE + '].' + @OBJET + ' REORGANIZE '
+ '; UPDATE STATISTICS [' + @HMT_DATABASE + '].' + @OBJET
+ ' ( [' + @HMT_SUB + '] ) WITH SAMPLE '
+ CAST(COALESCE(NULLIF(@HMT_PARAMETRE, 0), 100) AS NVARCHAR(32))
+ ' PERCENT ; ' ;
-- vérification des structures d'allocation
IF @HMT_ACTION = 'CHECKALLOC'
SET @SQL = 'DBCC CHECKALLOC (''' + @HMT_DATABASE + ''') WITH NO_INFOMSGS ;';
-- vérification des tables systèmes
IF @HMT_ACTION = 'CHECKCATALOG'
SET @SQL = 'DBCC CHECKCATALOG (''' + @HMT_DATABASE + ''') WITH NO_INFOMSGS ;';
-- vérification d'une table
IF @HMT_ACTION = 'CHECKTABLE'
SET @SQL = 'DBCC CHECKTABLE (''' + @HMT_DATABASE + '.'+ @OBJET + ''') WITH NO_INFOMSGS ;';
-- exécution de l'opération
SET @ERROR_MSG = NULL ;
SET @DT1 = CURRENT_TIMESTAMP ;
BEGIN TRY
EXEC (@SQL);
END TRY
BEGIN CATCH
SET @ERROR_MSG = ERROR_MESSAGE() ;
END CATCH
SET @DT2 = CURRENT_TIMESTAMP ;
-- mise à jour de l'opération effectuée
UPDATE msdb.S_MAINT.T_HISTORIQUE_MAINTENANCE_HMT
SET HMT_BEGIN_DATE = @DT1 ,
HMT_END_DATE = @DT2 ,
HMT_ERROR_MESSAGE = @ERROR_MSG
WHERE HMT_ID = @HMT_ID;
SET @ERROR_MSG = NULL;
END;
-- suppressions des lignes trop anciennes de la table
DELETE FROM msdb.S_MAINT.T_HISTORIQUE_MAINTENANCE_HMT
WHERE HMT_REQUEST_DATE < DATEADD(DAY, @RETENTION_DAYS * -1, CURRENT_TIMESTAMP)
OR HMT_REQUEST_DATE < @RETENTION_DATE;
GO
4 – Utilisation
Créez la procédure S_MAINT.P_SET_MAINTENANCE_BASE dans chacune des bases à maintenir.
Dans l’agent SQL Server, planifiez un travail constitué d’autant d’étapes qu’il y a de bases de données afin d’alimenter la table de travail à l’aide de la procédure S_MAINT.P_SET_MAINTENANCE_BASE exécutée dans le contexte de la base visée. Par exemple s’il y a deux bases B1 et B2, les travaux à enchainer sont :
EXEC S_MAINT.P_SET_MAINTENANCE_BASE 1, 1, 10, 30, 16, 1000;
Dans chaque contexte des bases B1 et B2, soit deux étapes.
Ce travail peut être planifié à 22h.
Toujours dans l’agent SQL Server, planifiez un travail constitué d’une seule étape exécutant la procédure S_MAINT.P_EXEC_MAINTENANCE_BASE.
Par exemple :
EXEC S_MAINT.P_EXEC_MAINTENANCE_BASE 1000, '20080101';
Ce travail peut être planifié à 0h.
--------
Frédéric Brouard, SQLpro - ARCHITECTE DE DONNÉES, http://sqlpro.developpez.com/
Expert bases de données relationnelles et langage SQL. MVP Microsoft SQL Server
www.sqlspot.com : modélisation, conseil, audit, optimisation, tuning, formation
* * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *
