Discussion :

[SoaB]

Bonjour à tous,

Voici un problème que je rencontre que je n'arrive pas à comprendre sous SQL Server.

On a changé d'hébergeur, et on est donc passés d'un SQL Server 2005 SP3 à un SQL Server 2012 SP2.
Au passage les serveurs ont pris un sacré boost de puissance ou capacité (CPU/Mémoire)

Mais je rencontre un problème sur certaines requêtes bien précises à base de Pivot dans des sous requetes

Exemple :

Code :

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

1
2
3
4
SELECT *
FROM TABLE
LEFT JOIN (SELECT BIDULE PIVOT (MACHIN) ) ON ID =ID
WHERE MyConditions

Avant, c'était long => 5/6 sec d'exécution
Maintenant c'est très long => 25 sec minimum

Quand on regarde le serveur on voit que le CPU du serveur à est à fond quand ça tourne.

Mais j'ai trouvé une parade

Code :

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

1
2
3
4
5
6
7
8
9
 
SELECT BIDULE INTO #TEMP PIVOT (MACHIN)
 
SELECT *
FROM TABLE
LEFT JOIN #TEMP  ON ID =ID
WHERE MyConditions
 
DROP TABLE #TEMP

En faisant ça, le temps tombe à moins d'1 sec la requête donc jackpot.

Mais je ne comprends pas pourquoi la 1ère version chute en performance par rapport aux anciens serveurs.
Les données ont été reprises, donc sont identiques en valeur et en nombre de lignes.


Merci d'avance pour votre aide.

Cdt,
Vincent.

[aieeeuuuuu]

Bonjour,

Il nous faudrait les plans d’exécution des deux requêtes pour pouvoir mieux analyser le problème, mais une cause probable serait des statistiques qui ne sont pas à jour. commencez par mettre à jour vos statistiques et dites nous ce que ça donne.

[SoaB]

On a déjà fait plusieurs choses

- DBCC FREEPROCCACHE
- EXEC sp_updatestats;

ET hier un plan de maintenance a été exécuté pour statistiques et rebuild des index.


Sinon comment vous transmettre les plans d'exécutions ?

[aieeeuuuuu]

Sinon comment vous transmettre les plans d'exécutions ?

Vous pouvez l'enregistrer en XML et le poster :
Sous Management studio, faites afficher le plan d’exécution réel (CTRL + M)
éxécutez vos deux requêtes
Dans la fenêtre du plan d’exécution, faites un clic-droit, puis enregistrer sous...

[SoaB]

Voila la pièce jointe au format XML.

PlanProcStoredMensSuiviQualite.zip

Merci beaucoup

[mikedavem]

On a déjà fait plusieurs choses

- DBCC FREEPROCCACHE
- EXEC sp_updatestats;

A savoir que sp_updatestats dans ce cas ne met à jour que les statistiques des tables en se basant sur la valeur de rowmodctr. C'est à dire qu'une table dont les valeurs n'ont pas été modifié ne seront pas prises en compte par cette procédure. Par ailleurs cette même procédure se base sur l'échantillon par défaut des statistiques existantes. Je te conseille d'essayer de faire un UPDATE STATISTICS FULLSCAN sur les tables concernés par tes requêtes dans un premier temps pour voir si cela change quelque chose chez toi.

++

[mikedavem]

D'ailleurs si on regarde ton plan d'exécution il semblerait qu'un problème de d'estimation de cardinalité existe sur ta table TStatusFichier. Je commencerais par remettre à jour les statistiques de cette table en mode FULLSCAN pour commencer qui donnera lieu à un meilleur plan d'exécution probablement. Après on peut toujours en redire sur la requête mais je pense qu'il y a matière à creuser sur les statistiques pour le moment. A vérifier ...



++

[SoaB]

Bonjour à vous et Grand merci pour votre aide sur ce problème.

Ce matin j'ai donc effectué l'update des statistics des différentes tables

Code :

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

1
2
UPDATE STATISTICS [MATABLE] WITH FULLSCAN

Sur les tables suivantes :

STF_tFichier
STF_tStatutFichier

ainsi que les MBU* + REF

Ca ne change pas grand chose.
M'ai je trompé quelque part ?

[mikedavem]

Le nouveau plan d'exécution suite à ta mise à jour?

++

[SoaB]

Le nouveau plan d'exécution suite à ta mise à jour?

++

Ah oui au temps pour moi.

Voir PJ .
PlanProcStoredMensSuiviQualite2.zip

Merci beaucoup


Edit :
En fait y a du mieux, c'est à 10 sec maintenant comme sur l'ancien serveur de prod.

Mais j'ai réussi à optimiser la requête en mettant dans des tables temporaires, les 2 sous requêtes sous PIVOT.
Je tombe à 1sec max.

[mikedavem]

Petite question tout de même

Bon hormis toutes les vues que tu utilises je vois ceci dans la première sous requête:

Code :

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

1
2
INNER JOIN CNSBTP..STF_tStatutFichier STF ON STF.IdFichier = VDS.IdFichier        --Permet de récupérer la date de validation du bordereau
INNER JOIN CNSBTP..STF_tRefStatut REFS ON REFS.ID = STF.IdRefStatut AND REFS.TxtCodeStatut= 'BRD_VALID' --Permet de récupérer la date de validation du bordereau

On voit que l'optimiseur essaie de passer par un clustered index scan pour la table STF_tStatutFichier. As-tu un index pertinent sur ta table STF_tStatutFichier sur la colonne IdFichier qui couvrirait également la colonne STF.DteStatut si je lis bien ta requête?

++

[SoaB]

Index sur clé primaire uniquement.

