Performance requête dynamique

**sqllm** · 06/06/2016, 21h14

Bonjour,

J'ai ecrit une procedure stockee qui construit une requete dynamiquement et qui l'execute via

EXEC sp_executesql @SQL, @PARAM

.

Cela fonctionne et me retourne un resultat en 12 secondes mais quand je lance la meme requete que celle generee le resultat est trouve en 1 seconde !

J'ai vu que cela pouvait provenir du sniffing de parametre et qu'il fallait utiliser l'option

OPTION (OPTIMIZE FOR UNKNOWN)

mais cela ne change pas grand chose.

D'autre part j'utilise une function table (Inline Table). J'ai essaye d'enlever la jointure sur cette function table, la difference d'execution est moins importante (3s VS 1s) mais il reste un ecart.

Je sais plus vraiment ou chercher, auriez-vous une piste ?

Ci joint la requete (simplifiee) generee

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
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
 
SELECT STATFAM.CODE, 
       frm_MAIN.*, 
       ( CA_2016 - CA_2015 ) / NULLIF(CA_2015, 0) * 100 
FROM   (SELECT CRITERE1, 
               Sum(CA_2015)       AS CA_2015, 
               Sum(CA_2016)       AS CA_2016, 
               Grouping(CRITERE1) AS GRPCRITERE1
 
        FROM   (SELECT udv_MAIN.STATFAM_ID             AS CRITERE1, 
                       DATA + '_' + CONVERT(char(4), ANNEE) AS COL, 
                       VALUE 
                FROM   dashboard.udv_MAIN 
                       JOIN (SELECT STATFAM_ID 
                             FROM   dbo.udf_SplitRange('05;06') R 
                                    JOIN dashboard.STATFAM 
                                      ON STATFAM.CODE BETWEEN R.DEBUT AND R.FIN) 
                            FCFAM1 
                         ON FCFAM1.STATFAM_ID = udv_MAIN.STATFAM_ID 
                WHERE   ( ( udv_MAIN.DATEFACTURE BETWEEN 
                               Dateadd(YEAR, -1, '2016-01-01') 
                               AND 
                               Dateadd(YEAR, -1, '2016-12-31') ) 
                              OR ( udv_MAIN.DATEFACTURE BETWEEN 
                                   '2016-01-01' AND '2016-12-31' 
                                 ) ) 
                       ) SRC 
               PIVOT (Sum(VALUE) 
                     FOR COL IN ( [CA_2015], 
                                  [CA_2016] )) PIV 
        GROUP  BY ROLLUP( CRITERE1 ) 
        ) AS frm_MAIN 
       LEFT JOIN dashboard.STATFAM 
              ON STATFAM.STATFAM_ID = frm_MAIN.CRITERE1 
ORDER  BY frm_MAIN.GRPCRITERE1, 
          STATFAM.CODE 
OPTION (OPTIMIZE FOR UNKNOWN)

**sqllm** · 07/06/2016, 00h22

En utilisant

OPTION (RECOMPILE)

Les temps d'execution sont comparables.

Je laisse ouvert pour que quelqu'un puisse m'expliquer car je ne comprends pas l'interet d'utiliser sp_executesql si les plans d'execution ne sont pas reutilises ?

**SQLpro** · 07/06/2016, 12h11

Tout dépend du paramètre "optimize for ad hoc workload" dans sp_configure...

A lire :
Nom : Couverture livre SQL server Eyrolles.jpg
Affichages : 352
Taille : 105,0 Ko

Nom : Couverture livre SQL server Eyrolles.jpg
Affichages : 352
Taille : 105,0 Ko

A +

**elsuket** · 07/06/2016, 22h56

Bonjour,

Pour vérifier ce que vous indique SQLPro, vous pouvez écrire :

SELECT *
FROM sys.configurations
WHERE name = 'optimize for ad hoc workloads'

Si la colonne value indique 1, c'est que cette option est activée.

