Un système de maintenance des index et structures de stockage

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  * * * * *

MVP Microsoft SQL Server

Laisser un commentaire