[mikedavem]

Edit :
En fait y a du mieux, c'est à 10 sec maintenant comme sur l'ancien serveur de prod.

Mais j'ai réussi à optimiser la requête en mettant dans des tables temporaires, les 2 sous requêtes sous PIVOT.
Je tombe à 1sec max.

Bon il y avait du mieux alors avec la mise à jour des statistiques :-)
Possible que dans ton cas les tables temporaires aident un peu vu le nombre de table spool que tu as dans ton plan d'exécution. Ceci dit garde à l'esprit que PIVOT force l'optimiseur à adopter une stratégie proche du curseur.
Il serait intéressant de voir le plan d'exécution avec les tables temporaires.

Index sur clé primaire uniquement.

Etonnant car ce n'est pas ce que dis ton plan d'exécution :-)



Ok j'avais pas bien vu ton plan d'exécution.

Tu as visiblement quelques vues dont il sera bon d'en connaître la définition:

cnsbtp..STF_vDernierStatut
tablesref..REF_vSuiviTypeLigne

Aurais-tu leur éventuellement leur définition?



++

[SoaB]

Bonjour,


Voici les 2 vues zippées :
DoubleVues.zip

J'ai revérifié, J'ai effectivement un index sur IdFichier, DteStatut.

[SQLpro]

Essayez en créant l'index :

Code :

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

1
2
3
4
5
USE [cnsbtp]
GO
CREATE NONCLUSTERED INDEX X_SQLpro_STF_IRS_I_IFC
ON [dbo].[STF_tStatutFichier] ([IdRefStatut])
INCLUDE ([IdFichier])

A +

[aieeeuuuuu]

et celui-ci :

Code :

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

1
2
3
4
 
CREATE INDEX [NomIndex]
	ON dbo.STF_tRefStatut(ID)
	WHERE TxtCodeStatut = 'DEPOT'

Dans votre vue, STF_vDernierStatut, l’utilisation des fonctions de classement pourrait éviter vos trois jointures sur la table STF_tStatutFichier.
A tester pour voir si cela améliorera les perfs...

[SoaB]

Merci beaucoup à vous de prendre du temps pour m'aider.

Alors les 2 index me font gagner quelques secondes bien venues

Je vais regarder du côté des fonctions de classement.

Le nouveau plan d'exécution :
PlanProcStoredMensSuiviQualite3.zip


EDIT :
Je ne vois absolument pas comment récupérer la colonne IdRefStatut en fonction du Max(ID) de la table STF_tRefStatut avec des fonctions de classement.

[mikedavem]

Peut être quelque chose comme cela:

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
43
44
-- grant select on [dbo].[STF_vDernierStatut]  to usr_cnsbtp
CREATE VIEW [dbo].[STF_vDernierStatut] 
WITH SCHEMABINDING
as
select 
			F.ID AS IdFichier,
			F.IdTypeFichier,
			F.TxtNomFichier,
			TF.TxtRacine,
			F.IdOrganisme,
			ORG.NumOrg as NumCaisse,
			F.TxtPeriode,
			F.NbOctetFichier,
			F.NbLigneFichier, 
			F.NumGeneration,
			SF.IdRefStatut AS IdDernierStatut,	
			SF.DteStatut,
			RS.TxtCodeStatut,
			RS.IndNiveauErreur,
			F.TxtNomArchive,
			TF.IdMethodeTransfert,
			TF.TxtLibelleLong,
			TxtSens,
			RS.TxtLibelle as TxtLibelleRefStatut,
			IndInt,
			TxtTypeFichierCode,
			TxtChaine,
			TxtRepArchive,
			F.IndIgnore,
			F.TxtIgnore
			--SF2.DteStatut AS DteDepot
from dbo.stf_tfichier F 
inner join (select   row_number() over (partition by idfichier order by id desc) as num,
			 idfichier, 
			 id as IdDernierStatut,
			 IdRefStatut,
			 DteStatut
			from dbo.stf_tstatutfichier) SF on (SF.Idfichier = f.id and SF.num = 1)
inner join dbo.STF_tRefStatut RS on (RS.ID = SF.IdRefStatut)
inner join dbo.stf_ttypefichier TF on (tf.Id = f.idtypefichier)
inner join dbo.stf_trefmethodetransfert rmt on (rmt.id = tf.idmethodetransfert)
inner join dbo.ALL_tOrganisme ORG ON ORG.ID = F.IdOrganisme
WHERE RS.TxtCodeStatut = 'DEPOT' 
GO

La seule chose que je ne comprends pas c'est pourquoi avoir mis 2 fois dbo.STF_tRefStatut avec 2 alias SF1 et SF2. De base de ce que je comprends du modèle c'est que de toute façon on va se retrouver uniquement avec les derniers statuts de fichiers donc le label est DEPOT. A ce moment là, il suffit d'utiliser une seule fois dbo.stf_tstatutfichier. Bon vu l'heure je ne réfléchis plus comme il faut .. donc on verra demain

++

[SoaB]

Le but c'est d'avoir la date de dépot du fichier, ainsi que les données du dernier statut du fichier qui n'est pas forcément la date de dépôt. (1ère date du fichier)

Effectivement je note la méthode row_number avec une jointure basé sur un bon order by. je regarde ça

Edit : Ta méthode est plus longue. 8 sec au lieu de 6 sec.
Mais en regardant l'utilité de la vue dans le cadre de notre appli, je peux déjà créer une autre vue identique mais déjà filtrer par un certain type de donnée afin de garder les 2400 lignes qui nous intéressent parmi les 1.500.000 lignes totales.
De 6 sec on passe à moins de 1 sec forcément :p