Index non-cluster : Choix de l'optimiseur ?

Bonjour tout le monde,

J'ai cette requête

Code:

---

1 2 3

SELECT id,val,creation_date FROM T_TEST WHERE val = '0'

---

basé sur la table

Code:

---

1 2 3 4 5 6

CREATE TABLE dbo.T_TEST ( id int identity(1,1), val varchar(10), creation_date datetime )

---

Et j'ai créé les quatres index suivants :

Code:

---

1 2 3 4

CREATE INDEX IXNC_val_id_creationdate ON T_TEST (val,id,creation_date) CREATE INDEX IXNC_val_creationdate_id ON T_TEST (val,creation_date,id) CREATE INDEX IXNC_val_INCLUDE_id_creationdate ON T_TEST (val) INCLUDE (id,creation_date) CREATE INDEX IXNC_val_INCLUDE_creationdate_id ON T_TEST (val) INCLUDE (creation_date,id)

---

Parmi ces quatre index l'optimiseur a choisi IXNC_val_INCLUDE_id_creationdate lors de l'exécution de la requête.

L'analyse des statistiques ne me permet pas d'expliquer le choix l'optimiseur.

Voici un écran des statistiques

Avez-vous une idée sur ce choix ?

Merci de m'éclairer .

Si je prends l'exemple des 2 index avec colonnes incluses que tu as créé, l'optimiseur peut prendre soit l'un soit l'autre car les 2 index ont le même coup à l'usage. Il n'y a même pas de différences notables au niveau de leur état physique. (via la vue sys.dm_db_index_physical_stats).

Cependant je me suis posé la question suivante :

Si l'optimiseur peut choisir soit l'un soit l'autre des index et qu'il choisit toujours le même, il se base peut être sur l'ID de l'index et apparemment cela se confirme chez moi.

Fais le test suivant :

Tu supprimes les index IXNC_val_INCLUDE_id_creationdate et IXNC_val_INCLUDE_creationdate_id

Tu recrées à nouveau les 2 index dans l'ordre suivant :
IXNC_val_INCLUDE_creationdate_id et ensuite IXNC_val_INCLUDE_id_creationdate

En principe l'optimiseur choisit l'index IXNC_val_INCLUDE_id_creationdate maintenant.

++

1. TEST 1
================
--Suppression des index

Code:

---

1 2 3

DROP INDEX T_TEST.IXNC_val_INCLUDE_id_creationdate DROP INDEX T_TEST.IXNC_val_INCLUDE_creationdate_id

---

--Création des index dans l'ordre suivant

Code:

---

1 2	CREATE INDEX IXNC_val_INCLUDE_creationdate_id ON T_TEST (val) INCLUDE (creation_date,id) CREATE INDEX IXNC_val_INCLUDE_id_creationdate ON T_TEST (val) INCLUDE (id,creation_date)

---

Citation:

---

Résultat de la requête
Utilisation de l'index "IXNC_val_INCLUDE_creationdate_id"

---

--Obtenir les stats_id et index_id

Code:

---

1 2 3 4

SELECT s.name,s.stats_id,i.index_id FROM sys.stats s INNER JOIN sys.indexes i ON s.object_id = i.object_id AND s.stats_id = i.index_id WHERE s.object_id = OBJECT_ID('dbo.T_TEST') AND s.name IN('IXNC_val_INCLUDE_creationdate_id', 'IXNC_val_INCLUDE_id_creationdate');

---

--Affichage

Citation:

---

name stats_id index_id
IXNC_val_INCLUDE_creationdate_id 6 6
IXNC_val_INCLUDE_id_creationdate 7 7

---

2. TEST 2
================
--Suppression des index

Code:

---

1 2 3

DROP INDEX T_TEST.IXNC_val_INCLUDE_id_creationdate DROP INDEX T_TEST.IXNC_val_INCLUDE_creationdate_id

---

--Création des index dans l'ordre suivant

Code:

---

1 2	CREATE INDEX IXNC_val_INCLUDE_id_creationdate ON T_TEST (val) INCLUDE (id,creation_date) CREATE INDEX IXNC_val_INCLUDE_creationdate_id ON T_TEST (val) INCLUDE (creation_date,id)

---

Citation:

---

Résultat de la requête
Utilisation de l'index "IXNC_val_INCLUDE_id_creationdate"

---

--Obtenir les stats_id et index_id

Code:

---

1 2 3 4

SELECT s.name,s.stats_id,i.index_id FROM sys.stats s INNER JOIN sys.indexes i ON s.object_id = i.object_id AND s.stats_id = i.index_id WHERE s.object_id = OBJECT_ID('dbo.T_TEST') AND s.name IN('IXNC_val_INCLUDE_creationdate_id', 'IXNC_val_INCLUDE_id_creationdate');

---

-- Affichage

Citation:

---

name stats_id index_id
IXNC_val_INCLUDE_id_creationdate 6 6
IXNC_val_INCLUDE_creationdate_id 7 7

---

