Discussion :

[fredoche]

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

[iberserk]

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)

[mikedavem]

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.

++

[fredoche]

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 ?

[SQLpro]

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 +

[fredoche]

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

[SQLpro]

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 +

[mikedavem]

En fait il faut mutualiser la création des index...

Chose que les outils SQL Server ne savent pas faire actuellement

++

[elsuket]

Bonjour,

D'autre part les suggestions d'index suggèrent très souvent d'ajouter les colonnes de la clé de l'index cluster de la table dans la clause INCLUDE, ce qui est absurde, puisqu'elle est déjà au niveau feuille de tout index non-cluster de la même table.

Voici un billet que j'ai écrit il y a quelques temps pour SQL Server 2005, et qui est toujours valable pour SQL Server 2008.

@++

[SQLpro]

Bonjour,

D'autre part les suggestions d'index suggèrent très souvent d'ajouter les colonnes de la clé de l'index cluster de la table dans la clause INCLUDE, ce qui est absurde, puisqu'elle est déjà au niveau feuille de tout index non-cluster de la même table.

Voici un billet que j'ai écrit il y a quelques temps pour SQL Server 2005, et qui est toujours valable pour SQL Server 2008.

@++

La suggestion SQL Server n'est pas totalement dénuée de bon sens. En effet, rien ne dit que cet clef d'index clustered restera toujours comme à l'origine. D'autres parts j'ai vu des requêtes ne pas fonctionner sans !!!

A +

[fredoche]

Bonjour à vous tous

merci d'avoir continué à réagir et échanger sur ce sujet

La création des 2 index cités plus haut par Frédéric ont été immédiatement suivi de résultats:
- au niveau appli, firebug montre des gains de l'ordre de 10 à 20 % sur les requêtes AJAX courantes (visualisation de l'arborescence simple sur la base des droits). Sur les requêtes plus complexes mettant en oeuvre les droits de l'utilisateur de manière fine, les gains sont de l'ordre de 50 %. On passe en gros de 800ms à 400 ms sur les requêtes AJAX les plus longues, transfert ADSL compris.

Les requêtes sont plus complexes que ce que peut laisser supposer l'article de SQLpro sur les arborescences. Le MCD inclue des notions d'utilisateurs, de groupes d'utilisateurs, de droits d'agir par groupe (up/down/delete/copy/move..) sur les répertoires...

- au niveau de la table [sys].[dm_db_index_usage_stats], l'usage de ces index apparait dès leur création, et croit de manière très rapide sur l'ensemble des opérations impliquées par les scripts de l'appli.

- dans la table [dm_db_missing_index_details], les 4 lignes cités plus haut disparaissent. Les index ne sont plus manquants.

Bref ça marche bien

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 +

Décidément Frédéric tu as beaucoup d'à propos

Alors dans cette vue [sys].[dm_db_index_usage_stats], on a 4 colonnes dédiées aux "users": user_seeks, user_scans, users_lookups et user_updates


Pourrais-tu, pourriez-vous me confirmer que cette dernière colonne user_updates reflète l'usage de l'index lors des opérations d'update sur la table concernée ?
Ou bien cela montre il le nombre de mises à jour sur ces index durant les opérations d'updates ?
Selon la colonne user_updates, l'usage de ces index est flagrant durant les opérations d'update, il suit de très près l'usage de l'index PK, et devance l'usage des index dédiés à NMC_BG et NMC_BD. J'ai des opérations d'update qui concernent tous les tuples de la table, et dans ce cas les 5 index semblent impactés.

Là où je ne suis pas clair, c'est
"est-ce un outil au service de l'update ?"
ou bien
"les updates de la table impliquent en conséquence des updates en temps réel des index, ce que reflète user_updates"

Enfin je vais supprimer l'index sur ID_USER, probablement inutile d'après les stats.

[SQLpro]

Alors dans cette vue [sys].[dm_db_index_usage_stats], on a 4 colonnes dédiées aux "users": user_seeks, user_scans, users_lookups et user_updates

Pourrais-tu, pourriez-vous me confirmer que cette dernière colonne user_updates reflète l'usage de l'index lors des opérations d'update sur la table concernée ?
Ou bien cela montre il le nombre de mises à jour sur ces index durant les opérations d'updates ?
Selon la colonne user_updates, l'usage de ces index est flagrant durant les opérations d'update, il suit de très près l'usage de l'index PK, et devance l'usage des index dédiés à NMC_BG et NMC_BD. J'ai des opérations d'update qui concernent tous les tuples de la table, et dans ce cas les 5 index semblent impactés.

Là où je ne suis pas clair, c'est
"est-ce un outil au service de l'update ?"
ou bien
"les updates de la table impliquent en conséquence des updates en temps réel des index, ce que reflète user_updates"

Enfin je vais supprimer l'index sur ID_USER, probablement inutile d'après les stats.

user_update montre les mises à jour effectuées DANS l'index et non pas dans la table à moins que cet index soit l'index clustered.

Les index sont mis à jour de manière synchrone dans la transaction. Ils sont donc verrouillés le temps des lectures comme des mises à jour.

mais la structure du verrou n'est pas la même. C'est pourquoi on parle de

• "lock" => verrou de table => consistance LOGIQUE
• "lock" => verrou d'index => consistance PHYSIQUE

A +

[elsuket]

La suggestion SQL Server n'est pas totalement dénuée de bon sens. En effet, rien ne dit que cet clef d'index clustered restera toujours comme à l'origine. D'autres parts j'ai vu des requêtes ne pas fonctionner sans !!!

Je suis d'accord techniquement; en revanche quand on change la définition d'un cluster, c'est quelque chose de tout à fait prévu : sa redéfinition sans la suppression des index non-cluster, s'il y en a bien sûr, entraîne un volume transactionnel important.
J'ai aussi vu des requêtes fonctionner sans, mais je suis sûr que tu es d'accord que la plupart du temps, ce n'est pas requis.
Pour moi, ça reste une façon de faire gonfler l'index inutilement.

@++