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

MS SQL Server Discussion :

Moniteur d'activité, plan d'exécutions et index proposés


Sujet :

MS SQL Server

Vue hybride

Message précédent Message précédent   Message suivant Message suivant
  1. #1
    Membre Expert
    Profil pro
    Inscrit en
    Décembre 2003
    Messages
    1 616
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Décembre 2003
    Messages : 1 616
    Par défaut Moniteur d'activité, plan d'exécutions et index proposés
    Bonjour

    Travaillant à différents niveaux de mon appli pour son optimisation, j'ai pris pour habitude de jeter un oeil au moniteur d'activité fourni par SQL SVR 2008.

    On y retrouve les requêtes couteuses récentes.

    Certaines de celles-ci lorsque on affiche le plan d'exécution propose un "index absent", la syntaxe nécessaire à la création de cet index, ainsi que l'impact en terme de performance.

    Peut on se contenter de ce qui est proposé, ou devrait on y consacrer un travail d'analyse plus approfondi ?
    Est ce fiable ?

    Je dois dire que les requêtes concernées ne me semblent pas particulièrement lentes, mais ces gains ne seraient certainement pas inutiles.

    Si SQL fait un usage réel de ces index par la suite, j'imagine qu'on en trouve trace dans les plans d'exécution ?

    Merci d'avance

  2. #2
    Membre Expert Avatar de iberserk
    Homme Profil pro
    Architecte de base de données
    Inscrit en
    Novembre 2004
    Messages
    1 795
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 43
    Localisation : France, Gironde (Aquitaine)

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

    Informations forums :
    Inscription : Novembre 2004
    Messages : 1 795
    Par défaut
    L'index est en général cohérent (attention toutefois SQL SERVER est très généreux avec les INCLUDE)
    Exemple:
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    SELECT * 
    FROM T 
    WHERE COL1='TOTO'
    SQL SERVER vous proposera surement un index sur COL1 avec en INCLUDE l'intégralité des colonnes de la table (pour satisfaire le SEELCT *)...

    Il convient donc de garder un oeil critique...

    Notez que vous pouvez retrouver la liste des indexes estimés manquants avec les DMV (sys.dm_db_missing_index_...).

    Si SQL fait un usage réel de ces index par la suite, j'imagine qu'on en trouve trace dans les plans d'exécution ?
    Oui vous pouvez aussi voir les statistiques d'utilisation de tous vos indexes toujours avec les DMV (sys.dm_index_usage_stats)

  3. #3
    Expert confirmé
    Avatar de mikedavem
    Homme Profil pro
    Administrateur de base de données
    Inscrit en
    Août 2005
    Messages
    5 450
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 46
    Localisation : France, Ain (Rhône Alpes)

    Informations professionnelles :
    Activité : Administrateur de base de données
    Secteur : Distribution

    Informations forums :
    Inscription : Août 2005
    Messages : 5 450
    Par défaut
    Peut on se contenter de ce qui est proposé, ou devrait on y consacrer un travail d'analyse plus approfondi ?
    Est ce fiable ?
    Il faut juste savoir que lorsque SQL Server vous propose la création d'un index il se base sur la requête à l'origine de cette proposition. Donc la solution est en générale viable pour la requête seule (a quelques exceptions près mais dans ton cas c'est le cas).

    Maintenant SQL Server ne tient pas compte de l'impact que pourrait avoir cet index sur la charge globale et des requêtes qui utiliseraient cette table (pour les mises à jour par exemple : INSERT, DELTE, UPDATE). Pour pouvoir vérifier que ton index n'impacte pas les autres requêtes il faudrait pouvoir utiliser le DTA par exemple.

    ++

  4. #4
    Membre Expert
    Profil pro
    Inscrit en
    Décembre 2003
    Messages
    1 616
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Décembre 2003
    Messages : 1 616
    Par défaut
    Merci beaucoup pour vos réponses.

    Sur les tutos de Frédéric Brouard (Merci à lui pour tout ce travail si précieux ), j'ai vu la référence à ces vues système : http://sqlpro.developpez.com/cours/quoi-indexer/#LXII

    et la table de stats que tu évoques iberserk s'appelle plus précisément dm_db_index_usage_stats. On y trouve des choses intéressantes visiblement, il faut que je me documente un peu plus sur celle-ci. je n'ai pas encore retrouvé mes petits sur les index existants (quel ID correspond à quel index)

    Voici ce que je ressors de dm_db_missing_index_details

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    index_handle|database_id|object_id|equality_columns|inequality_columns|included_columns|statement
    7|35|405576483|[NMC_NIVEAU], [ID_USER]|[NMC_BG], [NMC_BD]|[NMC_ID], [NMC_LIBELLE]|[db_name].[dbo].[FOLDER_NOMENCLATURE_NMC]
    1|35|405576483|NULL|[NMC_BG], [NMC_BD]|[NMC_ID], [NMC_LIBELLE], [NMC_NIVEAU]|[db_name].[dbo].[FOLDER_NOMENCLATURE_NMC]
    28|35|405576483|[NMC_NIVEAU], [ID_USER]|NULL|[NMC_ID]|[db_name].[dbo].[FOLDER_NOMENCLATURE_NMC]
    5|35|405576483|[NMC_NIVEAU], [ID_USER]|NULL|[NMC_ID], [NMC_LIBELLE], [NMC_BG], [NMC_BD]|[db_name].[dbo].[FOLDER_NOMENCLATURE_NMC]
    les séparateurs sont des |

    J'ai en tout 6 index manquants, qui concernent mes devs actuels en fait. Il y en aurait peut être et même surement plus en production.


    Ce qui m'interroge est au dessus, 4 de ces index concernent la même table.

    la table est inspiré de l'article de Frédéric Brouard sur la gestion des arborescences : http://sqlpro.developpez.com/cours/arborescence/#L3

    J'ai déja 3 index séparés sur NMC_BD, NMC_BG, ID_USER. il faut que je vérifie l'usage avec ces stats, une fois que j'aurais fait le lien.

    Ici on me propose 4 index de plus sur la même table, qui me semblent très proches.

    J'aurai envie d'écrire quelque chose qui puisse répondre au moins au 4 manquants, sur un seul index :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    CREATE NONCLUSTERED INDEX [<Name of Missing Index>]
    ON [dbo].[FOLDER_NOMENCLATURE_NMC]
     ([NMC_NIVEAU],[ID_USER],[NMC_BG],[NMC_BD])
    INCLUDE ([NMC_ID],[NMC_LIBELLE])
    créer ces 4 index de manière brute me semble inutile. Et vous ?

    L'instruction ci dessus me semble couvrir les cas énoncés en défaut par l'analyseur et noté dans cette table.
    Et me semble "couvrir " les recherches comme l'évoque Frédéric dans son tutoriel (la notion d'étoile)

    Qu'en pensez-vous ?

  5. #5
    Rédacteur

    Avatar de SQLpro
    Homme Profil pro
    Expert bases de données / SQL / MS SQL Server / Postgresql
    Inscrit en
    Mai 2002
    Messages
    21 997
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

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

    Informations forums :
    Inscription : Mai 2002
    Messages : 21 997
    Billets dans le blog
    6
    Par défaut
    Vos données d'index manquants :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
                   eq_col                  ineq_col           inc_col                                     table 
     7|35|405576483|[NMC_NIVEAU], [ID_USER]|[NMC_BG], [NMC_BD]|[NMC_ID], [NMC_LIBELLE]                    |[db_name].[dbo].[FOLDER_NOMENCLATURE_NMC]
     1|35|405576483|NULL                   |[NMC_BG], [NMC_BD]|[NMC_ID], [NMC_LIBELLE], [NMC_NIVEAU]      |[db_name].[dbo].[FOLDER_NOMENCLATURE_NMC]
    28|35|405576483|[NMC_NIVEAU], [ID_USER]|NULL              |[NMC_ID]                                   |[db_name].[dbo].[FOLDER_NOMENCLATURE_NMC]
     5|35|405576483|[NMC_NIVEAU], [ID_USER]|NULL              |[NMC_ID], [NMC_LIBELLE], [NMC_BG], [NMC_BD]|[db_name].[dbo].[FOLDER_NOMENCLATURE_NMC]
    Seul 2 index sont à poser :

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    CREATE INDEX X??? 
        ON [db_name].[dbo].[FOLDER_NOMENCLATURE_NMC]
                ([NMC_NIVEAU], [ID_USER], [NMC_BG], [NMC_BD])  --> couvre l'index 1, 3, 4 au niveau eq_col + ineq_col
        INCLUDE ([NMC_ID], [NMC_LIBELLE]);                     --> couvre l'index 1, 3 et combiné avec la clause ON, 4
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    CREATE INDEX X??? 
        ON [db_name].[dbo].[FOLDER_NOMENCLATURE_NMC]
                ([NMC_BG], [NMC_BD])                           --> couvre l'index 2 au niveau ineq_col
        INCLUDE ([NMC_ID], [NMC_LIBELLE], [NMC_NIVEAU])        --> couvre l'index 2
    En fait il faut mutualiser la création des index...

    venez à mon cours d'optimisation de SQL Server chez orsys : http://www.orsys.fr/formation-sql-se...ion.asp?sdid=0

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

  6. #6
    Membre Expert
    Profil pro
    Inscrit en
    Décembre 2003
    Messages
    1 616
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Décembre 2003
    Messages : 1 616
    Par défaut
    Bonjour Frédéric et merci

    depuis le temps que j'apprends de tes cours et que je bénéficie de tes conseils, ici ou auparavant sur le usenet, franchement ce serait un vrai plaisir de participer à ce genre de session de formation.

    Bon mais c'est pas d'actu, je garde le lien malgré tout.

    Mine de rien, ma question, c'est du coupage de cheveux en 4. Les requêtes qui amènent l'optimiseur à me signaler ces index absents servent des scripts ASP (hé oui...) répondant à des requêtes AJAX. Le tout est extrêmement rapide à mon sens.

    Les requêtes concernées par ces index ont des durées moyennes de 5-6ms pour la plus longue, d'après le moniteur d'activité. Les temps de chargement de page sont toujours inférieurs à 2s, transfert par ADSL compris.

    C'est surtout l'occasion de me pencher sur le sujet des index, des optimisations de requêtes (Très bon article là dessus aussi de ta part), et de bien comprendre comment tout cela fonctionne.


    Merci encore

  7. #7
    Rédacteur

    Avatar de SQLpro
    Homme Profil pro
    Expert bases de données / SQL / MS SQL Server / Postgresql
    Inscrit en
    Mai 2002
    Messages
    21 997
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

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

    Informations forums :
    Inscription : Mai 2002
    Messages : 21 997
    Billets dans le blog
    6
    Par défaut
    L'optimisation par les index n'a pas pour seul but d'accélérer les select... C'est aussi et c'est encore plus important, accélérer les mises à jour. En effet, lors des mises à jour (UPDATE) une table sans index ne peut qu'être verrouillé entièrement, diminuant ainsi de manière dramatique la concurrence...
    1) par l'étendue du verrouillage
    2) par la durée liée au parcours de l'étendue

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

+ Répondre à la discussion
Cette discussion est résolue.

Discussions similaires

  1. Réponses: 11
    Dernier message: 28/04/2008, 16h29
  2. Plan d'exécution pas logique
    Par pat29 dans le forum Administration
    Réponses: 6
    Dernier message: 07/03/2008, 14h37
  3. Réponses: 12
    Dernier message: 22/06/2006, 10h26
  4. Plan d' exécution
    Par rod59 dans le forum Décisions SGBD
    Réponses: 2
    Dernier message: 15/06/2006, 21h50
  5. Comparer des plan d'exécution
    Par sygale dans le forum Oracle
    Réponses: 7
    Dernier message: 06/04/2006, 17h58

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