Discussion :

[mic.gil]

Bonjour,

J'ai le problème suivant :

Sur un serveur SQL2005, comportant environ 20 bases de données, j'exécute une requête et les temps de réponse de la requête sont très différents (10s vs plusieurs minutes) suivant le contexte dans lequel je me situe(USE nomdedb),

Les chemin d'accès sont différents : Hash Match (réponse 7s) et Inner Join (réponse plusieurs minutes).

Le contexte posant problème est celui qui héberge les tables de la requête.

Merci pour votre aide

[aieeeuuuuu]

Bonjour,

S'agit-ils exactement de la même requête ? (est-ce que vous nommez vos noms de table en trois ou quatre partie, c'est à dire avec le nom de la base ?)

En lançant dans des contextes différents, vous générez à chaque fois une nouvelle compilation d'un plan d'éxécution. Vous pouvez (et visiblement c'est le cas) avoir deux plans différents.

Êtes-vous sur un serveur de test ? Si c'est le cas, vous pouvez ajouter OPTION(RECOMPILE) à la fin de la requête afin de forcer une recompilation de la requête et voir si le résultat est meilleur.

[mic.gil]

Merci beaucoup pour votre réponse : Le paramètre OPTION(RECOMPILE) résout bien le problème de cette requête.

Mais je ne peux modifier ces requêtes en environnement de Production.

Ainsi, comment puis je faire pour être sûr d'utiliser les meilleurs chemins d'accès, notamment en cas de modifications (ajout d'index dans mon exemple)? un DBCC FREEPROCCAHE régulièrement ? un arrêt/relance du serveur réinitialise t il tout? Autres?

(En réponse à vos questions : les noms de tables sont préfixés par le nom de la DB et les requêtes sont vraiment identiques (copie/coller dans le gestionnaire de requête)).

Merci

[mic.gil]

Bonjour,

en comparant le paramétrage des bases, j'ai vu une différence dans le paramètre 'PARAMETERIZATION '

Dans le contexte DB où j'ai PARAMETERIZATION SIMPLE, le chemin d'accès est bon.
Dans le contexte DB où j'ai PARAMETERIZATION FORCED, le chemin d’accès est défaillant.

J'ai modifié ce paramètre (ALTER DATABASE BINFP SET PARAMETERIZATION SIMPLE) et, effectivement, la requête s'est déroulée instantanément.

Il ne me reste plus qu'à analyser l'impact de ce paramètre (impact que j'espère négatif) pour le reste des requêtes sur cette DB.

Merci beaucoup pour votre aide

[mikedavem]

Hello,

L'avantage de cette option est de forcer SQL Server à "paramétriser" les plans d'exécutions pour qu'ils puissent être ultérieurement réutilisés mais cela peut également devenir un problème si le plan d'exécution généré n'est optimisé que pour un sous ensemble de valeurs de paramètres correspondants (SQL Server sniffe les valeurs de paramètres pour compiler le plan d'exécution).

Le fait de supprimer cette option de base de données peut améliorer ta requête mais peut aussi avoir un effet négatif comme tu le cites sur les autres requêtes et sur la taille du cache dédié à cela (compiled plan / adhoc).

++

[aieeeuuuuu]

Il y a aussi d'autre options.
Par exemple
- Il est envisageable de laisser le hint OPTION (RECOMPILE) pour cette requête spécifiquement, surtout si son cout de compilation est faible...
- En fonction de la façon dont la requête est/sera appelée, il est aussi possible d'utiliser OPTIMZE FORliste non exhaustive !

Est-il possible de voir la requête en question ?
Et un peu de détail sur le contexte, la volumétrie, ...

[mikedavem]

Il y a aussi d'autre options.

Effectivement mais visiblement notre ami mic.gil ne peut pas toucher au code si j'ai bien compris.

Mais je ne peux modifier ces requêtes en environnement de Production.

... à moins qu'il soit possible de faire modifier la requête par l'éditeur ou l'équipe de dev on peut voir un peu plus en détail la requête en question.

++

[aieeeuuuuu]

Bien vu !

Cela dit, il reste peut-être possible d'obtenir le même effet avec un guide de plan ?

Mais dans un premier temps, il faudrait comprendre précisément ce qui cause cette lenteur, et pour cela, la requête en question et son plan d’exécution réel seraient utiles. On peut supposer que la requête contient un filtre sur une valeur remarquable, mais ça reste à confirmer.

[mikedavem]

Cela dit, il reste peut-être possible d'obtenir le même effet avec un guide de plan ?

J'y ai effectivement aussi pensé mais je pense que ta seconde phrase dit le reste : il faut connaître un peu plus contexte ...

++

[mic.gil]

Bonjour, merci pour ces réponses.

Quelques précisions :
Le serveur SQL est un serveur dédié à notre Infocentre (on n'est donc pas à des temps de réponse au 10éme de seconde prés)
Après avoir modifié le paramètre Parameterization, j’attends un "retour" de l'équipe Infocentre qui doit lancer une batterie de requêtes et me dire si ça tient la route.
Quand la requête "déconne", ça met à plat le serveur SQL (CPU/mémoire) le temps de son exécution (12mn).

La table concernée dans la requête fait environ 600 000 lignes d'environ 400 Octets et a 5 indexes (1 index cluster UNIQUE sur les colonnes CO_POS_PRINC, MATRICULE, LIEN_FAM et des indexes de performance)
Voici la requête qui est en fait générée par notre outil d'Infocentre (BO) (c'est pour cela que l'équipe Infocentre de chez moi ne veut pas la modifier), et ... ne m'en veuillez pas sur le code...

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
38
39
40
41
42
 
SELECT
  BINFP.dbo.RACINE_TOT.MATRICULE,  BINFP.dbo.RACINE_TOT.NOM_PERSONNE,  BINFP.dbo.RACINE_TOT.NOM,  BINFP.dbo.RACINE_TOT.PRENOM_1,  BINFP.dbo.RACINE_TOT.SEXE,  BINFP.dbo.RACINE_TOT.DA_NAISSANCE,
  substring(BINFP.dbo.RACINE_TOT.NOM_PERSONNE,1,1)
FROM
  BINFP.dbo.RACINE_TOT
WHERE
  (
   BINFP.dbo.RACINE_TOT.LIEN_FAM  =  '  '
   AND
   BINFP.dbo.RACINE_TOT.DA_NAISSANCE  NOT IN  ( 0, 19010101  )
   AND
   BINFP.dbo.RACINE_TOT.DATE_DECES  =  0
   AND
   (
    BINFP.dbo.RACINE_TOT.MATRICULE  BETWEEN  0  AND  299999
    OR
    BINFP.dbo.RACINE_TOT.MATRICULE  BETWEEN  500000  AND  799989
   )
   AND
   ( BINFP.dbo.RACINE_TOT.CO_POS_PRINC='1'  )
   AND
   BINFP.dbo.RACINE_TOT.SEXE +' '+ substring(BINFP.dbo.RACINE_TOT.NOM_PERSONNE,1,1) +' '+ convert(char,BINFP.dbo.RACINE_TOT.DA_NAISSANCE)  IN  
     (
     SELECT
       BINFP.dbo.RACINE_TOT.SEXE +' '+ substring(BINFP.dbo.RACINE_TOT.NOM_PERSONNE,1,1) +' '+ convert(char,BINFP.dbo.RACINE_TOT.DA_NAISSANCE)
     FROM
       BINFP.dbo.RACINE_TOT
     WHERE
       (
        BINFP.dbo.RACINE_TOT.LIEN_FAM  =  '  '
        AND
        BINFP.dbo.RACINE_TOT.DA_NAISSANCE  NOT IN  ( 0, 19010101  )
        AND
        BINFP.dbo.RACINE_TOT.DATE_DECES  =  0
        AND
        BINFP.dbo.RACINE_TOT.MATRICULE  BETWEEN  800000  AND  899999
        AND
        ( BINFP.dbo.RACINE_TOT.CO_POS_PRINC='1'  )
       )
     )
  )

les chemins d'accès sont en fichiers joints...

[aieeeuuuuu]

Ce sont les plans d’exécution estimés et non réels... néanmoins, en comparant les deux, on peut supposer que c'est la dernière clause de votre filtre (celle avec la sous requête) qui pose problème... En fait, c'était visible avant même de regarder les plan
Elle mériterait d'être réécrite avec un EXISTS.
Chose qu’apparemment vous ne pouvez pas faire... Peut-on savoir pourquoi, en fait surtout savoir ce que vous pouvez ou ne pouvez pas faire (il est étonnant, et dommage, que vous puissiez modifier un tel paramètre du serveur comme PARAMETRIZATION, mais pas modifier une requête)...

Même si ça ne sera pas la solution idéale, on pourrait envisager la création d'une colonne calculée et/ou d'une vue indexée reprenant la sous requête...

[mikedavem]

Effectivement la requête mériterait d'être réécrite je pense.
On a pas le plan d'exécution réel mais je dirais la même chose que aieeeuuuuu. La sous requête utilisée en filtre peut poser problème ici. En effet la table dbo.RACINE_TOT est lue 2 fois avec un certain nombre de critères, ce qui peut amener SQL Server à spooler cette table dans tempdb pour ne pas avoir à la relire une seconde fois dans certains cas (volume de données à lire important par exemple).

++

[mic.gil]

Je comprends votre étonnement concernant notre organisation :
Je suis le DBA (+ DB2 que MSSQL) mais je ne maitrise pas les requêtes générées par l’outil qu’utilisent mes collègues (charmantes en l’occurrence) de la cellule Infocentre qui elles-mêmes ne maitrisent pas le SQL généré...

J’avais déjà pensé à intercepter l’ordre SQL de la requête et changé le sous select en une clause EXISTS : résultat OK, mais … impossible à modifier dans l’outil…

Aujourd’hui, avec PARAMETERIZATION SIMPLE, le problème de la requête est résolu, l’ensemble des autres requêtes continue à fonctionner correctement (les éventuels temps supplémentaires de recompile sont négligeables dans l’environnement Infocentre), il me reste à surveiller certains paramètre comme l’utilisation du Cache par exemple.

C’est la première fois que j’interviens sur votre site et je vous remercie beaucoup pour votre intervention.

Un utilisateur satisfait…

[elsuket]

Bonjour,

La première chose qui saute aux yeux, c'est effectivement la qualité de la requête.
Réécrivons la pour la rendre un peu plus lisible :

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
SELECT	 MATRICULE
	, NOM_PERSONNE
	, NOM
	, PRENOM_1
	, SEXE
	, DA_NAISSANCE
	, SUBSTRING( NOM_PERSONNE, 1, 1)
FROM	BINFP.dbo.RACINE_TOT
WHERE	LIEN_FAM = ' '
AND	DA_NAISSANCE NOT IN (0, 19010101)
AND	DATE_DECES = 0
AND	(
		MATRICULE BETWEEN 0 AND 299999
		OR MATRICULE BETWEEN 500000 AND 799989
	)
AND	CO_POS_PRINC = '1'
AND	SEXE + ' ' + SUBSTRING( NOM_PERSONNE, 1, 1) + ' ' + CONVERT(char, DA_NAISSANCE) IN 
	(
		SELECT	SEXE + ' ' + SUBSTRING(NOM_PERSONNE, 1, 1) + ' ' + CONVERT(char, DA_NAISSANCE)
		FROM	BINFP.dbo.RACINE_TOT
		WHERE	LIEN_FAM = ' '
		AND	DA_NAISSANCE NOT IN (0, 19010101)
		AND	DATE_DECES = 0
		AND	MATRICULE BETWEEN 800000 AND 899999
		AND	CO_POS_PRINC = '1'
	)

Le prédicat de filtre suivant :

Code :

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

1
2
3
SEXE + ' ' + LEFT( NOM_PERSONNE, 1) + ' ' + CONVERT(char, DA_NAISSANCE) IN 
	(
		SELECT	SEXE + ' ' + SUBSTRING(NOM_PERSONNE, 1, 1) + ' ' + CONVERT(char, DA_NAISSANCE)

est très clairement inchercheable. En effet SQL Server sait estimer la probabilité qu'une valeur soit dans une colonne. En revanche, il ne sait pas ce qui est dans 3 colonnes, sauf si celles-ci sont indexées par le même index, ou que l'expression constitue une colonne calculée, laquelle est possiblement indexée.
Ajoutons à cela le OR et le NOT IN : celà n'arrange pas la chose.

On pourrait donc ajouter une colonne calculée et l'indexer, mais il y a autre chose : seul le plan sous-optimal parallélise.
Plus curieux, l'arbre des résolution est le même dans les deux requêtes, à l'exception du Table Spool et de l'algorithme de jointure final.
C'est à dire, le coût et les cardinalités de chaque opérateur à l'exception de ceux cités ci-avant sont strictement identiques.

De plus, si on analyse les deux plans avec SQL Sentry Plan Explorer, le plan sous-optimal montre les paramètres (l'onglet est présent), mais pas le plan optimal (l'onglet est désactivé). Ceci montre donc que le plan sous-optimal est généré avec la base de données à paramétrisation forcée, et pas le plan optimal.

C'est donc que vous exécutez la requête dans deux bases de données différentes, bien que vous référenciez la même.
Cela indique-t-il que vous avez un plan correct en DEV et pas en PROD par exemple ?

P.S. je viens de discuter de tout cela avec Mikedavem.

@++

[mic.gil]

Bonjour,
en fait, j'exécute (ou fais l'explain de) la requête dans la même base en switchant simplement le paramètre PARAMETERIZATION de " FORCED" (comme il l'était initialement) à "SIMPLE" etc.
PS : parmi toutes les bases que l'on gère, c'est la seule Base qui était en mode FORCED
A+