Comme tu le dis, on est tenté de dire que tout est lié à l'ID. On peut aussi dire simplement que l'index l'index qui est créé premièrement est choisit par l'optimiseur.
First Create First Use ?

l'index étant couvrant, c'est moins lourd que de lire la table !

A +

Citation:

---

Envoyé par SQLpro

l'index étant couvrant, c'est moins lourd que de lire la table !

A +

---

Oui, mais jusque là on est arrivé à trouver une justification au choix de l'optimisateur pour ce qui concerne les index couvrants INDEX IXNC_val_INCLUDE_creationdate_id
et IXNC_val_INCLUDE_id_creationdate.

Mais pourquoi l'optimiseur préfère l'un des index couvrants ci-dessus au détriment des index couvrants suivants:
IXNC_val_id_creationdate
IXNC_val_creationdate_id

Code:

---

1 2	CREATE INDEX IXNC_val_id_creationdate ON T_TEST (val,id,creation_date) CREATE INDEX IXNC_val_creationdate_id ON T_TEST (val,creation_date,id)

---

Merci de m'éclairer

Bonjour,

Intuitivement, je dirai que c'est parce que la clé de l'index est plus petite : le nombre de pages à lire étant plus faible, le parcours est plus rapide.

Je vais reproduire votre cas et regarder le nombre de pages / niveaux de chaque index ;)

@++ ;)

Citation:

---

Envoyé par elsuket

Bonjour,

Intuitivement, je dirai que c'est parce que la clé de l'index est plus petite : le nombre de pages à lire étant plus faible, le parcours est plus rapide.

Je vais reproduire votre cas et regarder le nombre de pages / niveaux de chaque index ;)
@++ ;)

---

Le détail des tests est sur mon blog ici
Pour les 4 index suivants on a les mêmes nombre de pages logiques : 42 et des index seek pour chaque index:

Code:

---

1 2 3 4

CREATE INDEX IXNC_val_id_creationdate ON T_TEST (val,id,creation_date) CREATE INDEX IXNC_val_creationdate_id ON T_TEST (val,creation_date,id) CREATE INDEX IXNC_val_INCLUDE_id_creationdate ON T_TEST (val) INCLUDE (id,creation_date) CREATE INDEX IXNC_val_INCLUDE_creationdate_id ON T_TEST (val) INCLUDE (creation_date,id)

---

Je viens de reproduire le cas, et voici les statistique que j'obtiens :

http://img541.imageshack.us/img541/7756/indexstats.png

Les index qui ont été créés avec l'INCLUDE compent une page de plus mais ont une fragmentation plus faible, tous autant de niveaux, et presque tous le même nombre de pages...

@++ ;)

Les colonnes d'index sont trop petites pour voir une réelle différence au niveau des pages.

Cependant il faut savoir que pour un index avec colonnes incluses, seul le niveau feuille possède l'ensemble des colonnes défini dans l'index au niveau stockage. La racine et les niveaux intermédaires n'ont que la clé d'index sans les colonnes incluses.

Le nombre de pages à lire est effectivement le même pour les 2 types d'index (bien que dans mon cas j'ai 3 lectures logiques (ce qui correspond aux 3 niveaux de l'index de l'index) avec la requête suivante :)

Code:

---

1 2 3 4 5 6 7 8 9 10

SET STATISTICS IO ON; SELECT id,val, creation_date FROM T_TEST WITH (INDEX (IXNC_val_INCLUDE_id_creationdate)) WHERE val = '0'   SELECT id,val, creation_date FROM T_TEST WITH (INDEX(IXNC_val_id_creationdate)) WHERE val = '0'   SET STATISTICS IO OFF;

---

La quantité de données à ramener est mois importante pour l'index avec colonnes incluses : (en me basant sur la taille moyenne d'une ligne de données de l'index pour chaque niveau)

IXNC_val_INCLUDE_id_creationdate : 28 + 27,99 + 33,98 = 90 (arrondi)
IXNC_val_id_creationdate : 40 + 39,98 + 33,98 = 114 (arrondi)

++

Citation:

---

Envoyé par elsuket

Je viens de reproduire le cas, et voici les statistique que j'obtiens :
http://img6.glowfoto.com/images/2010...248346388L.png

---

On ne voit pas les statistiques, l'image n'est pas visible ;)

Citation:

---

Envoyé par elsuket

Les index qui ont été créés avec l'INCLUDE compent une page de plus mais ont une fragmentation plus faible, tous autant de niveaux, et presque tous le même nombre de pages...
@++ ;)

---

Chez moi j'ai le même nombre de pages pour ces 4 index : 4453 pour la valeur de la colonne page_count et une moyenne du pourcentage de fragmentation identique (colonne avg_fragmentation_in_percent) = 0,022 %

Citation:

---

Envoyé par mikedavem

Les colonnes d'index sont trop petites pour voir une réelle différence au niveau des pages.

Cependant il faut savoir que pour un index avec colonnes incluses, seul le niveau feuille possède l'ensemble des colonnes défini dans l'index au niveau stockage. La racine et les niveaux intermédaires n'ont que la clé d'index sans les colonnes incluses.

La quantité de données à ramener est mois importante pour l'index avec colonnes incluses : (en me basant sur la taille moyenne d'une ligne de données de l'index pour chaque niveau)

IXNC_val_INCLUDE_id_creationdate : 28 + 27,99 + 33,98 = 90 (arrondi)
IXNC_val_id_creationdate : 40 + 39,98 + 33,98 = 114 (arrondi)

++

---

S'il y a une différence à ce niveau alors ceci explique celà ;)
Il me semble que dans la plupart des cas, il est plus bénéfique de créer un index couvrant avec INCLUDE qu'un index couvrant avec une clé contenant toutes les colonnes !?

Désolé pour les statistiques, j'ai mis l'image ailleurs ;)

Mikedavem : c'est bien ce que je pensais ;)

@++ ;)

Au vue de tout ce qui précède, puis-je tirer quelques conclusions ? ;)

1. un index couvrant rend une requête plus performante qu'un index non couvrant basé UNIQUEMENT sur les colonnes de la clause WHERE

2. un index couvrant avec colonnes INCLUSES (INCLUDE) est plus performant qu'un index couvrant à colonnes NON INCLUSES

3. Pour un index couvrant à colonnes NON INCLUSES, l'ordre d'enchainement des colonnes dans la clé de l'index est très important !

4. S'il existe des index couvrants à colonne INCLUSE (INCLUDE) en doublon (en triplon, quadruplon,...N-ultiplon), l'optimiseur utilise
l'index ayant le plus petit ID (index_id) dont la valeur est égale à stats_id.
Exemple

Code:

---

1 2 3 4 5

SELECT s.name,s.stats_id,i.index_id FROM sys.stats s INNER JOIN sys.indexes i ON s.object_id = i.object_id AND s.stats_id = i.index_id WHERE s.object_id = OBJECT_ID('dbo.T_TEST') AND s.name IN('IXNC_val_INCLUDE_creationdate_id', 'IXNC_val_INCLUDE_id_creationdate');

---

Bonjour

Je lisais votre discussion qui contient des choses semble-t-il tout à fait intéressantes mais ..... je ne suis pas sur d'avoir compris ce que signifie un "index couvrant" ? (je sais bien ce qu'est un index avec colonnes incluses - donc des colonnes non clefs pour l'index -, mais je ne vois pas bien la nuance avec un index "couvrant").

Un index couvrant ne veut pas dire index avec colonnes incluses.

Une définition peut être celle-ci :

Un index couvrant est un index non cluster qui possède les données nécessaires pour répondre à une requête sans avoir besoin d'interroger un index cluster ou une table HEAP.

Par exemple :

Code:

---

1 2 3

SELECT cola, colb, colc FROM maTable WHERE cola = 'valeur'

---

Un index couvrant peut être défini comme ceci:

Code:

---

1 2 3

CREATE INDEX idx1 ON maTable (cola, colb, colc)

---

ou

Code:

---

1 2 3

CREATE INDEX idx2 ON maTable (cola) INCLUDE (colb, colc)

---

++

Citation:

---

Il me semble que dans la plupart des cas, il est plus bénéfique de créer un index couvrant avec INCLUDE qu'un index couvrant avec une clé contenant toutes les colonnes !?

---

Dans la plupart des cas oui.

Il suffit simplement de suivre cette règle qui stipule que toutes les colonnes se trouvant dans un prédicat avec la clause WHERE doivent faire parti de la clé. Le reste des colonnes peut faire parti de la clause INCLUDE.

++

Citation:

---

Envoyé par mikedavem

Dans la plupart des cas oui.

Il suffit simplement de suivre cette règle qui stipule que toutes les colonnes se trouvant dans un prédicat avec la clause WHERE doivent faire parti de la clé. Le reste des colonnes peut faire parti de la clause INCLUDE.

++

---

Je suis complètement d'accord. :ccool:

Par ailleurs je constate que le moteur de base de données décide de ne pas créer de statistiques sur la colonne creation_date de la table T_TEST ! :roll:

Comment justifier ce choix ?
Dans quel(s) cas le moteur ferme les yeux sur les statistiques d'une colonne ?

Pour mettre en évidence l’absence de statistiques sur la colonne creation_date je fais :

Code:

---

DBCC SHOW_STATISTICS ('dbo.T_TEST',creation_date)

---

et SSMS me renvoie ce message d'erreur 2767:

Citation:

---

Msg 2767, Level 16, State 1, Line 1
Impossible de localiser les statistiques 'creation_date' dans le catalogue système.
Exécution de DBCC terminée. Si DBCC vous a adressé des messages d'erreur, contactez l'administrateur système.

---

Merci de m'éclairer

Voyez la réponse que je vous ai faite ici

@++ ;)

J'ai fais une petite synthèse sur le sujet

Merci à tous ;)