septembre
2010
Un article de zinzineti
L’idée est d’examiner l’utilisation des index non-cluster par l’optimiseur de requête.
Afin de mettre en évidence le choix du moteur de base données vis à vis des index non-cluster couvrants avec colonnes incluses ou non
–> Création de la table
IF OBJECT_ID('dbo.T_TEST') IS NOT NULL
DROP TABLE dbo.T_TEST
CREATE TABLE dbo.T_TEST
(
id int identity(1,1),
val varchar(10),
creation_date datetime
)
--> Chargement d'1 million de lignes dans la table
DECLARE @counter int;
SET @counter = 1;
WHILE @counter <= 1000000
BEGIN
INSERT INTO T_TEST(val,creation_date) VALUES(convert(varchar(10),(LEFT(convert(bigint,RAND()*10000000),6))),getdate());
SET @counter = @counter + 1
END;
–> Plaçons 1% de la colonne ‘val’ sur la valeur ‘0’ : objectif avoir une forte (haute) sélectivité pour la requête de test
UPDATE dbo.T_TEST SET val = '0' WHERE dbo.T_TEST.id <=10000
–> Activons les options IO afin de mesurer les pages logiques lues
SET STATISTICS IO ON
——————————————————
–>1. Exécutons la requête de test
——————————————————
FROM T_TEST
WHERE val = '0'
Résultat => TABLE SCAN, lectures logiques 4017
——————————————————
–> 2. Créons un index non-cluster sur la colonne val
——————————————————
-- Test
SELECT id,val,creation_date
FROM T_TEST
WHERE val = '0'
Résultat => TABLE SCAN , lectures logiques 4017
–Forçons maintenant l’optimiseur à utiliser l’index IXNC_val
FROM T_TEST WITH (INDEX(IXNC_val))
WHERE val = '0'
Résultat => INDEX SEEK, lectures logiques 10027
–Interpretation : ici on a un index seek mais le nombre de pages lues est double du nombre de pages dans le cas d’un
— TABLE SCAN. Donc forcer l’otimiseur à utiliser l’index IXNC_val n’est pas la meilleure solution
——————————————————
–> 3.1 Créons un index non-cluster sur les trois colonnes id,val,creation_date dans cet ordre
——————————————————
-- Test
SELECT id,val,creation_date
FROM T_TEST
WHERE val = '0'
Résultat => TABLE SCAN , lectures logiques 4017
–Forçons l’optimiseur à utiliser l’index IXNC_id_val_creationdate
FROM T_TEST WITH (INDEX(IXNC_id_val_creationdate))
WHERE val = '0'
Résultat => INDEX SCAN, lectures logiques 4479
–Interpretation : Dans ce cas aussi le nombre de pages lues est supérieur 4017, ce n’est donc pas le meilleur index
——————————————————
–> 3.2 Créons un index non-cluster sur les trois colonnes val,id,creation_date dans cet ordre
——————————————————
CREATE INDEX IXNC_val_id_creationdate ON T_TEST (val,id,creation_date)
— Test
FROM T_TEST WITH (INDEX(IXNC_val_id_creationdate))
WHERE val = '0'
Résultat => INDEX SEEK sur IXNC_val_id_creationdate lectures logiques 42
–Interpretation : l’ordre des colonnes est très importante, étant donné que la clause WHERE concerne la colonne val .
——————————————————
–> 3.3 Créons un index non-cluster sur les trois colonnes val,creation_date et id dans cet ordre
——————————————————
CREATE INDEX IXNC_val_creationdate_id ON T_TEST (val,creation_date,id)
— Test
FROM T_TEST WITH (INDEX(IXNC_val_creationdate_id))
WHERE val = '0'
Résultat => INDEX SEEK sur IXNC_val_creationdate_id lectures logiques 42
–Quel index l’optimiseur va choisir ?
FROM T_TEST
WHERE val = '0'
Résultat => INDEX SEEK sur IXNC_val_id_creationdate lectures logiques 42
–Interpretation : l’optimiseur choisit IXNC_val_id_creationdate au lieu de IXNC_val_creationdate_id. Pourquoi ?
——————————————————
–> 4.1 Créons un index non-cluster sur la colone val avec INCLUDE sur les colonnes respectives id et creation_date
——————————————————
-- Test
SELECT id,val,creation_date
FROM T_TEST WITH (INDEX (IXNC_val_INCLUDE_id_creationdate))
WHERE val = '0'
Résultat => INDEX SEEK sur IXNC_val_INCLUDE_id_creationdate, lectures logiques 42
——————————————————
–> 4.2 Créons un index non-cluster sur la colone val avec INCLUDE sur les colonnes respectives creation_date et id
——————————————————
-- Test
SELECT id,val,creation_date
FROM T_TEST WITH (INDEX (IXNC_val_INCLUDE_creationdate_id))
WHERE val = '0'
Résultat => INDEX SEEK, utilisation de l’index IXNC_val_INCLUDE_creationdate_id. lectures logiques 42
Finalement qu’est ce que l’optimiseur va choisir ?
FROM T_TEST
WHERE val = '0'
Résultat => INDEX SEEK sur IXNC_val_INCLUDE_id_creationdate lectures logiques 42
——————————————————
Question : Sur quoi l’otimiseur se base pour choisir l’index IXNC_val_INCLUDE_id_creationdate au lieu de IXNC_val_INCLUDE_creationdate_id ? ou IXNC_val_id_creationdate ? ou IXNC_val_creationdate_id ?
——————————————————
–Visualisons les statistiques des index
DBCC SHOW_STATISTICS (‘T_TEST’, IXNC_val_id_creationdate );
DBCC SHOW_STATISTICS (‘T_TEST’, IXNC_val_creationdate_id);
DBCC SHOW_STATISTICS (‘T_TEST’, IXNC_val_INCLUDE_id_creationdate);
DBCC SHOW_STATISTICS (‘T_TEST’, IXNC_val_INCLUDE_creationdate_id);
 |
Questions :
====================
Alors comment expliquer le choix de l’optimiseur ?
Qu’est ce qui permet à l’optimiseur de faire un choix parmi ces quatre possibilités ?
Réponse:
====================
J’ai posté ces questions sur le forum et j’ai eu les réponses des experts sur le sujet.
Dabord mikedavem a remarqué que pour les index couvrants à colonnes INCLUSES l’ordre de création des index influence le choix du moteur de base de données :
pour IXNC_val_INCLUDE_id_creationdate et IXNC_val_INCLUDE_creationdate_id l’optimiseur utilise l’index ayant l’ID le plus petit (l’index utilisé est donc l’index créé en premier).
Ensuite, elsuket , affirme que l’optimiseur choisit l’index avec INCLUDE parce que la clé de l’index est plus petite : le nombre de pages à lire étant plus faible, le parcours est plus rapide.
elsuket a donc reproduit le scénario chez lui et est arrivé à la même conclusion que mikedavem :
la quantité de données à ramener par l’index couvrant à colonnes INCLUSES est moins importante que pour l’index couvrant avec clé contenant toutes les colonnes impliquées dans la requête.
Ce qui nous conduit à tirer ces conclusions dans le cadre de cet exemple :
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.
Voici le lien vers la discussion sur ce sujet
Merci à tous 
————————————————————————
Auteur : Etienne ZINZINDOHOUE
————————————————————————