Syndication : Atom 1.0  RSS 2.0
Blogs des développeurs   »   Le blog d'ElSuket

Article complet: Une procédure stockée pour défragmenter les indexes sous SQL Server 2005 et ultérieur

05/06/2009

Permalink 17:53:33, Catégories: Indexation, Récapitulatif SGBD, SQL Server, Agent SQL Server, 1800 mots   French (FR) , elsuket

[SGBD][SQL Server] Une procédure stockée pour défragmenter les indexes sous SQL Server 2005 et ultérieur

Voici une petite procédure stockée que l'on peut exécuter régulièrement dans un job pour défragmenter les indexes de toutes les bases de données, en fixant les seuils de nombre de page et de pourcentage moyen de fragmentation

[Suite:]

--------------------------------- 
-- Nicolas SOUQUET - 05/06/2009 - 
--------------------------------- 
CREATE PROCEDURE [dbo].[Ps_Indexes_Rebuild] 
AS 
BEGIN 
  SET NOCOUNT ON 
 
  -- Table dans laquelle on va récupérer les noms et caractéristiques des indexes 
  DECLARE @Tbindexes TABLE 
  ( 
    DB SYSNAME, 
    Tables SYSNAME, 
    IndexName SYSNAME, 
    Frag VARCHAR(5), 
    PageUsage VARCHAR(5), 
    Pages VARCHAR(10), 
    FillFactors TINYINT, 
    Seeks VARCHAR(20), 
    Scans VARCHAR(20), 
    LastUserSeek VARCHAR(19), 
    LastUserScan VARCHAR(19), 
    IsUniqueConstraint BIT, 
    IsPadded BIT, 
    IsHypothetical BIT 
  ) 
 
  -- Table dans laquelle on récupère les statistiques des indexes 
  DECLARE @TbIndexesStats TABLE 
  ( 
    database_id SMALLINT, 
    object_id INT, 
    index_id INT, 
    avg_fragmentation_in_percent NUMERIC(4,2), 
    avg_page_space_used_in_percent NUMERIC(4,2), 
    page_count BIGINT, 
    user_seeks BIGINT, 
    user_scans BIGINT, 
    last_user_seek DATETIME, 
    last_user_scan DATETIME 
  ); 
 
  -- Récupération des stats sur les indexes 
  WITH CTE_DB AS 
  ( 
    SELECT database_id 
    FROM sys.databases 
    WHERE name NOT IN ('master', 'model', 'msdb', 'tempdb', 'ReportServer', 'ReportServerTempDB', 'distribution') 
  ) 
  INSERT INTO @TbIndexesStats 
  SELECT IPS.database_id, 
      IPS.object_id, 
      IPS.index_id, 
      IPS.avg_fragmentation_in_percent, 
      IPS.avg_page_space_used_in_percent, 
      IPS.page_count, 
      IUS.user_seeks, 
      IUS.user_scans, 
      IUS.last_user_seek, 
      IUS.last_user_scan 
  FROM sys.dm_db_index_physical_stats(NULL, NULL, NULL, NULL, 'SAMPLED') IPS 
  JOIN CTE_DB ON CTE_DB.database_id = IPS.database_id 
  JOIN sys.dm_db_index_usage_stats IUS 
      ON IUS.database_id = IPS.database_id 
      AND IUS.object_id = IPS.object_id 
      AND IUS.index_id = IPS.index_id 
  WHERE IPS.index_id > 0 
  AND IPS.avg_fragmentation_in_percent > 25.0 
  AND IPS.page_count > 50 
 
  -- Récupération des noms et caractéristiques des indexes 
  DECLARE @SQL VARCHAR(1024), 
      @Database_id VARCHAR(10), 
      @Object_id VARCHAR(10), 
      @Index_id VARCHAR(10), 
      @AvgFragInPercent VARCHAR(5), 
      @AvgPageSpaceUsedInPercent VARCHAR(5), 
      @PageCount VARCHAR(10), 
      @User_seeks VARCHAR(20), 
      @User_scans VARCHAR(20), 
      @Last_user_seek VARCHAR(19), 
      @Last_user_scan VARCHAR(19) 
 
  DECLARE curDBIndexes CURSOR FOR 
    SELECT CAST(database_id AS VARCHAR(10)), 
      CAST(object_id AS VARCHAR(10)), 
      CAST(index_id AS VARCHAR(10)), 
      CAST(avg_fragmentation_in_percent AS VARCHAR(5)), 
      CAST(avg_page_space_used_in_percent AS VARCHAR(5)), 
      CAST(page_count AS VARCHAR(10)), 
      CAST(user_seeks AS VARCHAR(20)), 
      CAST(user_scans AS VARCHAR(20)), 
      COALESCE(CONVERT(VARCHAR(19), last_user_seek, 120), 'NULL'), 
      COALESCE(CONVERT(VARCHAR(19), last_user_scan, 120), 'NULL') 
    FROM @TbIndexesStats 
  FOR READ ONLY 
 
  OPEN curDBIndexes 
  FETCH NEXT FROM curDBIndexes INTO @Database_id, @Object_id, @Index_id, @AvgFragInPercent, 
                    @AvgPageSpaceUsedInPercent, @PageCount, @User_seeks, @User_scans, 
                    @Last_user_seek, @Last_user_scan 
  WHILE @@FETCH_STATUS = 0 
  BEGIN 
    SET @SQL = 'SELECT DB_NAME(' + @Database_id + '),' + 
            'O.name,' + 
            'IDX.name,' + 
            @AvgFragInPercent + ',' + 
            @AvgPageSpaceUsedInPercent + ',' + 
            @PageCount + ',' + 
            'IDX.fill_factor, ' + 
            @User_seeks + ',' + 
            @User_scans + ',''' + 
            @Last_user_seek + ''',''' + 
            @Last_user_scan + ''',' + 
            'IDX.is_unique_constraint, ' + 
            'IDX.is_padded, ' + 
            'IDX.is_hypothetical' + 
          ' FROM ' + DB_NAME(CAST(@Database_id AS INT)) + '.sys.indexes IDX' + 
          ' JOIN ' + DB_NAME(CAST(@Database_id AS INT)) + '.sys.objects O ON O.object_id = IDX.object_id' + 
          ' WHERE IDX.object_id = ' + @Object_id + 
          ' AND IDX.index_id = ' + @Index_id 
 
    INSERT INTO @Tbindexes 
    EXEC (@SQL) 
 
    FETCH NEXT FROM curDBIndexes INTO @Database_id, @Object_id, @Index_id, @AvgFragInPercent, 
                      @AvgPageSpaceUsedInPercent, @PageCount, @User_seeks, @User_scans, 
                      @Last_user_seek, @Last_user_scan 
  END 
  DEALLOCATE curDBIndexes 
 
  -- Reconstruction des indexes 
  DECLARE @DB SYSNAME, 
      @Tables SYSNAME, 
      @IndexName SYSNAME, 
      @FillFactors VARCHAR(5), 
      @IsPadded BIT, 
      @RebuildCommand VARCHAR(1024) 
 
  DECLARE CUR_INDEXES_TO_REBUILD CURSOR FOR 
    SELECT DB, 
        Tables, 
        IndexName, 
        CAST( 
            CASE FillFactors 
              WHEN 0 THEN 99 
              ELSE FillFactors 
            END AS VARCHAR(5) 
          ), 
        IsPadded 
    FROM @Tbindexes 
  FOR READ ONLY 
 
  OPEN CUR_INDEXES_TO_REBUILD 
  FETCH NEXT FROM CUR_INDEXES_TO_REBUILD INTO @DB, @Tables, @IndexName, @FillFactors, @IsPadded 
  WHILE @@FETCH_STATUS = 0 
  BEGIN 
    SELECT @RebuildCommand = 'ALTER INDEX ' + @IndexName + ' ON ' + @DB + '.dbo.' + @Tables +  
                  ' REBUILD WITH (FILLFACTOR = ' + @FillFactors +  
                  CASE @IsPadded 
                    WHEN 1 THEN ', PAD_INDEX = ON)' 
                    ELSE ')' 
                  END 
    
    EXEC (@RebuildCommand) 
    FETCH NEXT FROM CUR_INDEXES_TO_REBUILD INTO @DB, @Tables, @IndexName, @FillFactors, @IsPadded 
  END 
  DEALLOCATE CUR_INDEXES_TO_REBUILD 
END

ElSuket

Permalien 2 commentaires

Social Bookmarking:

                                     

Commentaires, Pingbacks:

Connectez-vous pour vous abonner à cet article:

Flux de commentaires pour cet article : Atom 1.0  RSS 2.0
Commentaire de: micniv [Membre]
Merci Elsuket pour toutes tes contributions, toujours très utiles.
Ici je signale simplement que les 2 occurences de @@ETCH_STATUS doivent être corrigées en @@FETCH_STATUS.
Ensuite la procédure s'éxécute correctement.
Ce serait un plus si l'on pouvait tracer les tables et les indexes sur lesquels la procédure a agit.

Merci encore.


Permalien 09/11/2009 @ 15:54
Commentaire de: elsuket [Membre]
Merci pour ce retour !

Je viens de corriger l'erreur sur le @@FETCH_STATUS.
Tu peux voir rapidement sur quels indexes la procédure stockée est passée en interrogeant la table sys.indexes avec la fonction STATS_DATE() :

SELECT O.name AS nomobjet,
I.name AS nomIndex,
STATS_DATE(I.object_id, I.index_id) AS dateDerniereMAJ
FROM sys.indexes AS I
JOIN sys.objects AS O
ON I.object_id = O.object_id
AND O.type_desc IN ('U', 'V')

J'ai mis U (table) et V (vue) si tu as des vues indexées ;)
Sinon tu peux supprimer la ligne AND de la jointure et remplacer sys.objects par sys.tables ou sys.views, au choix.

Bonne défragmentation ;)
Permalien 30/11/2009 @ 22:16

Vous devez être identifié pour poster un commentaire.

Liste des blogs

Le blog d'ElSuket

ElSüket

Catégories


Rechercher






<  Mai 2012  >
Lun Mar Mer Jeu Ven Sam Dim
  1 2 3 4 5 6
7 8 9 10 11 12 13
14 15 16 17 18 19 20
21 22 23 24 25 26 27
28 29 30 31      

Liens

Indexation

  • [.NET][SGBD][SQL Server] Différence entre ALTER INDEX ... REBUILD et ALTER INDEX ... REORGANIZE

    Après avoir vu ce que sont la fragmentation interne et externe d'un index, voyons les différences entre les options REBUILD et REORGANIZE de l'instruction ALTER INDEX (ou respectivement DBCC DBREINDEX ou DBCC INDEXDEFRAG sous SQL Server 2000)
    ]suite

    Permalien
  • [SGBD][SQL Server] Différence entre fragmentation interne et externe d'un index

    Quand on parle de la fragmentation physique des indexes, on parle aussi de fragmentation externe. Celle-ci se "produit" lorsque l'ordre logique des pages de l'index est incorrect. Les nouvelles valeurs de clé de l'index sont alors insérées dans de nouvelles pages d'index, qui désordonnent l'ordre original de la clé de l'index.

    Quand on parle de fragmentation logique de l'index, on parle aussi de fragmentation interne : c'est le cas lorsque la quantité de données stockée dans les pages de l'index est plus petite que la quantité maximale de données que peut stocker une page.

    ElSuket

    Permalien
  • [.NET][SGBD][SQL Server] Lister les caractéristiques des indexes sous SQL Server 2005 et 2008

    Voici une requête qui nous permet de retrouver pour tout index :

    - la liste de ses colonnes clé
    - la liste de ses colonnes incluses
    - la définition de son filtre
    - le script de création de cet index

    ]suite

    Permalien
  • [SGBD][SQL Server] Lister les colonnes des index d'une base de données

    Voici une petite requête qui permet de lister les colonnes de tous les index d'une base de données, avec leur type et l'ordre des colonnes dans la clé de l'index :
    ]suite

    Permalien
  • [SGBD][SQL Server] Purge du cache de plans

    Il est possible qu'un jour vous trouviez dans les journaux de SQL Server le libellé suivant :

    SQL Server has encountered n occurrence(s) of cachestore flush for the (partie du cache de plans) cachestore due to some database maintenance or reconfigure operations"

    Ce message n'apparaît qu'à partir du SP2 de SQL Server 2005, et il est écrit par intervalles de 5 minutes.
    La purge du cache de plans peut se produire dans les cas suivants :
    ]suite

    Permalien
  • [SGBD][SQL Server] Recherche d'indexes manquants sous SQL Server 2005

    Une nouvelle fonctionnalité intéressante, introduite avec SQL Server 2005, est la recherche d’indexes manquants.
    Elle permet, de façon très simple, de trouver les indexes manquants qui pourraient simplifier le travail
    du moteur de base de données s’ils étaient posés sur des tables de base ou des vues indexées.
    Néanmoins, cette fonctionnalité comporte quelques limitations, qui doivent être prise en compte avant qu’on
    ait décidé de créer l’index conseillé par SQL Server.
    Comme vous le verrez, plusieurs sujets sont connexes à cet article, mais nous ne les aborderons pas ici.
    Ils seront l’objet de prochains articles.

    ]suite

    Permalien
  • [.NET][SGBD][SQL Server] Rechercher les index inutiles

    Si les index représentent l'optimisation la plus simple à mettre en place, on souhaite néanmoins conserver le minimum d'entre-eux, car leur maintenance lors de l'exécution de requêtes de modifications de données (INSERT, UPDATE, DELETE) peut être coûteuse, surtout sur des tables volumineuses.
    Voyons comment collecter cette information ...
    ]suite

    Permalien
  • [.NET][SGBD][SQL Server] Une procédure pour connaître l'état physique et l'utilisation des index

    Voici une petite procédure stockée qui permet de connaître l'état physique des index (nombre de pages du niveau feuille, fragmentation et taux d'utilisation des pages) en même temps que la façon dont ils sont utilisés (nombres de seeks et de scans, ...).

    Elle est utilisable pour collecter ces statistiques sur l'ensemble d'une base de données, ou bien sur une table en particulier
    ]suite

    Permalien
  • [SGBD][SQL Server] Une procédure stockée pour défragmenter les indexes sous SQL Server 2005 et ultérieur

    Voici une petite procédure stockée que l'on peut exécuter régulièrement dans un job pour défragmenter les indexes de toutes les bases de données, en fixant les seuils de nombre de page et de pourcentage moyen de fragmentation

    ]suite

    Permalien
  • [SGBD][SQL Server] [Agent SQL Server] Gestion de l'historique

    Outre sa principale fonctionnalité de gestion d'exécutions, l'Agent SQL Server comporte quelques fonctionnalités intéressantes concernant la gestion de son historique.
    ]suite

    Permalien

Snippets

  • [SGBD][SQL Server] Calculer le Buffer Cache Hit Ratio

    S'il est une valeur intéressante à suivre pour le performances d'un serveur, c'est bien le Buffer Cache Hit Ratio, ou Taux d'accès au cache des tampons, car celui-ci est un rapport du nombre de pages lues en RAM par rapport au nombre de pages lues à partir des disques.

    Voici une petite requête pour trouver sa valeur :

    ]suite

    Permalien
  • [SGBD][SQL Server] Caractériser une adresse IP

    Voici une procédure stockée pour :

    - vérifier une adresse IP
    - chercher si celle-ci est privée
    - chercher la classe de cette adresse IP

    ]suite

    Permalien
  • [SGBD][SQL Server] Comment exécuter une procédure stockée dès le démarrage de SQL Server ?

    Nous allons voir comment on peut exécuter n'importe quelle procédure stockée dès le démarrage de SQL Server, à l'aide de la procédure stockée sp_procoption

    ]suite

    Permalien
  • [SGBD][SQL Server] Concaténer les valeurs d'une colonne dans une variable

    Il est souvent demandé s'il est possible de concaténer les valeurs d'une colonne dans une variable, éventuellement en les séparant avec un symbole de ponctuation.
    Voici comment faire, sans curseur ni expression de table commune ...
    ]suite

    Permalien
  • [SGBD][SQL Server] Connaître l'état d'un service : xp_servicecontrol

    Comment connaître simplement l'état d'un service ?
    Est-il possible d'arrêter et de démarrer un service avec une requête ?

    ]suite

    Permalien
  • [SGBD][SQL Server] Consulter l'historique des restaurations de bases de données

    Voici une petite requête qui permet de voir toutes les restaurations de bases de données d'une instance ...

    ]suite

    Permalien
  • [SGBD][SQL Server] Créer une table de dates

    Pourquoi ne pas créer une table de dates pour se faciliter les recherches dans des tables suivant la colonne de type date qu'elles contiennent ?
    On peut vous demander par exemple de rechercher la quantité d'eau consommée par un parc de machines pour la deuxième semaine de chaque mois d'une année.
    Imaginez un peu la complexité de la requête à écrire ...
    Voyons comment on peut résoudre cette demande très facilement en créant une table de dates ...

    ]suite

    Permalien
  • [SGBD][SQL Server] Découper une chaîne en sous-chaînes de longueur fixe

    Certains me désignent sur le forum SQL Server comme l'homme qui murmure à l'oreille des CTE.
    En voici donc une nouvelle !

    ]suite

    Permalien
  • [SGBD][SQL Server] Détecter un caractère unicode dans une chaîne de caractères

    Voici une petite fonction qui permet de vérifier la présence d'un caractère qui n'est pas supporté par la norme ASCII dans une chaîne de caractères ...

    ]suite

    Permalien
  • [SGBD][SQL Server] Exemples d'affectations de variables en ligne sous SQL Server 2008

    Voici quelques exemples simples de code qui montrent que sous SQL Server 2008, on peut non seulement se passer des sempiternelles deux lignes de code nécessaires jusqu'en version 2005 pour déclarer et affecter d'un valeur une variable, mais on peut aller encore plus loin ...

    ]suite

    Permalien

Divers

Syndiquez ce blog XML

Articles :

Commentaires :

 
 
Developpez.com

Nous contacter

Participez

Informations légales

 
Services

Forum Blogs

Blogs

Hébergement

 
Partenaires

Hébergement Web