Procédure stockée anormalement lente [Résolu]

[james_usus]

Bonjour ,

J'ai une base de donnée sql server qui roulait parfaitement sous sql server 2000, maintenant que je l'ai faite migré a 2005 SP4 , une procédure stockée met 20 mn pour s'executer , alors qu'elle mettait quelques secondes sous sql 2000 , comment y remédier ? merci pour vos aides ,

[Jinroh77]

Toujours, toujours le même genre de demandes et pourtant personne ne comprend qu'avec un simple "c'était mieux avant" on ne peut strictement rien y faire.
On reprend, comme d'habitude :
- config des 2 machines si différentes.
- DDL des tables utilisées
- script de la procédure
etc.

Pourquoi ne comprend que ce genre de demande sans aucune information n'a aucun sens

[SQLpro]

Lors de votre migration, avez-vous pensé à :
1) reconstruire tous les index,
2) recalculées toutes les statistiques de table (_WA_Sys...)
?

Si non, commencez par cela.

A +

[james_usus]

Envoyé par SQLpro

Lors de votre migration, avez-vous pensé à :
1) reconstruire tous les index,
2) recalculées toutes les statistiques de table (_WA_Sys...)
?

Si non, commencez par cela.

A +

Merci de vous intéresser a mon sujet
Je vais tenter de le faire , et je vous tiendrais au courant du résultat

[james_usus]

Envoyé par SQLpro

Lors de votre migration, avez-vous pensé à :
1) reconstruire tous les index,
2) recalculées toutes les statistiques de table (_WA_Sys...)
?

Si non, commencez par cela.

A +

J'ai reconstruis tous les index

J'ai recalculé toutes les stat de table

j'ai fais un gain de quelques minutes sans plus , la procédure s’exécute en 12mn , ce qui est excessivement long , merci de m'indiquer d'autres pistes éventuelles.

[elsuket]

Bonjour,

Sans le plan d'exécution réel, impossible de vous aider plus.

@++

[james_usus]

Voici le plan d’exécution tel qu'il est représente lors de l'affichage.
désole pour le retard car alité .
je vous remercie pour toute vos aides

[SQLpro]

Ayez au moins un peu de respect pour ceux qui vous aide et postez le texte intégral de votre procédure !!!!

Sinon, quelques conseils :

A moins que ce ne soit des vues, créez les index suivants :

Code :

1
2
3
4
5
CREATE INDEX X_IOB_PERM ON IOB (Permission) WITH (FILLFACTOR = 90);
CREATE INDEX X_Titre_Seance_FIX_NOS ON Titre_Seance (Fixing) INCLUDE ([no_Seance])  WITH (FILLFACTOR = 90);
CREATE INDEX X_Ordres_NOS_TIT  ON Ordres (no_Seance, Titre) WITH (FILLFACTOR = 90);
CREATE INDEX X_PseudoA_VOL ON PseudoA (Volume) WITH (FILLFACTOR = 90);
CREATE INDEX X_PseudoV_VOL ON PseudoV (Volume) WITH (FILLFACTOR = 90);

le coup de COUNT(*) + 1 comme auto incrément est d'une haute stupidité.
Veuillez lire ceci : http://sqlpro.developpez.com/cours/clefs/
Changez votre code.

L'opérateur différence est <> et non !=, exemple

Code :

IF ( SELECT SUM ( Volume ) FROM PseudoA ) !=  ( SELECT SUM ( Volume ) FROM PseudoV )

récrivez ceci avec NOT EXISTS et un AND :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
IF EXISTS ( SELECT *     
              FROM Ordres Ord1
              INNER JOIN Transactions
              ON Ord1.Ref_Ordre = Transactions.Ref_Ord_Acheteur       
              GROUP BY Ord1.Ref_Ordre, Ord1.Volume
              HAVING Ord1.Volume < SUM ( Transactions.Volume ) ) OR
     EXISTS ( SELECT *     
              FROM Ordres Ord1
              INNER JOIN Transactions
              ON Ord1.Ref_Ordre = Transactions.Ref_Ord_Vendeur 
              GROUP BY Ord1.Ref_Ordre, Ord1.Volume
              HAVING Ord1.Volume < SUM ( Transactions.Volume ) )

je ne suis allé que jusqu'au 15e plan...

[james_usus]

Désole mille merci pour vos efforts , je vous publie l’intégralité de la procédure

[elsuket]

Bonjour,

Envoyé par SQLPro

Code :

CREATE INDEX X_Titre_Seance_FIX_NOS ON Titre_Seance (Fixing) INCLUDE ([no_Seance])  WITH (FILLFACTOR = 90);

Je pense qu'il vaudrait mieux que la table Titre_Seance ait un index cluster sur la colonne no_Seance.
D'autre part la colonne Fixing ne semble pas très sélective (peu de valeurs distinctes).
En effet il semble que cette table n'en ait pas (par conséquent pas de clé primaire ni de contrainte d'unicité) puisque le plan montre un RID Lookup.
Placer un index cluster (si c'est possible !) permettrait d'avoir un clustered index seek

Un index ayant pour clé Titre et comme colonne incluse cote aiderait les requêtes n°6, 7 et 8 si un index cluster sur le numéro de séance est possible.

J'ai une question pour le plan n°12 : quelqu'un peut-il m'expliquer pourquoi le Table Spool explose la cardinalité ?
Je rencontre parfois ceci sans comprendre ce qui se passe, et je n'aime pas être dans le flou

Les plans de requête après le n°15 sont relativement similaires à ceux qui sont avant.
Donc en mettant les contraintes de clé primaire et d'unicité comme il se doit, vous devriez vous en sortir à peu près.

D'autre part il semble que la base de données sur laquelle vous avez testé ne contient pas un jeu de données suffisamment conséquent pour faire des tests proches d'un environnement de production.

@++

[james_usus]

Envoyé par SQLpro

Sinon, quelques conseils :
...
je ne suis allé que jusqu'au 15e plan...

J'ai appliqué tous les changements sans grands résultats malheureusement , la procédure continue d'être trop longue à s'exécuter .

Question : Pourquoi ce problème survient t'il seulement sur La version 2005, La 2000 la faisait fonctionné parfaitement

[james_usus]

Envoyé par elsuket

D'autre part il semble que la base de données sur laquelle vous avez testé ne contient pas un jeu de données suffisamment conséquent pour faire des tests proches d'un environnement de production.

La base est suffisamment peuplé pour faire les tests que l'on souhaite.

Pourquoi changer les contraintes de clé et d'unicité alors qu'elles marchaient à merveille sur les anciennes moutures Sql server , c'est pas comme si c'était une nouvelle base de donnée jamais testé, non ???

[Glouferu]

Bonjour,

Je n'avais pas répondu jusque la puisque j'avais peur de raconter une connerie, mais rajouter des index ne doit pas avoir d'effet sur vos temps de chargements car le nombre de ligne "estimé" est très faible.

Personnellement, j'ai plus l'impression que le problème vient de la configuration du Server SQL. Lors de la migration, des paramètres ont changé ?
Comme le nombre de CPU utilisé ? la parallélisation ? la mémoire ... etc ?
-> Avez-vous vérifié de ce coté la ?


Cdt,

[james_usus]

Envoyé par Glouferu

Bonjour,

Je n'avais pas répondu jusque la puisque j'avais peur de raconter une connerie, mais rajouter des index ne doit pas avoir d'effet sur vos temps de chargements car le nombre de ligne "estimé" est très faible.

Personnellement, j'ai plus l'impression que le problème vient de la configuration du Server SQL. Lors de la migration, des paramètres ont changé ?
Comme le nombre de CPU utilisé ? la parallélisation ? la mémoire ... etc ?
-> Avez-vous vérifié de ce coté la ?


Cdt,

Merci de vous intéresser au sujet,

J'ai testé la base sur le system hote windows 2008 R2 Sp1 64bits entreprise edition
Le serveur est un Hp proliant proc xeon e5520 @ 2.27 ghz , 8 coeurs , 12gigas Ram ,

j'ai effectivement modifié les entrées dans les proprietes de sql 2005 :
Max degree of parallelism =1
Je n'ai activé qu'1: CPU0

sans résultat

Testé aussi sur une console hyper-v windows 2008 32bits entreprise edition
Pour ce qui est de la console hyper-v , le problème ne se pose pas puisqu'il n'utilise qu'un seul coeur du processeur.

Merci de me donner des pistes de réflexions.

[SQLpro]

Quel est le contenu de sp_configure ?

Votre serveur est-il en hyperthreading ? (si oui, desactivez le)

A +

[james_usus]

Envoyé par SQLpro

Quel est le contenu de sp_configure ?

Votre serveur est-il en hyperthreading ? (si oui, desactivez le)

A +

resultat sp_configure


name minimum maximum config_value run_value
----------------------------------- ----------- ----------- ------------ -----------
allow updates 0 1 0 0
clr enabled 0 1 0 0
cross db ownership chaining 0 1 0 0
default language 0 9999 2 2
max text repl size (B) 0 2147483647 65536 65536
nested triggers 0 1 1 1
remote access 0 1 1 1
remote admin connections 0 1 0 0
remote login timeout (s) 0 2147483647 20 20
remote proc trans 0 1 0 0
remote query timeout (s) 0 2147483647 600 600
server trigger recursion 0 1 1 1
show advanced options 0 1 0 0
user options 0 32767 0 0


hyper-threading desactivé

[elsuket]

Bonjour,

Exécutez plutôt :

Code :

1
2
3
4
5
6
7
8
9
10
EXEC sp_configure 'show advanced options', '1'
GO
RECONFIGURE
GO
EXEC sp_configure
GO
EXEC sp_configure 'show advanced options', '0'
GO
RECONFIGURE
GO

Ou plus simple :

Code :

1
2
SELECT	*
FROM	sys.configurations

@++

[james_usus]

voici le résultat en pièce jointe

[james_usus]

Resultat en pièces jointe de SELECT *
FROM sys.configurations

[james_usus]

Personne pour me donner une solution ???