Là où je ne saisis pas, c'est que cette option vaut lorsque la chaîne de requête contient des valeurs de filtre codées "en dur", du style :

Code :

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

1
2
3
SELECT	mesColonnes
FROM	dbo.maTable
WHERE	uneColonne = 123456

Lorsque l'option n'est pas activée, ce qui est le cas par défaut, le moteur compile un plan, et le met en cache. Ceci occupe un certain espace en RAM.
Supposons maintenant que l'on soumet une telle requête de nombreuses fois, mais qu'à chaque fois seule la valeur (123456) change.
On a alors une multitude de plans pour une petite variation de l'expression de la requête, ce qui peut vite occuper plusieurs centaines de Mo voire Go; or les chances qu'un tel plan soit réutilisé sont très faibles, puisqu'il faudrait que la même requête (plus exactement son texte) soit soumise une nouvelle fois. Donc l'efficacité du cache de requêtes est réduite, et il occupe un espace qui pourrait être occupé par d'autres plans, ou par le cache de données.

Pourquoi ce phénomène ? En fait SQL Server cherche si une requête est déjà en cache sur la base du hash du texte de la requête.
Il suffit donc qu'un seul caractère de la requête change pour que le hash change.

Si l'on souhaite donc éviter une pollution du cache de requête, on peut activer cette option. Dans ce cas, lorsqu'une telle requête est soumise pour la première fois à SQL Server, il en stocke ce que l'on pourrait appeler un talon : c'est en fait le hash de la requête. Pour la petite histoire, il occupe presque 400 octets (cf. sys.dm_exec_cached_plans.size_in_bytes et cacheobjtype = 'Compiled Plan Stub'). Ce hash est beaucoup plus petit qu'un plan de requête. La seconde fois, il fait le même calcul, et comme il trouve le talon du plan, il stocke le plan compilé. Lors des soumissions suivantes, le même plan est réutilisé. Il en résulte une pollution et une taille moindre du cache de requêtes. Ceci d'autant que les plans qui n'ont été utilisés qu'une seule fois, ainsi que les talons sont les premiers a être évincés du cache de requêtes. L'éviction est faite par un algorithme qui calcule un âge au plan suivant (pour simplifier) son nombre d'utilisations , son coût de génération et une période de temps donnée.

Revenons maintenant à nos moutons. Vous dites exécuter vos requêtes à l'aide de EXEC sp_executesql @SQL, @PARAM.
Ce qui est surprenant dans cet appel, c'est que si votre requête est paramétrée, @PARAM devrait être suivi de l'affectation des variables qui participent à la requête.
Pourriez-vous donner un exemple réel d'appel SVP ?

En ce qui concerne OPTION (RECOMPILE), utilisez-le avec parcimonie : si la requête cible est exécutée un grand nombre de fois, comme la compilation est un processus coûteux en CPU, il peut en résulter une utilisation CPU élevée. Cet indicateur de requête peut être intéressant pour des requêtes exécutées peu de fois, comme ce peut être le cas avec du reporting.

Cela fonctionne et me retourne un resultat en 12 secondes mais quand je lance la meme requete que celle generee le resultat est trouve en 1 seconde !

Si vous exécutez la requête sans sp_executesql et avec des variables, qui sont donc locales, SQL Server ne tient pas compte de la valeur des variables lors du calcul du plan (*)
Dans le cas où vous utilisez sp_executesql avec des variables (ex : EXEC sp_executesql @sql, @params, @v1 = n, @v2 = 's'), SQL Server se sert de la valeur des paramètres pour estimer le nombre de lignes qui sera retourné par chaque accès à une table. Donc pour ces deux cas, les règles d'optimisation peuvent être différentes, et donc aboutir à deux plans différents, d'où la différence de vitesse d'exécution.

Il se peut également que les options de session de votre application soient différentes de celles que vous utilisez pour exécuter manuellement la même requête. Là encore, cela peut mener à deux plans de requête différents.

