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 +