IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

Administration SQL Server Discussion :

Plan de maintenance pour les indexes : bonnes pratiques ?


Sujet :

Administration SQL Server

  1. #1
    Expert confirmé
    Avatar de Kropernic
    Homme Profil pro
    Analyste / Programmeur / DBA
    Inscrit en
    juillet 2006
    Messages
    3 925
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 38
    Localisation : Belgique

    Informations professionnelles :
    Activité : Analyste / Programmeur / DBA
    Secteur : Distribution

    Informations forums :
    Inscription : juillet 2006
    Messages : 3 925
    Points : 4 220
    Points
    4 220
    Par défaut Plan de maintenance pour les indexes : bonnes pratiques ?
    Bonjour,

    A la lecture de ce sujet, j'ai découvert qu'il était important de prévoir des plans de maintenances pour les indexes.

    En tant que DBA auto-proclamé du petit service où je bosse (nous sommes 2 sans aucune formation de DBA), je vais donc mettre en place ces fameux plans de maintenance.

    Dans cette optique, j'aurais voulu savoir quelles étaient les bonnes pratiques à respecter.

    Je songe à faire un recalcule des statistiques hebdomadaire et une reconstruction mensuelle.

    Est-ce une manière correcte de procéder ?
    Qu'avez-vous mis en place "chez vous" ?

    Merci d'avance.
    Kropernic

  2. #2
    Rédacteur
    Avatar de SQLpro
    Homme Profil pro
    Expert bases de données / SQL / MS SQL Server / Postgresql
    Inscrit en
    mai 2002
    Messages
    20 774
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Expert bases de données / SQL / MS SQL Server / Postgresql
    Secteur : Conseil

    Informations forums :
    Inscription : mai 2002
    Messages : 20 774
    Points : 49 211
    Points
    49 211
    Billets dans le blog
    1
    Par défaut
    Utilisez les scripts que j'ai donné dans mes articles...
    Par exemple celui la qui fait plusieurs maintenance :
    http://blog.developpez.com/sqlpro/p8...es-index-et-s/
    1) vérification des structures de stockage
    2) reconstruction ou défragmentation des index
    3) recalcul des statistiques

    Ces 3 éléments sont essentiels et devraient être fait le plus souvent possible : quotidiennement si possible.

    A +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Expertise SQL Server : http://mssqlserver.fr/ * * * * *

  3. #3
    Membre expérimenté

    Homme Profil pro
    Administrateur de base de données
    Inscrit en
    août 2007
    Messages
    1 216
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 37
    Localisation : Suisse

    Informations professionnelles :
    Activité : Administrateur de base de données
    Secteur : Industrie Pharmaceutique

    Informations forums :
    Inscription : août 2007
    Messages : 1 216
    Points : 1 725
    Points
    1 725
    Par défaut
    Bonjour,

    Je pars sur la mise en place d'une solution basée sur les scripts proposés ici.
    Cette solution me permet de faire des déployments/mises à jour de masse facilement avec un CMS.
    Elle est plus flexible aussi que les plans de maintenance.
    Pour le scheduling des jobs, utilisation d'un SQL Agent master qui distribue les jobs à divers agents secondaires, ce qui me permettra d'avoir un job for all.
    Vu la flexibilité des scripts et que la nuit on est assez libre de faire ce que l'on veut (dans mon cas), execution des jobs de manière quotidienne.

    C'est pas encore en place mais c'est l'idée

  4. #4
    Modérateur
    Avatar de elsuket
    Homme Profil pro
    Administrateur de base de données
    Inscrit en
    janvier 2005
    Messages
    5 822
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 39
    Localisation : France, Hérault (Languedoc Roussillon)

    Informations professionnelles :
    Activité : Administrateur de base de données
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : janvier 2005
    Messages : 5 822
    Points : 12 339
    Points
    12 339
    Par défaut
    Bonjour,

    Pour ma part je n'ai jamais trouvé que les plans de maintenance permettent de faire tout ce que l'on peut faire en T-SQL : par exemple en ce qui concerne la maintenance des statistiques, il est impossible de spécifier un niveau d'échantillonnage, et toutes les statistiques sont passées au crible, ce qui est loin d'être nécessaire.
    D'autre part les plans de maintenance ne sont pas facilement portables (on ne peut pas tout simplement les déployer sur un ensemble de serveurs directement) : il faut changer la chaine de connexion à chaque copie de serveur à serveur, et changer le propriétaire du plan et du job à chaque fois qu'on y touche ...
    Enfin lors du passage à une nouvelle version de SQL Server, il faut convertir les plans de maintenance à cette nouvelle version, ce qui peut se solder par un abandon, suppression du plan de maintenance et recréation complète ... Bref, beaucoup de mal et d'imprécisions.

    En revanche avec un jeu de tables et de procédures stockées bien pensées, si l'on passe bien sûr plus de temps à modéliser et à coder, les déploiements se font très simplement, et l'on est parfaitement maître de ce qui doit être fait, par exemple avec une fenêtre de maintenance.

    C'est clairement ce que montrent les liens proposés par SQLPro et Ptit_Dje, et ma propre expérience.

    @++

  5. #5
    Expert confirmé
    Avatar de Kropernic
    Homme Profil pro
    Analyste / Programmeur / DBA
    Inscrit en
    juillet 2006
    Messages
    3 925
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 38
    Localisation : Belgique

    Informations professionnelles :
    Activité : Analyste / Programmeur / DBA
    Secteur : Distribution

    Informations forums :
    Inscription : juillet 2006
    Messages : 3 925
    Points : 4 220
    Points
    4 220
    Par défaut
    Et bien, merci à tous pour vos précieux conseils.

    Par contre, le truc de la table de maintenant avec les deux procédures stockées, même si ça m'a l'air d'être génial, si je mets cela en place, le jour où je tombe malade ou autre, mon collègue ne s'en sortira jamais s'il y a une intervention à faire

    J'ai regardé un peu de mon côté entre-temps et j'avais songé à créer des packages SSIS avec les tâches adéquates.

    De ce que j'en ai vu (j'ai regardé la tâche Update Statistics et Rebuild Index), ça permet de faire ce qu'il faut automatique sur toutes les tables, celles précisées, ou juste les tables utilisateurs. Ca m'avait l'air pas trop mal comme solution "prête à l'emploi" (ou presque).

    Que pensez-vous de ces taches SSIS ? Est-ce fiable ?
    Je me souviens que sqlpro a dit (écrit en fait) un jour qu'il fallait toujours préférer un script T-SQL plutôt qu'une interface qui ferait le boulot pour nous. En est-il de même ici ?

    EDIT - Question supplémentaire :
    J'ai relevé comme tâche de maintenance les suivantes :
    • Check Database Integrity
    • Rebuild Index
    • Reorganize Index
    • Update Statistic


    Doivent-elles toutes être faites quotidiennement ou bien faut-il alterner ?
    Par exemple, chaque jour, faire un Reorganize Index et une fois par semaine, un Rebuild. (C'est juste un exemple, c'est probablement une connerie).
    Kropernic

  6. #6
    Rédacteur
    Avatar de SQLpro
    Homme Profil pro
    Expert bases de données / SQL / MS SQL Server / Postgresql
    Inscrit en
    mai 2002
    Messages
    20 774
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Expert bases de données / SQL / MS SQL Server / Postgresql
    Secteur : Conseil

    Informations forums :
    Inscription : mai 2002
    Messages : 20 774
    Points : 49 211
    Points
    49 211
    Billets dans le blog
    1
    Par défaut
    Ces tâches SSIS sont grossières et faites pour les neuneus.

    Tu refais trois fois le même boulot.

    Par exemple Un rebuild des index recalcule les stats. Il est donc inutile de faire un recalcul des stats pour les index déjà reconstruit. Or l'algo de SSIS est incapable de savoir ce qu'une tâche à faite précédemment.

    L'intérêt de mon algo avec la table de maintenance est de pouvoir savoir ce qui a été fait ou pas (c'est très utile en cas de problème ultérieur) mais aussi de voir les dérives... En gros l'index qui doit mettre 3 minutes à être reconstruit et qui subitement en met 20 !
    Les script du "Winner" ne permettent pas de suivre cela !

    A +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Expertise SQL Server : http://mssqlserver.fr/ * * * * *

  7. #7
    Expert confirmé
    Avatar de Kropernic
    Homme Profil pro
    Analyste / Programmeur / DBA
    Inscrit en
    juillet 2006
    Messages
    3 925
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 38
    Localisation : Belgique

    Informations professionnelles :
    Activité : Analyste / Programmeur / DBA
    Secteur : Distribution

    Informations forums :
    Inscription : juillet 2006
    Messages : 3 925
    Points : 4 220
    Points
    4 220
    Par défaut
    Ok bin va pour la table de maintenance alors !

    Je vais tenter de mettre cela en place sur le server qui sert aux tests et voir ce que ça donne.

    Je reviendrai donner un feedback.
    Kropernic

  8. #8
    Expert confirmé
    Avatar de Kropernic
    Homme Profil pro
    Analyste / Programmeur / DBA
    Inscrit en
    juillet 2006
    Messages
    3 925
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 38
    Localisation : Belgique

    Informations professionnelles :
    Activité : Analyste / Programmeur / DBA
    Secteur : Distribution

    Informations forums :
    Inscription : juillet 2006
    Messages : 3 925
    Points : 4 220
    Points
    4 220
    Par défaut
    Premier feedback :

    J'ai mis en place sur la DB principale de la boîte (celle qui contient notamment les transactions caisses de nos 15 magasins depuis 2007).

    2 minutes pour procédure qui remplit la table des actions à faire.
    La procédure qui effectue les actions de maintenance est en cours. Le rebuild de l'index de la table des transactions semble prendre pas mal de temps ^^.

    Sinon, ça a l'air de tourner impec.

    J'aurais quand même une question. Pendant ces opérations de maintenance, la table maintenue est-elle toujours accessible ? Je pose cette question car j'ai divers processus qui peuplent les tables durant la nuit.

    EDIT : J'imagine également que si ces opérations sont faites tous les jours, elles prendront moins de temps à être exécutées que si elles sont faites une fois par semaine... Non ?
    Kropernic

  9. #9
    Modérateur
    Avatar de elsuket
    Homme Profil pro
    Administrateur de base de données
    Inscrit en
    janvier 2005
    Messages
    5 822
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 39
    Localisation : France, Hérault (Languedoc Roussillon)

    Informations professionnelles :
    Activité : Administrateur de base de données
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : janvier 2005
    Messages : 5 822
    Points : 12 339
    Points
    12 339
    Par défaut
    Citation Envoyé par Kropernic
    Par contre, le truc de la table de maintenant avec les deux procédures stockées, même si ça m'a l'air d'être génial, si je mets cela en place, le jour où je tombe malade ou autre, mon collègue ne s'en sortira jamais s'il y a une intervention à faire
    Documenter, c'est long, c'est parfois pénible, mais c'est nécessaire précisément pour la situation que vous exposez

    J'aurais quand même une question. Pendant ces opérations de maintenance, la table maintenue est-elle toujours accessible ? Je pose cette question car j'ai divers processus qui peuplent les tables durant la nuit.
    Cela dépend d'une part de l'édition de l'instance SQL Server sous laquelle la base de données est maintenue : on ne peut réaliser la reconstruction d'index en ligne que lorsque l'édition est Enterprise.

    Ensuite certains index ne sont pas reconstructible (ALTER INDEX ... REBUILD) en ligne : les index cluster sur une table contenant une colonne LOB (varchar(max), nvarchar(max), varbinary(max) et xml), ou au moins une colonne de type spatial ou CLR. Il en va de même pour les index spatiaux, ou les index qui ont des colonnes LOB incluses.
    Cependant cela change avec SQL Server 2012, qui le supporte sauf pour les colonnes XML et de type spatial.
    Les opérations de défragmentation (ALTER INDEX ... REORGANIZE) sont toujours effectuées en ligne. Pour en savoir un petit peu plus, lisez ceci

    Enfin, reconstruire une partition d'un index se fait uniquement hors-ligne.

    @++

  10. #10
    Expert confirmé
    Avatar de Kropernic
    Homme Profil pro
    Analyste / Programmeur / DBA
    Inscrit en
    juillet 2006
    Messages
    3 925
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 38
    Localisation : Belgique

    Informations professionnelles :
    Activité : Analyste / Programmeur / DBA
    Secteur : Distribution

    Informations forums :
    Inscription : juillet 2006
    Messages : 3 925
    Points : 4 220
    Points
    4 220
    Par défaut
    Citation Envoyé par elsuket Voir le message
    Documenter, c'est long, c'est parfois pénible, mais c'est nécessaire précisément pour la situation que vous exposez
    Ouais mais c'est une encyclopédie qu'il va falloir que j'écrive alors... C'est un collègue que j'apprécie bcp sur le plan humain mais sur le plan technique, il se fait un peu vieux et est carrément à la traine... (bientôt pensionné le veinard)

    Cela dépend d'une part de l'édition de l'instance SQL Server sous laquelle la base de données est maintenue : on ne peut réaliser la reconstruction d'index en ligne que lorsque l'édition est Enterprise.
    Voici les infos pour mon instance sql :
    Edition : Standard Edition (Il y a beau y avoir des data, ce n'est pas Picard qui commande... (comprendra qui pourra^^))
    ProductLevel : SP4
    ProductVersion : 9.00.5057.00 (2005 quoi...)

    Ensuite certains index ne sont pas reconstructible (ALTER INDEX ... REBUILD) en ligne : les index cluster sur une table contenant une colonne LOB (varchar(max), nvarchar(max), varbinary(max) et xml), ou au moins une colonne de type spatial ou CLR. Il en va de même pour les index spatiaux, ou les index qui ont des colonnes LOB incluses.
    Cependant cela change avec SQL Server 2012, qui le supporte sauf pour les colonnes XML et de type spatial.
    Les opérations de défragmentation (ALTER INDEX ... REORGANIZE) sont toujours effectuées en ligne. Pour en savoir un petit peu plus, lisez ceci

    Enfin, reconstruire une partition d'un index se fait uniquement hors-ligne.

    @++
    Donc en résumé, vu que c'est une version standard de 2005, dès qu'il y a maintenance, la table maintenue n'est plus accessible.
    Par contre, si j'arrive à faire débourser le passage à la version 2012 (entreprise aussi ?) et vu que nous n'utilisons pas toutes ces colonnes de types étranges que vous mentionnez (je ne sais même pas ce que c'est), alors je serais peinard avec mes autres processus (Ceux-ci font principalement des ordres de type INSERT et UPDATE. Peut-être quelques uns de type SELECT mais jamais de DELETE il me semble).

    C'est bien ça où j'ai encore compris de travers ?

    Complément :
    Voici les différents type d'actions effectués. J'avoue ne pas encore être à l'aise avec tout cela alors je donne le plus détails possibles pour être certain d'avoir un conseil/avis adapté à ma situation.
    DBCC CHECKALLOC
    DBCC CHECKCATALOG
    DBCC CHECKTABLE
    INDEX DEFRAG
    INDEX STAT
    TABLE DEFRAG
    TABLE STAT
    TABLE CLUSTERED REBUILD
    TABLE CLUSTERED STAT
    Kropernic

  11. #11
    Membre expérimenté

    Homme Profil pro
    Administrateur de base de données
    Inscrit en
    août 2007
    Messages
    1 216
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 37
    Localisation : Suisse

    Informations professionnelles :
    Activité : Administrateur de base de données
    Secteur : Industrie Pharmaceutique

    Informations forums :
    Inscription : août 2007
    Messages : 1 216
    Points : 1 725
    Points
    1 725
    Par défaut
    Citation Envoyé par SQLpro Voir le message
    L'intérêt de mon algo avec la table de maintenance est de pouvoir savoir ce qui a été fait ou pas (c'est très utile en cas de problème ultérieur) mais aussi de voir les dérives... En gros l'index qui doit mettre 3 minutes à être reconstruit et qui subitement en met 20 !
    Les script du "Winner" ne permettent pas de suivre cela !
    Hello,

    Sans doute suite à une mise à jour des scripts... les commandes effectuées peuvent être logguées et permettent une tracabilité des actions effectuées.
    Ceci aussi bien pour les index que pour les DBCC.

Discussions similaires

  1. [INDEX] Maintenance sur les indexes
    Par guigui_cwoco dans le forum Oracle
    Réponses: 1
    Dernier message: 13/03/2007, 08h13
  2. Option COMPRESS pour les INDEX
    Par Wurlitzer dans le forum Oracle
    Réponses: 12
    Dernier message: 12/07/2006, 09h55
  3. petit conseil pour les index
    Par fpouget dans le forum Langage SQL
    Réponses: 11
    Dernier message: 10/12/2005, 04h39

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo