Discussion :

[zinzineti]

Bonjour tout le monde,

J'ai cette requête

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
SELECT id,val,creation_date 
FROM T_TEST  
WHERE val = '0'

basé sur la table

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 .

[mikedavem]

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.

++

[zinzineti]

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

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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)

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

--Obtenir les stats_id et index_id

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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)

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

--Obtenir les stats_id et index_id

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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

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 ?

[SQLpro]

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

A +

[zinzineti]

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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

[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

@++