Discussion :

[KyoshiroKensei]

Bonjour,

J'ai eu un problème sur une procédure stockée. Elle mettait 23 seconde à s'exécuter. Après recherche, il s’avère qu'un select prend 22 secondes d’exécution. Le plan d’exécution me semble curieux (2 boucle imbriquée?)

Après plusieurs tests, je constate les faits suivant:
- l'instruction posant problème s’exécute en moins de 1 seconde hors procédure stockée
- en supprimant le tri selon la colonne, l’exécution est immédiate.

Dans l'image est présente la requête 1 qui pose problème.

J'ai solutionné le problème en mettant le résultat (sans le top 1) de la requête 2 dans une table temporaire, puis en faisant un top 1 + order by sur cette table temporaire.

Du coup, je m'interroge sur l'utilisation de la clause order by. J'ai l'impression que l'order by ne s'applique pas uniquement à mon jeux de résultat. Je souhaiterai avoir vos avis.

La table lue contient 3 millions de lignes, les index non fragmentés. La requete retourne 600 lignes.

[aieeeuuuuu]

Bonjour,

Il faudrait connaitre les index qui existent sur vos tables. Vos statistiques sont-elles à jour ?

Visiblement, SQL server change de stratégie avec le ORDER BY afin de profiter d'un troisième index.

Vous pouvez essayer les fonctions analytiques :

Code :

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

1
2
3
4
5
6
7
8
9
 
WITH CTE AS (
     SELECT idTbarre
        ,ROW_NUMBER() OVER(ORDER BY DateHeure) AS RN
        FROM  ... --la suite de votre requete
)
SELECT idtBarre 
FROM CTE
WHERE RN = 1

[KyoshiroKensei]

Je pense que les statistiques étaient à jour, le plan de maintenance mettant à jour les statistique datant de moins de 5 heures. Il a les index non cluster suivant:


Action + Position
IdtBarre Desc
Position
Position + Dateheure Desc
Position + Idtbarre Desc

[hmira]

C'est connu, la clause Order By influence à coup sûr la stratégie de l'optimiseur dans l'élaboration du plan optimal. Un index qui va dans le même sens que l'ordre by sera "préviligié" ou du moins "retenu" par l'optimiseur. Dans ce contexte, l'optimiseur s'affranchit de l'opération Trier (Sort) puisque les lignes sont déjà triées au travers l'index au moment de leur extraction.

Dans l'exemple de la première requête l'order by (ORDER DateHeure ASC) plus le filtre Sui.Position = 'PFC1' , fini par coïncider avec l'index TabSuiviBarre.Position_DateHeure.. (qui je crois est établi sur Position+Dateheure à confirmer par KyoshiroKensei ). Et, c'est la raison pour laquelle, on ne voit pas l'opération Trier (Sort) dans le premier plan alors qu'il y a bien un "ORDER BY DateHeure ASC"

A partir de SQL Server 2008 R2, vous pouvez utiliser les Hint pour le cas échéant forcer l'utilisation d'index. Dans votre cas, ce sera forcer l'utilisation de l'index Position_IdBarre (au lieu de l'index Position_DateHeure utilisé actuellement par l' optimiseur dans la première requête). Il faut faire le test et mesurer les perf. parce si vous forcer l'utilisation de l'index Position_IdBarre, tout en gardant l'Order by (ORDER BY DateHeure ASC), vous allez voir une Opération Trier (Sort) qui va apparaître en plus dans le plan, espérant que celle-ci ne va pas à nouveau "plomber" votre plan d'exécution.

Pour cela, vous pouvez utiliser le hint FORCESEEK comme indiqué ci-dessous :

Code SQL :

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

1
2
3
4
..... 
FROM  dbo.TabSuiviBarre sui  WITH (FORCESEEK(Position_IdtBarre(Position) ))
.... 
ORDER DateHeure ASC

A+

PS : Tout cela est pour vous proposer des solutions de contournement, mais ne répond évidemment pas à la question : "Pourquoi, dans la requête n° 1, l'optimiseur a été fourvoyé en élaborant un plan qui est loin d'être le plus optimal !

A+

[KyoshiroKensei]

bonjour,

je confirme pour l'index.

Merci de votre réponse, je vais tester en forçant l'utilisation de l'index pour voir l'impact sur les performances. D'habitude, j'étais plutôt contre l'utilisation de table temporaire, mais maintenant que je vois cela, je vais être plus prudent dans l'utilisation des ORDER By.

