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 ..

++

[zinzineti]

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.

Il ne s'agit pas d'UN POURCENTAGE PRECIS, rien n'est précis et figé dans la vraie vie. il est question de fourchette, d'intervalle ou de marge.
Ceci dit, le nombre de lignes renvoyées par une requête n'a t-il aucune influence sur l'utilisation d'un index non cluster ? quelles en sont limites ? les exceptions ? ... ?
L'esprit ouvert et critique devrait se poser ces questions et lire d'abord ceci http://www.sqlskills.com/BLOGS/KIMBE...d-Indexes.aspx
plus précisément le paragraphe intitulé : The Tipping Point Query Answers.


Merci de m'éclairer

[zinzineti]

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

Ce ratio est-il aléatoire ?

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 ...

Oui je suis parfaitement d'accord. Mais quels sont les poids de ces différents paramètres qui entre en jeu pour une prise de décision de l'optimiseur ?

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.

Il me semble que ta requête est différente de la mienne

Code :

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

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

A+

[mikedavem]

Ce ratio est-il aléatoire ?

Aléatoire oui dans le sens où d'autres paramètres entrent en jeu.. Mais globalement on peut bien avoir une règle générale de référence qui marche dans la plupart des cas non ? Je dirais que cela est plus une règle générale ou une statistique générale ..

Il me semble que ta requête est différente de la mienne

Autant pour moi, j'avais fait un test sur un jeu de données différent également .. mais cela ne change pas beaucoup . As tu mis à jour les statistiques sur ta table de TEST avant chaque test ? SQLServer ne mettra à jour les statistiques qu'à partir de 20% + 500 des données mises à jour de la table dans ton cas. En principe les statistiques ne sont donc pas mis à jour dans ta fourchette de test ...

Essaye et dis nous si tes résultats changent

++

[zinzineti]

Après lecture de l'article de Kimberly, j'ai voulu vérifier si les résultats que j'obtenais colle un peu avec sa méthode (la méthode Kimberly).
Voici comment j'ai procédé :

1°) Quel est le nombre total de pages occupées par ma table T_TEST ?

Pour répondre à cette question, j'ai utilisé la commande :

Code :

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

1
2
3
SET STATISTICS IO ON
SELECT * FROM dbo.T_TEST
SET STATISTICS IO OFF

qui me renvoie le résultat suivant :

(1000000 row(s) affected)
Table 'T_TEST'. Nombre d'analyses 1, lectures logiques 219698, lectures physiques 0, lectures anticipées 0, lectures logiques de données d'objets volumineux 0, lectures physiques de données d'objets volumineux 0, lectures anticipées de données d'objets volumineux 0.

Je déduis donc que la table T_TEST occupe 219698 pages

2°) Appliquons bêtement la méthode de Kimberly pour déterminer les pourcentages de lignes maximales pour l'utilisation de l'index non-cluster

La table T_TEST occupe 219698 pages

25% de 219698 pages = 219698 * 0,25 = 54924,5
33% de 219698 pages = 219698 * 0,33 = 72500,34

Assimilons ces chiffres comme étant le nombre de lignes [ce n'est pas moi qui le dit, c'est la méthode Kimberly ] afin de calculer le % de lignes

54924,5 / 1000000 = 5,5 %

72500,34 /1000000 = 7,3 %

Donc la méthode Kimberly me dit qu'au delà de 7,3 % de lignes, l'index non-cluster de la table T_TEST ne sera pas utilisé.

Pour mon test voici ce que j'avais obtenu

Sous SQL SERVER 2008 R2
=========================

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

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

Sous SQL SERVER 2005 SP3
=========================

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

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


Le résultat que j'obtiens colle donc avec la méthode proposée par Kimberly .

Si le résultat de mon test ne correspondait pas à la méthode Kimberly, je vais encore piquer cette phrase à Kimberly :

"However, parallelism, some server settings (processor affinity and I/O affinity), memory and table size - all can have an impact "


Avant de fermer ce post, je tiens à remercier particulièrement SQLPro pour ses conseils.

Merci à mikedavem pour sa patience et sa clairvoyance.

Merci à dbaffaleuf pour les pistes sur l'optimiseur et la doc

Enfin Merci à Ptit_Dje pour ses commentaires.

A+

[zinzineti]

J'ai fait une grosse erreur dans mes calculs, au fait j'avais modifié la table T_TEST, les résultats que j'ai annoncé précédemment ne sont pas bon ! Sorry
Je reprends tout.

Code :

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

1
2
3
4
 
SET STATISTICS IO ON
SELECT * FROM dbo.T_TEST
SET STATISTICS IO OFF

Résultat : 4017 pages
=========

Le nombre total de pages pour la table T_TEST est donc : 4017 pages

33 % des pages = 4017 * 0,33 = 1325 pages

L'assimilation nombre de pages et nombre de lignes permet de calculer le % maximal de lignes pour l'utilisation de l'index (méthode Kimberly) :

1325 / 1000000 = 0,13 %

Donc d'après la méthode Kimberly au delà de 0,13 % l'index non-cluster ne sera pas utilisé

Vérifions cela avec notre table T_TEST

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 contrôler le % de lignes renvoyées par la requête précédente :
============


# 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 ....


Résultats :
=========

[0% - 6%[ ==> INDEEX SEEK

[6% - 100%] ==> INDEEX SCAN


Ces résultats ne correspondent pas du tout à ce que prévoie la méthode Kimberly !!!

Comment expliquer ces résultats ?

je dirai "However, parallelism, some server settings (processor affinity and I/O affinity), memory and table size - all can have an impact "

Non, sérieux... je pense que dans mon cas c'est la taille de la table (plus précisement la longueur de la ligne) qui a creusé l'écart par rapport au résultat attendu. Si la table de T_TEST était un peu plus épaisse (10000 pages, 20000 pages , +) les résultats seront proches de ce que propose Kimberly. ce qui n'est même pas sûr ...

Je termine ce post par quelques citations

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.

dbaffaleuf
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

mikedavem
Aléatoire oui dans le sens où d'autres paramètres entrent en jeu.. Mais globalement on peut bien avoir une règle générale de référence qui marche dans la plupart des cas non ? Je dirais que cela est plus une règle générale ou une statistique générale ..

A + les gars

[SQLpro]

Pour info, lorsque je fais cours aux Ars et métiers en PACA, je donne systématiquement un calcul de ce genre à faire : estimé quel index sera utilisé en fonction de la sélectivité, de la fragmentation des lignes dans les pages....

Et généralement il est rare que plus de 10% d'élèves donnent le bon résultat.
Ce que ne m'intéresse d'ailleurs, pas !
Ce qui m'intéresse est la façon dont chacun a raisonné !

En conclusion, le travail que tu as fait, malgré toutes ces erreurs et tâtonnement est un extraordinaire exercice de compréhension de la façon dont fonctionne un optimiseurs au niveau des choix à faire....

A +

[zinzineti]

Merci SQLPro pour le compliment. Je ne pourrai pas y arriver tout seul .
Heureusement que ce forum existe et permet l'entraide. Le plus de ce forum (developpez.com) c'est la
présence des personnes de haute qualité, très compétent et ayant une bonne connaissance des bases de données.
Ceci dit, j'ai fait une petite synthèse sur le sujet sur mon blog

A+