J'ai vu que cela pouvait provenir du sniffing de parametre et qu'il fallait utiliser l'option OPTION (OPTIMIZE FOR UNKNOWN)

Effectivement dans ce cas on revient au cas que j'ai noté avec une astérisque un peu plus haut : vous indiquez à SQL Server de ne pas tenir compte de la valeur de toutes les variables qui participent à la requête lorsqu'il compile le plan. C'est un peu brutal, mais cela peut fonctionner; notez néanmoins que ce sont des cas rares. Légèrement moins rarement, il est possible que seule la valeur d'une variable pose problème, et dans ce cas on peu utiliser OPTION (OPTIMIZE FOR (@v UNKNOWN)) ou encore OPTION (OPTIMIZE FOR (@v = uneConstante)).

Ceci se produit en général lorsqu'on a soumis une requête avec des valeurs de variable qui ne se trouvent pas dans l'histogramme qui représente la distribution des valeurs dans les colonnes et les index. Pour simplifier, l'histogramme peut décrire jusqu'à 200 intervalles de valeur pour une colonne ou la colonne la plus à gauche de la clé d'un index : il contient le nombre de lignes qui sont égales à la borne inférieure de l'intervalle, le nombre total de lignes dans l'intervalle, le nombre de valeurs distinctes dans l'intervalle. C'est à partir de cet histogramme que l'optimisation se fait. Avec la vie de la base de données, ces statistiques sont automatiquement créées et mises à jour par le moteur de SQL Server. Cette deux tâches sont en général réalisées par échantillonnage des données de la table (sinon cela coûterait très cher en IO), et de façon générale, une fois que le nombre de lignes modifiées suivant a été atteint : 500 + 20% * nombre de lignes de la table. Par modifiées, on entend le nombre total de lignes écrites par des ordres INSERT, UPDATE , DELETE. Notons que le recalcul de l'objet de statistique entraîne le recalcul des plans de requête qui l'ont utilisé.

Supposons que l'on a une requête qui filtre sur une colonne de type date à l'aide d'une variable, que la table est insérée très fréquemment, et que les valeurs de uneColonneDate sont toujours croissantes :

Code :

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

1
2
3
SELECT	desColonnes
FROM	dbo.uneTable
WHERE	uneColonneDate = @uneVariableDate

Supposons aussi que l'histogramme, qui vient d'être recalculé, n'a pas d'intervalle qui contient la valeur de la variable, et ce du fait de l'échantillonage.
Le moteur ne peut donc pas optimiser suivant la valeur de cette variable, et décide donc d'un plan qui peut être radicalement différent de ce qu'il aurait été dans le cas contraire.
Vous imaginez aisément les conséquences que cela peut avoir. Là encore, il s'agit d'un cas rare.

L’idéal est donc que vous capturiez le plan d'exécution des deux cas, avec la sortie de SET STATISTICS IO, TIME ON (à placer juste avant sp_executesql) dans le cas où vous réalisez une exécution manuelle. Il n'y a qu'en comparant les deux plans que l'on peut comprendre pourquoi leur exécution est différente.

Vous pouvez récupérer le plan exécuté par l'application à l'aide de la requête suivante :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
SELECT		CP.refcounts
		, CP.usecounts
		, CP.size_in_bytes
		, CP.cacheobjtype
		, CP.objtype
		, ST.text
		, QP.query_plan
FROM		sys.dm_exec_cached_plans AS CP
CROSS APPLY	sys.dm_exec_sql_text(CP.plan_handle) AS ST
CROSS APPLY	sys.dm_exec_query_plan(CP.plan_handle) AS QP
WHERE		ST.text LIKE '%unePartieBienCaractéristiqueDeVotreRequête%'

Les chapitres 13 et 14 du livre que SQLPro vous recommande, et moi aussi puisque nous en sommes deux des quatre auteurs, explique le cache de requêtes et la lecture des plans d'exécution graphique. Le chapitre 17 décrit également la maintenance des statistiques.

