Discussion :

[zinzineti]

Bonjour,
J'ai un problème d'interprétation sur la variabilité de la séléctivité de l'optimiseur pour un index Non Cluster.
Autrement dit à partir de quel pourcentage du nombre total de lignes d'une table, l'optimiseur préfère un TABLE SCAN à la place d'un INDEX SEEK pour une requête SARGable ?

Voici ma démarche :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
--Création de la table
SET NOCOUNT ON 
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; 
 
--Création d'index sur la colonne val
CREATE INDEX IX_VAL ON dbo.T_TEST(val)

La requête de test :

Code :

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

SELECT * FROM dbo.T_TEST WHERE val = '0'

-- Requête pour imposer le % de lignes renvoyées par la requête :

# Pour imposer 1% du nombre total de lignes renvoyées par la requête de test :

Code :

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

UPDATE dbo.T_TEST SET val = '0' WHERE dbo.T_TEST.id <=10000

# Pour imposer 2% du nombre total de lignes renvoyées par la requête de test :

Code :

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

UPDATE dbo.T_TEST SET val = '0' WHERE dbo.T_TEST.id <=20000

Et ainsi de suite ....

Voici les résultats obtenus:


Pour SQL SERVER 2008 R2
=========================

--> 0% -7% ==> INDEX SEEK

--> 8% -100% ==> TABLE SCAN

Pour SQL SERVER 2005 SP3
=========================

--> 0% -7% ==> INDEX SEEK

--> 8% -100% ==> TABLE SCAN

Généralement la limite de sélectivité d'un index non cluster est entre 0% - 5% du nombre du nombre total de lignes de la table !?

Question :
Comment expliquer la différence observée (0-7% au lieu de 0-5%) dans mon exemple ?


Merci de m'éclairer

[dbaffaleuf]

Ce n'est pas un pourcentage, c'est un optimiseur basé sur les coûts. Si le coût IO + CPU de lire toute la table est inférieur au coût de lire l'index, alors il fera un table scan. Tu ne peux pas faire des généralités avec l'optimiseur, chaque requête est différente et sera traitée différemment. Je comprends que tu essayes de trouver des repères mais à mon avis c'est vain. Il vaut mieux essayer de comprendre comment il fonctionne, comment fonctionnent les stats, comment fonctionnent les phases d'optimisation lors de la compilation, etc... Il y a trop de paramètres en jeu pour en tirer des conclusions aussi simples que "à partir de tel pourcentage il fera un table scan...."

[SQLpro]

Le cout étant calculé par rapport à la volumétrie estimée (en pages) et à l'opération demandé.
Par exemple un "=" sur du CHAR est moins couteux qu'un "LIKE 'toto%' " sur du VARCHAR.

A +

[dbaffaleuf]

Si tu recherches des éléments fixes dans le comportement de l'optimiseur, il existe des heuristiques pessimistes utilisées lors de la phase d'optimisation, c'est peut être les seules choses qui sont fixes.

Par exemple il considère qu'au moment de la compilation le buffer pool est vide et que toutes les lectures de pages seront des lectures physiques, il ne pondère pas par avance lectures logiques / lectures physiques. Un autre exemple est qu'il considère que les lectures aléatoires seront uniformes sur disque. S'il doit lire 100 pages, alors il considère qu'il devra faire 100 I/Os, ce qui est faux dans la plupart des cas car ReadFileScatter() lit en général autour de 64K de données en un seul appel.

[zinzineti]

Si le coût IO + CPU de lire toute la table est inférieur au coût de lire l'index, alors il fera un table scan.

Je suis d'accord.
Et quand le coût de la table = coût de l'index, que fait l'optimiseur ?

Ce n'est pas un pourcentage, c'est un optimiseur basé sur les coûts.

