Discussion :

[aolelien]

Hello,

J'ai mis le doigt sur une requête qui a gros problèmes de performances, lorsque les clauses du where sont variabilisés alors que cela est immédiat lorsque les valeurs sont passées en "dur".

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
set statistics time on
 set statistics io on
 go
DECLARE  @GRRE_Cod               AS  TINYINT,   
         @CECP_OrgDes            AS SMALLINT,  
         @ENBO_SITE_CodFac       AS  SMALLINT  
 
SET @GRRE_Cod = 1
SET @CECP_OrgDes = 931
SET @ENBO_SITE_CodFac = 319         
 
  SELECT  TOP 1   ENBO.ENBO_Lot  
  FROM	dbo.ENBO_EnteteBordereauTP  ENBO with (nolock) 
  INNER JOIN  dbo.CECP_CentreCPAM	CECP with (nolock) 
		ON ENBO.ENBO_CECP_GRRE_CodFac  = CECP.CECP_GRRE_Cod  
		AND ENBO.ENBO_CECP_CPAM_CodFac  = CECP.CECP_CPAM_Cod  
		AND (ENBO.ENBO_CECP_CodFac       = CECP.CECP_Cod or ENBO.ENBO_CECP_CodFac is null)  
  WHERE	CECP.CECP_GRRE_Cod      = @GRRE_Cod  
		AND CECP.CECP_OrgDes        = @CECP_OrgDes  
		AND ENBO.ENBO_SITE_CodFac   = @ENBO_SITE_CodFac  
		AND ENBO.ENBO_Lot           IS NOT NULL  
    ORDER BY     ENBO.ENBO_DatBor        DESC
	,CAST(ENBO.ENBO_Lot AS int)      DESC  
go
set statistics time off
 set statistics io off
 go

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
SQL Server parse and compile time: 
   CPU time = 20 ms, elapsed time = 20 ms.
 
 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
 
 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
 
 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
 
(1 row(s) affected)
Table 'ENBO_EnteteBordereauTP'. Scan count 112, logical reads 4789929, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'CECP_CentreCPAM'. Scan count 1, logical reads 142, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
 
 SQL Server Execution Times:
   CPU time = 18080 ms,  elapsed time = 18094 ms.

logical reads 4789929 - elapsed time = 18094 ms

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
set statistics time on
 set statistics io on
 go
 SELECT  TOP 1   ENBO.ENBO_Lot  
  FROM         dbo.ENBO_EnteteBordereauTP  ENBO with (nolock) 
  INNER JOIN  dbo.CECP_CentreCPAM         CECP with (nolock) 
	ON ENBO.ENBO_CECP_GRRE_CodFac  = CECP.CECP_GRRE_Cod  
    AND ENBO.ENBO_CECP_CPAM_CodFac  = CECP.CECP_CPAM_Cod  
    AND (ENBO.ENBO_CECP_CodFac       = CECP.CECP_Cod or ENBO.ENBO_CECP_CodFac is null)  
  WHERE	CECP.CECP_GRRE_Cod      = 1  
        AND CECP.CECP_OrgDes        = 931  
        AND ENBO.ENBO_SITE_CodFac   = 319  
        AND ENBO.ENBO_Lot           IS NOT NULL  
 
  ORDER BY     ENBO.ENBO_DatBor        DESC,  
        CAST(ENBO.ENBO_Lot AS int)      DESC 
 
go
set statistics time off
 set statistics io off

Code :

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

1
2
3
4
5
6
7
8
9
10
11
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.
 
(1 row(s) affected)
Table 'CECP_CentreCPAM'. Scan count 6, logical reads 504, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ENBO_EnteteBordereauTP'. Scan count 1, logical reads 20461, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
 
 SQL Server Execution Times:
   CPU time = 515 ms,  elapsed time = 502 ms.

logical reads 20461 - elapsed time = 502 ms




Quelqu'un a t'il une explication du pourquoi une telle différence, alors que le plan devrai être le même?

Merci d'avance

[hmira]

Oui, évidemment que nous avons l'explication ! Cette problématique est connue du moins par les experts !

Cela provient, sous réserves, du fait que le plan d'exécution généré, à partir des premières valeurs des paramètres (attention ces valeurs ne sont pas forcément celles que vous utilisez dans vos tests), et mis en cache, n'est pas optimal, pour d'autres valeurs de paramètres (exemple celles que vous utilisez dans vos test), aboutissant ainsi à forte dégradation des performances lorsque le dit plan est utilisé avec d'autres valeurs de paramètres !