@++

**sqllm** · 08/06/2016, 04h01

Bonjour,

Tout d'abord un grand merci pour le temps passé et la qualite de la reponse elsuket

Pour repondre a vos questions :

SELECT *
FROM sys.configurations
WHERE name = 'optimize for ad hoc workloads'

la colonne value vaut bien 0

Je passe bien les parametres après le @PARAM (j'ai un peu trop simplifie mon exemple)

En reprenant vos explications je pense que je suis dans ce cas

Si vous exécutez la requête sans sp_executesql et avec des variables, qui sont donc locales, SQL Server ne tient pas compte de la valeur des variables lors du calcul du plan (*)
Dans le cas où vous utilisez sp_executesql avec des variables (ex : EXEC sp_executesql @sql, @params, @v1 = n, @v2 = 's'), SQL Server se sert de la valeur des paramètres pour estimer le nombre de lignes qui sera retourné par chaque accès à une table. Donc pour ces deux cas, les règles d'optimisation peuvent être différentes, et donc aboutir à deux plans différents, d'où la différence de vitesse d'exécution.

car les valeurs de filtre interrogees sont dans l'histogramme je suppose.

Comme vous l'avez devine il s'agit d'une requete de reporting et donc elle n'est pas executee un grand nombre de fois. L'option RECOMPILE ne devrait donc pas etre penalisante.

Pour les plans d'execution j'avais effectivement eu la meme approche mais pour etre honnete j'ai ete decourage par la taille des XML. Plus de 1500 lignes avec beaucoup de differences ...

Puisque je suis regulierement vos interventions ainsi que celles de SQLPro j'avais deja vu votre blog sur SQL Sentry Plan Explorer je vais voir si je peux l'installer. Si j'ai les droits je pourrai anonymiser les plans et les poster.

Concernant le livre, cela fait un moment qu'il me fait de l'oeil ! Des que je rentre en France je l'achete. Malheureusement pas avant la fin d'annee

Encore merci pour vos explications detailles

**elsuket** · 08/06/2016, 14h06

Bonjour,

Je passe bien les parametres après le @PARAM (j'ai un peu trop simplifie mon exemple)

OK, donc votre appel est du style : EXEC sp_executesql @sql, @params, @v1 = n, @v2 = 's', c'est bien ça ?

Comme vous l'avez devine il s'agit d'une requete de reporting et donc elle n'est pas executee un grand nombre de fois. L'option RECOMPILE ne devrait donc pas etre penalisante.

Certes, mais l'idéal serait tout de même de ne pas avoir à utiliser cet indicateur de requête.

Pour les plans d'execution j'avais effectivement eu la meme approche mais pour etre honnete j'ai ete decourage par la taille des XML. Plus de 1500 lignes avec beaucoup de differences ...

C'est pour cela qu'il y a les plans d'exécution graphique

. On peut comparer les plans avec des outils comme Beyond Compare, ou encore WinMerge.
Cela dit l'interprétation est facilitée par le plan d'exécution graphique; l'article que je vous ai donné pour la capture de plans mentionne SQL Sentry Plan Explorer, dont il existe une version gratuite qui est très largement suffisante.

Si j'ai les droits je pourrai anonymiser les plans et les poster.

Il n'y a pas de couche de sécurité sur l'anonymisation : SQL Sentry Plan Explorer donne une représentation graphique du plan de requête; une autre, moins pratique, est donnée par SQL Server Management Studio.

Concernant le livre, cela fait un moment qu'il me fait de l'oeil ! Des que je rentre en France je l'achete. Malheureusement pas avant la fin d'annee

Dans quel pays vous trouvez-vous ? Il se peut qu'en voyage je vous en amène un exemplaire

Pour la lecture de plans, vous pouvez démarrer avec cet excellent PDF de Grant Fritchey : en plus, il est gratuit.

Encore merci pour vos explications detailles

Merci à vous