Je ne suis pas d'accord.
Parce que dire "Si le coût IO + CPU de lire toute la table est inférieur au coût de lire l'index, alors il fera un table scan" revient à comparer la quantité de pages pour la totalité de la table d'une part et celle de l'index d'autre part.
Et cette comparaison met en évidence un delta (entre nombre de pages pour toute la table et l'index) et par conséquent permet d'introduire la notion de pourcentage (%).

Il y a trop de paramètres en jeu pour en tirer des conclusions aussi simples que "à partir de tel pourcentage il fera un table scan...."

C'est justement parce qu'il y a trop de paramètres que j'essaye de prendre un exemple simple, dans un contexte plus simple pour identifier les corrélations entre % de données (nombre de pages) et utilisation d'index.

Il vaut mieux essayer de comprendre comment il fonctionne, comment fonctionnent les stats, comment fonctionnent les phases d'optimisation lors de la compilation, etc...

Merci pour ces pistes, si tu as de la doc ou des liens, je suis preneur

A+

[dbaffaleuf]

Et cette comparaison met en évidence un delta (entre nombre de pages pour toute la table et l'index) et par conséquent permet d'introduire la notion de pourcentage (%).

Oui mais tu cherches à mettre en évidence une loi générale alors qu'il n'y en a pas. Si le coût de lire la table est inférieur, il lit la table, sinon il lit l'index, point. Encore une fois je ne vois pas où tu veux en venir.

C'est justement parce qu'il y a trop de paramètres que j'essaye de prendre un exemple simple, dans un contexte plus simple pour identifier les corrélations entre % de données (nombre de pages) et utilisation d'index.

Sors ton exemple de son contexte et il ne vaut plus rien. La corrélation que tu vas trouver dans un cas ne sera plus valable dans un autre.

Merci pour ces pistes, si tu as de la doc ou des liens, je suis preneur

Le chapitre 8 de SQL Server 2008 Internals.

A+

[mikedavem]

Bonjour,

Parce que dire "Si le coût IO + CPU de lire toute la table est inférieur au coût de lire l'index, alors il fera un table scan" revient à comparer la quantité de pages pour la totalité de la table d'une part et celle de l'index d'autre part.

C'est plus compliqué que cela comme le dit dbaffaleuf ... il y a d'autres paramètres comme la fragmentation des index par exemple ...

++

[zinzineti]

Oui mais tu cherches à mettre en évidence une loi générale alors qu'il n'y en a pas. Si le coût de lire la table est inférieur, il lit la table, sinon il lit l'index, point.

En recherchant un peu dans le book online (SQL 2008 R2) je suis tombé sur ceci :

Lors d'une analyse de table, l'optimiseur de requête lit toutes les lignes de la table et extrait celles qui répondent aux critères de la requête.
Une analyse de table génère de nombreuses opérations d'E/S disque et peut consommer une grande quantité de ressources.
Toutefois, l'analyse d'une table peut être la méthode la plus efficace si par exemple le jeu des résultats de la requête représente un pourcentage élevé de lignes de la table.

Pour accéder à cette page tu peux faire :

Conception et implémentation du stockage structuré (moteur de base de données) > Index > Présentation des index >

Puis aller au paragraphe :Utilisation des index par l'optimiseur de requête.

Ceci dit le book online ne donne pas de fourchette de pourcentage.

Par ailleurs, il existe des personnes qui ont creusé un peu le sujet, toujours à propos de la relation probable entre % de lignes renvoyées et l'utilisation d'index non cluster.
Voici ce que j'ai trouvé :

http://www.sqlskills.com/BLOGS/KIMBE...d-Indexes.aspx

Sur la page, la partie qui nous intéresse est intitulée : The Tipping Point Query Answers.

Je n'ai pas tout lu, je prendrai le temps de le lire

A+

[Ptit_Dje]

En recherchant un peu dans le book online (SQL 2008 R2) je suis tombé sur ceci :

Lors d'une analyse de table, l'optimiseur de requête lit toutes les lignes de la table et extrait celles qui répondent aux critères de la requête.
Une analyse de table génère de nombreuses opérations d'E/S disque et peut consommer une grande quantité de ressources.

Comme quoi il faut pas prendre pour argent comptant tout ce qui est note dans BOL et eviter les generalites...
Une table qui tient entierement en memoire et y etant deja chargee ne generera aucune E/S disque meme si elle est entierement scannee.
Avoir l'esprit critique et etre ouvert sont de grandes ressources, bien souvent plus utiles que de connaitre un pourcentage precis de scannage de table.

[mikedavem]

Il est évident qu'il existe un ratio nb de lignes retournées et le nb de lignes total de la table qui est pris en compte par l'optimiseur mais ce n'est pas tout car selon il doit également prendre en compte la fragmentation des index, les opérateurs logiques et donc physiques qui seront utilisés pour retrouver les données etc ...

Si je prends ton exemple que j'ai reproduit chez moi :

Code :

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

SELECT * FROM dbo.T_TEST WHERE val = 'O'

A 20% j'ai un index seek sur l'index non cluster suivi d'un bookmark lookup sur la table HEAP.

> 20 % j'ai déjà un table SCAN. L'optimiseur a donc décidé de scanner l'ensemble de la table car le coût engendré par un INDEX SEEK + Lookup est beaucoup plus consommateur qu'un simple TABLE SCAN.

Maintenant changeons un peu la requête et laissons la colonne couverte par l'index :

Code :

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

SELECT val FROM dbo.T_TEST WHERE val = 'O'

Tu verras que le comportement de l'optimiseur ne sera pas le même ..

++