Réserves et questions :
- Utilisez-vous des tables temporaires dans vos requêtes ?
- Vos statistiques de vos tables et indexes sont-elles bien à jour ?

Si les réponses aux 2 questions ci-dessus sont respectivement :
Non (c.à.d vous n'utilisez pas les tables temporaires)
Oui (c.à.d vos statistiques sont à jour ..)

alors vous devez utilisez un des indicateurs suivant :
OPTIMIZE FOR ...
ou
RECOMPILE

A+

[SQLpro]

C'est plus généralement un problème de "parameter sniffing". L'option RECOMPILE peut aider la chose à moins se produire, mais il y a d'autres techniques.

A lire : http://blog.developpez.com/sqlpro/p9...ns_de_requetes

A +

[aolelien]

Oui, évidemment que nous avons l'explication ! Cette problématique est connue du moins par les experts !

Cela provient, sous réserves, du fait que le plan d'exécution généré, à partir des premières valeurs des paramètres (attention ces valeurs ne sont pas forcément celles que vous utilisez dans vos tests), et mis en cache, n'est pas optimal, pour d'autres valeurs de paramètres (exemple celles que vous utilisez dans vos test), aboutissant ainsi à forte dégradation des performances lorsque le dit plan est utilisé avec d'autres valeurs de paramètres !

Réserves et questions :
- Utilisez-vous des tables temporaires dans vos requêtes ?
- Vos statistiques de vos tables et indexes sont-elles bien à jour ?

Si les réponses aux 2 questions ci-dessus sont respectivement :
Non (c.à.d vous n'utilisez pas les tables temporaires)
Oui (c.à.d vos statistiques sont à jour ..)

alors vous devez utilisez un des indicateurs suivant :
OPTIMIZE FOR ...
ou
RECOMPILE

A+

Bonjour,

Merci pour ton retour,

Les index/stats sont bien à jour.
Par contre il me semblai que SQL utilisai la "parametrizastion".
Elle est configuré en "simple" sur mon environnement.
Mais il semblerai que cela ne fonctionne correctement qu'avec l'utilisation de sp_executesql.
Dans mon cas, le moteur paramétrize correctement la requête avec les valeurs en dur (il utilise les paramètres @0, @1 et @2), mais il va garder les noms des variables dans le cas de la requête passée avec les variables.


L'ajout de l'option recompile a en effet amélioré les choses étant donné qu'il utilise maintenant le bon plan d'eéxécution.

Merci

[aolelien]

C'est plus généralement un problème de "parameter sniffing". L'option RECOMPILE peut aider la chose à moins se produire, mais il y a d'autres techniques.

A lire : http://blog.developpez.com/sqlpro/p9...ns_de_requetes

A +

Merci, je vais lire cet article.

A+

[elsuket]

Bonjour,

Si la mise à jour des statistiques est faite régulièrement, et si mettre à jour les statistiques pour une requête la rend plus performante, alors ce n'est qu'une solution éphémère.
De la même manière, forcer la recompilation à chaque exécution n'est pas une très bonne solution, surtout si la requête est fréquemment exécutée : chaque compilation consomme du CPU.

La recompilation d'un plan intervient lorsqu'une des statistiques a été marquée comme obsolète et recalculée, et que la mise à jour automatique des statistiques est activée, ce qui est le cas par défaut. Pour confirmer que cela n'a pas été changé, vous pouvez exécuter la requête suivante :

Code :

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

1
2
3
4
5
SELECT	name
	, is_auto_update_stats_on
	, is_auto_update_stats_async_on
	, is_auto_create_stats_on
FROM	sys.databases

Qu'est-ce qui déclenche la mise à jour automatique des statistiques ?

• Si la table contient moins de 6 lignes, et qu'elle est dans TempDB, les statistiques seront recalculées dès que l'ensemble des modifications des données de cette table aura affecté au moins 6 lignes.
• Si la table est une variable (DECLARE @t TABLE), aucun objet de statistiques n'est créé, et l'optimiseur estimera toujours qu'il y a une seule ligne dans cette table, sauf sur recompilation. Une telle table est aussi stockée dans TempDB.
• Si la table contient entre 7 et 500 lignes, les statistiques seront recalculées dès que l'ensemble des modifications des données de cette table aura affecté au moins 500 lignes
• Si la table contient plus de 500 lignes, les statistiques seront recalculées dès que l'ensemble des modifications des données de cette table aura affecté au moins 500 lignes + 20% du nombre total de lignes de cette table.

Par modification, nous entendons toute requête INSERT, UPDATE, DELETE ou MERGE.

Vous obteniez un meilleur fonctionnement avec sp_executesql : c'est certainement du au fait que vous avez exécuté cette requête en passant à sp_executesql les valeurs de paramètres qui font que le plan est optimal (mais peut-être qu'il ne l'est pas pour d'autres valeurs !).

Pourquoi ne pas avoir créé une procédure stockée à la place, même si cela ne prévient pas nécessairement pas ce problème ?
Si les performances sont toujours mauvaises (tester avec d'autres valeurs que celles que vous donnez), examinez le plan de requête réel, et vous allez probablement trouver la source du problème. Si vous ne la trouvez pas, SQL Sentry Plan Explorer permet d'anonymiser le plan, et vous pouvez donc le poster ici.
Par ailleurs, si vous changez le texte de la requête (simplement en ajoutant un espace dans son expression), alors un autre plan va être généré. Cela est du au fait que SQL Server recherche si un plan pour cette chaîne de caractères existe déjà dans le cache de plans sur la base d'une somme de contrôle.

Alternativement, vous pouvez utiliser l'indicateur de requête OPTIMIZE FOR, aussi bien dans la requête que dans la procédure stockée : ceci vous permet de forcer la compilation du plan avec des valeurs particulières.

[joujousagem2006]

Oui, évidemment que nous avons l'explication ! Cette problématique est connue du moins par les experts !

Cela provient, sous réserves, du fait que le plan d'exécution généré, à partir des premières valeurs des paramètres (attention ces valeurs ne sont pas forcément celles que vous utilisez dans vos tests), et mis en cache, n'est pas optimal, pour d'autres valeurs de paramètres (exemple celles que vous utilisez dans vos test), aboutissant ainsi à forte dégradation des performances lorsque le dit plan est utilisé avec d'autres valeurs de paramètres !

Réserves et questions :
- Utilisez-vous des tables temporaires dans vos requêtes ?
- Vos statistiques de vos tables et indexes sont-elles bien à jour ?

Si les réponses aux 2 questions ci-dessus sont respectivement :
Non (c.à.d vous n'utilisez pas les tables temporaires)
Oui (c.à.d vos statistiques sont à jour ..)

alors vous devez utilisez un des indicateurs suivant :
OPTIMIZE FOR ...
ou
RECOMPILE

A+

il ont conseillé de ne plus utiliser option

Code :

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

with recompile

vu que SQL Server ne va pas créé un plan de cache pour cette procédure ce qui va entraîner un ralentissent d’exécution

je suis sur la même situation utilisation du même procédure avec des paramètres différents avec un temps d’exécution différent

si je fait un exec sp_recompile 'non du procédure" est ce que possible que mon problème peuvent être résolu

pour info a chaque fois j'aurai ce scénario je fait un recalcul du stats et le problème disparu

aussi l'option OPTIMIZE FOR ... existe que a partir du version sql2008 alors que mon instance est sous sql2005

[soazig]

Bonjour,
Sur la même problématique, il y a cet article qui m'a intéressé.
http://www.sommarskog.se/query-plan-mysteries.html

Sqlpro cite cet article dans le sien, comme quoi c'est une bonne source, mais du coup cela fait double emploi
Bonne soirée
Soazig

[hmira]

il ont conseillé de ne plus utiliser option

Code :

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

with recompile

vu que SQL Server ne va pas créé un plan de cache pour cette procédure ce qui va entraîner un ralentissent d’exécution

je suis sur la même situation utilisation du même procédure avec des paramètres différents avec un temps d’exécution différent

si je fait un exec sp_recompile 'non du procédure" est ce que possible que mon problème peuvent être résolu

pour info a chaque fois j'aurai ce scénario je fait un recalcul du stats et le problème disparu

aussi l'option OPTIMIZE FOR ... existe que a partir du version sql2008 alors que mon instance est sous sql2005

Il s'agit comme cela été évoqué ci-haut d'un problème connu "parameter sniffing". Il ne s'agit pas d'un problème de recalcul des statistiques qui ne serait pas à jour. Le problème est ailleurs, il s'agit encore une fois du problème "parameter sniffing".

Dans le cas présent, le recalcul des statistiques comme sp_recompile sont des leurres ! En effet, parmi les conditions qui provoquent l'invalidité d'un plan et force donc sa recompilation lors de la prochaine exécution de la requête, il y a UPDATE STATISTICS et sp_recompile (il y en a d'autres ..). Cela revient à mettre du sparadrap sur un problème, lequel sparadrap se décollera à la prochaine exécution de la requête !

Pour ce problème, ayant trait aux "parameter sniffing", il n'y a malheureusement pas de solution miracle. En effet, il y a des situations où il est impossible d'avoir un plan optimal, et ce, quelles que soit les valeurs des paramètres !

Une solution consiste à examiner en détails les plans d'exécution des différents requêtes de la procédure et ce en fonctions de divers valeurs de paramètres et divers scénarios et de traiter au cas par cas. Cela consiste par exemple à adapter et réécrire la procédure et faire en sorte que pour chaque plage de valeurs des paramètres ou contextes on applique un OPTIMIZE FOR différent
Exemple :
Plage_A de valeurs de paramètres --> OPTIMIZE FOR Valeur_pour_Plage_A
Plage_B de valeurs de paramètres --> OPTIMIZE FOR Valeur_pour_Plage_B
etc.

OPTIMIZE FOR Valeur est solution plus "fine" et préférable à l'option RECOMPILE ou OPTIMIZE FOR UNKNOWN
L'option RECOMPILE est une solution au problème, de facilité certes, mais elle mérite d'exister.

Pour le reste il faut lire l'article indiqué par SQLPro. J'ai trouvé cet article très intéressant.


A+

[SQLpro]

Récrivez comme suit :

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
CREATE PROC MaPro @GRRE_Cod         TINYINT,   
                  @CECP_OrgDes      SMALLINT,  
                  @ENBO_SITE_CodFac SMALLINT
AS
 
DECLARE  @GRRE_Cod2         TINYINT,   
         @CECP_OrgDes2      SMALLINT,  
         @ENBO_SITE_CodFac2 SMALLINT  
 
SELECT @GRRE_Cod2         = @GRRE_Cod,
       @CECP_OrgDes2      = @CECP_OrgDes,
       @ENBO_SITE_CodFac2 = @ENBO_SITE_CodFac;
 
SELECT TOP 1
       ENBO.ENBO_Lot  
FROM   dbo.ENBO_EnteteBordereauTP AS ENBO 
       INNER JOIN  dbo.CECP_CentreCPAM AS CECP 
	         ON     ENBO.ENBO_CECP_GRRE_CodFac  = CECP.CECP_GRRE_Cod  
		        AND ENBO.ENBO_CECP_CPAM_CodFac  = CECP.CECP_CPAM_Cod  
		        AND (ENBO.ENBO_CECP_CodFac      = CECP.CECP_Cod OR ENBO.ENBO_CECP_CodFac IS NULL)  
WHERE  CECP.CECP_GRRE_Cod      = @GRRE_Cod2  
  AND  CECP.CECP_OrgDes        = @CECP_OrgDes2  
  AND  ENBO.ENBO_SITE_CodFac   = @ENBO_SITE_CodFac2  
  AND  ENBO.ENBO_Lot           IS NOT NULL  
ORDER  BY ENBO.ENBO_DatBor DESC,
          CAST(ENBO.ENBO_Lot AS int) DESC;

1) reparamétré les variables
2) retirer les WITH NOLOCK, cela donne des résultats FAUX !
3) utilisez le niveau d'isolation SAPSHOT si besoin est :
SET TRANSACTION ISOLATION SNAPSHOT;

Rajoutez l'option WITH (RECOMPILE) si cela n'a pas amélioré la chose...

A +

PS à l'avenir postez votre procédure telle qu'elle est et non un extrait rectifié !

[dlouvet69]

Je ne comprends pas pourquoi et dans quelle mesure les WITH NOLOCK donnent des résultats faux ? Vous parlez bien de performances pas de sémantique ?
(En fait nos requêtes en sont truffées lorsque coté résultats nous en assumons les conséquences).

[Invité]

Je ne comprends pas pourquoi et dans quelle mesure les WITH NOLOCK donnent des résultats faux ? Vous parlez bien de performances pas de sémantique ?
(En fait nos requêtes en sont truffées lorsque coté résultats nous en assumons les conséquences).

https://www.mssqltips.com/sqlservert...r-nolock-hint/