[aieeeuuuuu]

Position
Position + Dateheure Desc

Le deuxième est redondant avec le premier.
Je pense que la première chose à faire est de revoir votre stratégie d'indexation...

[KyoshiroKensei]

Ces 2 index sont nécessaire.

[aieeeuuuuu]

Non, si le deuxième existe, le premier est totalement inutile.

[SQLpro]

Non, si le deuxième existe, le premier est totalement inutile.

Je suis pas d'accord avec toi :
1) l'optimiseur ne choisira par forcément l'index à 2 colonnes si une seule est demandée
2) l'ordre inverse de la date est souvent judicieux, mais trompe parfois les stats.

A +

[KyoshiroKensei]

Ces 2 index correspondent à 2 façons différentes d’interroger la table, l'une selon une position particulière, l'autre permet de cherchre une position dans les lignes en début de table (dateheure desc) pour éviter de parcourir toutes les données. Lorsque je regarde les statistique d'utilisation, ces 2 index sont utilisé. J'ai ajouté ces index car certaines requêtes répétitives étaient trop longue sans.

Petite précision: ces 2 index ne comportent pas les même colonnes incluses.

[aieeeuuuuu]

Je suis pas d'accord avec toi :
1) l'optimiseur ne choisira par forcément l'index à 2 colonnes si une seule est demandée

Certes, si les deux existent, le premier pourrait être utilisés si la colonne date n'est pas utile pour une requete donnée. Cependant, en l'absence du premier, le deuxième sera utilisé. A mon avis, le gain obtenu dans le premier cas sera bien en dessous de la perte globale dûe a cet index supplémentaire : maintenance des index et utilisation du cache des données....
Donc à part peut être dans des cas très précis, je ne vois pas l'utilité du premier index

2) l'ordre inverse de la date est souvent judicieux, mais trompe parfois les stats.
A +

Oui, mais la colonne date est la deuxième colonne de l'index...

Petite précision: ces 2 index ne comportent pas les même colonnes incluses.

C'est même une grosse précision. Dans ce cas en effet, c'est différent...
Cela dit, il est peut-être possible de les fusionner....

[hmira]

Je suis pas d'accord avec toi :
1) l'optimiseur ne choisira par forcément l'index à 2 colonnes si une seule est demandée
...

Certes, si les deux existent, le premier pourrait être utilisés si la colonne date n'est pas utile pour une requete donnée. Cependant, en l'absence du premier, le deuxième sera utilisé. A mon avis, le gain obtenu dans le premier cas sera bien en dessous de la perte globale dûe a cet index supplémentaire : maintenance des index et utilisation du cache des données....
Donc à part peut être dans des cas très précis, je ne vois pas l'utilité du premier index
....
C'est même une grosse précision. Dans ce cas en effet, c'est différent...
Cela dit, il est peut-être possible de les fusionner....

Dans un environnement OLAP, on peut à la rigueur, garder les 2 indexes.
Mais dans un environnement OLTP, sur ce point, personnellement j'aurais tendance à être de l'avis de aieeeuuuuu, et j'aurais donné exactement les mêmes arguments.

Ceci dit, on ne peut pas pas être aussi catégorique. Avant de droper un index il faut prendre beaucoup de précautions :

1 - Il faut au préalable mesurer le ratio entre lecture et écriture (cf sys.dm_db_index_usage_stats ) et ce n'est que lorsque lorsque le nombre d'écriture est largement supérieur au nombre de lecture que l'on peut envisager la suppression de l'index.
2 - L'index à supprimer peut être utilisé dans des requêtes occasionnelles mais néanmoins importantes ! Donc il faut mener une enquête approfondie, mesurer le pour et le contre etc.

Ci-dessous la requête pour identifier les indexes "expansifs" (càd ceux qui génèrent plus d'écritures que de lectures) :

Code SQL :

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

1
2
3
4
5
6
7
8
SELECT s.index_id, OBJECT_NAME(s.object_id), i.name, i.type_desc
FROM sys.dm_db_index_usage_stats s WITH (NOLOCK )
JOIN sys.indexes i WITH (NOLOCK) ON s.index_id = i.index_id
AND s.object_id = i.object_id
WHERE   OBJECTPROPERTY(s.[object_id], 'IsUserTable') = 1
AND s.database_id = DB_ID()
AND s.user_updates > ( s.user_seeks + s.user_scans + s.user_lookups )
AND s.index_id > 1  -- Index Non Cluster uniquement

A+