Discussion :

[fdenis31]

Bonjour,
je suis face à un problème (important) de temps de réponse sur une requête SQL issue d'un générateur B.O.

Nous avons fait évoluer B.O., aujourd'hui sous 2008 R2, et fait suivre ses bases de travail ainsi que la base de données de notre observatoire économique sur SQL Server 2008 R2, anciennement sous SQL 2000.

J'ai lancé la requête dans SQL Server Management studio ;
elle met 39 secondes pour afficher les 294 lignes !

La requête est issue des univers de BO, dans lesquels les liens entre tables visitées ne respectent pas forcément ceux qui existent dans la base.

j'ai donc pris ma requête et l'ai exécutée sur l'ancien serveur de base de données, sous SQL 2000 ; 1 seconde !

J'avoue ne pas comprendre une telle différence.
La requête est en pièce jointe mais je ne pense pas qu'elle soit à l'origine du pb, il me semble que les temps de réponse sur SQL 2000 seraient longs aussi.

Si quelqu'un a eu le problème, ou du moins une idée de son origine, j'avoue être preneur.
Merci d'avance pour vos réponses

[elsuket]

Bonjour,

Est-ce que lorsque vous avez mis en route la base de données sous SQL Server 2008, vous avez forcer le recalcul de toutes les statistiques de colonnes ?

Sans la définition des tables mises en jeu, ou encore mieux du plan de requête réel il sera difficile de vous aider.

Pour le capturer, un petit CTRL + M avant d'exécuter la requête, qui quand elle sera terminée affichera un onglet supplémentaire dans la console, dans lequel de trouve le plan d’exécution.

De là vous cliquez dans une zone vierge de celui-ci, et vous choisissez "sauvegarder le plan en tant que ..." et vous postez ici

Si vous voulez le voir un peu mieux, je vous invite à lire cet article

@++

[fdenis31]

Bonjour, et merci pour votre réponse.

J'ai lancé la mise à jour des statistiques (sp_updatestats), pensant avoir un début de solution mais cela n'a rien fait. je joins le rapport.
Certes des index ont été mis à jour mais sans affecter ni améliorer les temps de réponse.

J'ai relancé la requête avec le plan d'exécution réel, je joins aussi le rapport.

à plus,

[elsuket]

A partir du plan de requête, on voit aisément qu'il y a un problème d'estimation :



Cela est dû au prédicat COMCOD <> '000'.
En effet SQL Server sait grâce aux statistiques de colonne les valeurs qui se trouvent dans la colonne, et à quelle fréquence (statistique) elle s'y trouvent, mais pas quelles valeurs n'y sont pas ...

Donc vous pouvez créer un index filtré avec cette condition; en revanche je ne comprends pas cette différence entre SQL 2000 et SQL Server 2008.
Pourriez-vous donner le plan sous SQL Server 2000 ?

@++

[fdenis31]

Bonjour,

je ne demande pas mieux que de vous fournir mon plan d'exécution, mais sous SQL2000, aucune possibilité ne m'est offerte pour le sauvegarder.

Même sous SQL2005, connecté au moteur de base de données du serveur SQL2000, la sauvegarde est impossible.

Je peux vous fournir le plan sur cette instance SQL2005, mais le problème de temps de réponse y est identique, la requête met 40 s pour afficher les enregistrements de la base créée en compatibilité 2000.

Que faire docteur ?

@+

[Glouferu]

Bonjour fdenis31,

Je n'explique pas la différence d’exécution entre ton Server 2000 et 2005.

Néanmoins, j'ai remarqué que les gens qui réalisent des rapports BO avec des conditions du genre NOT IN, NOT IN ALL, <> (qui correspondent sous Deski à PAS DANS LISTE ... etc) génèrent des requêtes SQL assez compliqués pour le serveur SQL qui n'est pas tout le temps capable de répondre dans des délais raisonnables.
-> Du coup, pour contourner le problème, tu ramènes toutes tes valeurs et tu fais un filtre d'affichage dans ton rapport.
Et l’exécution devient instantané.

Je sais... ce n'est pas glamour, mais ça permet de simplifier les requêtes générés par plusieurs fournisseurs de données imbriqués contenant des PAS DANS LISTE, eux-mêmes croisés avec d'obscur fichiers Excel !

Cdt,

[fdenis31]

Petit plus, j'ai au moins une image de l'écran à proposer.

On y voit bien que les estimations sont très différentes.

36700 correspond bien au nombre de lignes de la table scom au travers de la vue v_commune avec comcod<>'000'

j'ai fait un comptage et la table ACREACESS ne contient aucune ligne, avec la colonne comcod = '000'

La requête exécutée avec cette condition supprimée donne le résultat en 1 seconde .

Pourquoi SQL 2008 ne le gère pas aussi bien que SQL 2000 ?

Je n'ai pas la possibilité d'intervenir sur le générateur de requête de B.O.

N'y a-t-il rien à tenter, du côté des stats ?

@+

[Glouferu]

Avez-vous essayer de rajouter cet index :

Code :

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

1
2
3
4
5
6
USE [Toulouse]
GO
CREATE NONCLUSTERED INDEX IDX_TEST_SRDCCOM
ON [dbo].[SRDCCOM] ([RDCCOD])
INCLUDE ([REGCOD],[DEPCOD],[COMCOD])
GO

??


Moi ce qui m'étonne c'est que le moteur SQL estime remonter 36k lignes et que sur l'imprim écran de Elsuket, le moteur dit retourner réellement 112M de lignes non ? ... Il doit y avoir un problème sur l'index et ses statistiques.

[mikedavem]

Pouvez vous poster le détail des statistiques liés à votre table SCOM et index DEP_COM_FK

Code :

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

DBCC SHOW_STATISTICS (SCOM, DEP_COM_FK);

++

[fdenis31]

bonjour Glouferou,
j'ai créé l'index, pour test, et relancé la requête ; aucune amélioration.

Pour le résultat, je ne peux pas être derrière chaque gestionnaire qui, au travers de B.O., va créer les univers et faire ses propres liens sans rapport avec ceux existant dans la base de données.

Ils doivent se rapprocher au maximum de la structure de la base et en prendre connaissance le plus vite possible.

L'équipe a changé et il faudra un peu de temps pour qu'il prennent en compte les réalités du terrain.

J'ai fait un point avec eux et, si la table des communes SCOM contient 104 lignes avec le code commune à '000', c'est à des fins de cumuls dans la base de données.

Par contre, la table ACREACESS ne doit pas contenir de communes à '000'.

Le point soulevé par Nicolas est vérifié, c'est bien la cause de la lenteur de la réponse ; le prédicat enlevé sur cette table, tout fonctionne.

Seul bémol, créer un index filtré, pour répondre au problème, ouvre la porte à de multiples actions pour répondre aux incohérences des univers créés dans B.O.

La question reste posée ; pourquoi une telle différence entre SQL 2000 et 2008 ? La gestion des statistiques ?

cdt,

[fdenis31]

Bonjour David,

Merci pour votre intérêt.

Tout d'abord, n'hésitez pas à me reprendre sur ma façon de communiquer, je me sers plus volontier des prénoms que des surnoms.

Je vous joins le rapport, sous forme d'image, plutôt que sous la forme de 3 fichiers csv.

J'espère que cela apportera quelque chose.

Je continue, de mon côté, à enquêter du côté des modalités de mise-à-jour des stats.

@+

[mikedavem]

Que donne une création d'une statistique sur la colonne COMCOD sur le plan d'exécution SQL :

Code :

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

1
2
3
CREATE STATISTICS STAT_SCOM_COMCOD
ON dbo.SCOM (COMCOD)
WITH FULLSCAN;

Je n'ai pas vérifié la syntaxe excate .. corrigez au pire ...

Cependant pourrais-t'on avoir également les statistiques liés à l'exécution de la requête avec SET STATISTICS IO ON; et SET STATISTICS TIME ON; ?

++

[fdenis31]

Peut-être ai-je trouvé la cause, en attendant l'explication ;

En regardant la requête une énième fois, je me suis rendu compte que sur la ligne incriminée, celle qui contient "and ACREACESS.comcod!=000", la valeur n'était pas en alphanumérique.

Je l'ai transformé, en "and ACREACESS.comcod!='000'" et obtenu ma réponse dans la seconde

Comme je suis têtu, j'ai remis l'ancienne valeur pour vérifier, et suis revenu aux 36s de délai.

Je pensai SQL2008 plus souple, sur ce genre d'erreur, d'autant que l'éditeur ne décèle rien.

Ce non respect du type de données, dans la requête, ne semble pas gêner SQL2000.

Merci encore, je vais attendre un peu pour déclarer mon souci comme résolu.

En effet, s'il est possible de remédier au problème, par un paramétrage dans SQL2008, une mauvaise requête issue de B.O. pourrait ne plus avoir d'effets indésirables.

[Ptit_Dje]

On peut remarquer que le nombre de lignes retournees correspond au nombre de lignes estimees multiple par le nombre d'execution reelement executees:
36700 * 3077 = 112925900.

La difference ici vient de l'operateur precedent, le sort qui estime renvoyer une seule ligne et en renvoie en fait 3077.

Ces chiffres erronnes sont reportes de statistiques fausses (mauvaises estimations) venant des tables avant la lecture de la table SCOM qui elle retourne des donnees de statistiques correctes.

Revoyer les stats sur les autres tables (un update de toutes les stats avec un fulltable scan) et rebuilder vos index (si pas deja fait).

[mikedavem]

Moi ce qui m'étonne c'est que le moteur SQL estime remonter 36k lignes et que sur l'imprim écran de Elsuket, le moteur dit retourner réellement 112M de lignes non ? ... Il doit y avoir un problème sur l'index et ses statistiques.

Attention il faut faire la différence entre le nombre de lignes estimées et le nombre de lignes remontées par l'opération nested loop ou boucle imbriquée. Cette dernière exécute pour chaque ligne de l'ensemble intere (partie de droite) un scan de l'ensemble externe (en bas) soit 36700 * 3077 = 112925900 lignes remontées. On peut voir le nombre d'exécutions en passant la souris sur l'opérateur index scan (Actuel Execution).

La difference ici vient de l'operateur precedent, le sort qui estime renvoyer une seule ligne et en renvoie en fait 3077.

Si on regarde bien on peut remonter plus haut et on voit que l'estimation des cardinalités semblent ne plus être précise depuis le début. La différence entre le nombre estimé de lignes et le nombre actuel peut l'expliquer (dans la plupart des cas une grande différence indique un manque de précision dans les stats).

J'ai lancé la mise à jour des statistiques (sp_updatestats), pensant avoir un début de solution mais cela n'a rien fait. je joins le rapport.

A parti de 2005, la procédure sp_updatestats met à jour les statistiques en se base sur les valeurs de colonne rowmodctr dans sys.indexes. Pour faire simple, toutes les statistiques ne seront pas forcément mises à jour comme SQL Server 2000.

Comme le suggère Ptit_Dje je pense que vous devriez faire un rebuild d'index de vos tables concernés (qui mettront à jour les statistiques par la même occasion) et voir si cela résoud votre souci. Je ne garantis pas que cela solutionnera votre problème mais au moins vous partirez avec des index + statistiques "clean"

++

[fdenis31]

Bonjour,
Je vous joins le rapport d'exécution après correction du code (pour rappel, ACREACESS.COMCOD != '000').

D'après ce que je vois, tout a l'air normal.

J'ai créé la statistique STAT_COM_COMCOD sur SCOM Je joins aussi les rapports avec les statistiques IO ON et TIME ON : avec le code en erreur et après correction

Pour la mise à jour des statistiques sur l'ensemble des tables, je teste la commande suivante ;
exec sp_MSforeachtable 'UPDATE STATISTICS ? WITH FULLSCAN'

ça a l'air plutôt long, je lancerai la reconstruction des index après.

@+

[Glouferu]

j'ai créé l'index, pour test, et relancé la requête ; aucune amélioration.

Pour le résultat, je ne peux pas être derrière chaque gestionnaire qui, au travers de B.O., va créer les univers et faire ses propres liens sans rapport avec ceux existant dans la base de données.

Pensez bien à supprimer l'index inutile.
Pour vos utilisateurs, la seule solution que j'ai trouvé est d'activer le timeout des rapports Deski à 10min. Du coup, quand ils font des choses pas très catholiques, ils se prennent un message d'erreur dans les dents.

En regardant la requête une énième fois, je me suis rendu compte que sur la ligne incriminée, celle qui contient "and ACREACESS.comcod!=000", la valeur n'était pas en alphanumérique.

Je l'ai transformé, en "and ACREACESS.comcod!='000'" et obtenu ma réponse dans la seconde

Merci de l'info, je vais me servir de votre réponse pour regarder mes univers

Attention il faut faire la différence entre le nombre de lignes estimées et le nombre de lignes remontées par l'opération nested loop ou boucle imbriquée. Cette dernière exécute pour chaque ligne de l'ensemble intere (partie de droite) un scan de l'ensemble externe (en bas) soit 36700 * 3077 = 112925900 lignes remontées. On peut voir le nombre d'exécutions en passant la souris sur l'opérateur index scan (Actuel Execution).

Effectivement mikedavem, cela semble logique !
A ce propos, savez-vous ou je peux trouver des tuto en français sur l'analyse de ces plans d’exécution. Je comprend la logique générale mais lorsqu'il faut rentrer dans le détails, je galère ...


Par avance,
Merci.

[Ptit_Dje]

Effectivement mikedavem, cela semble logique !
A ce propos, savez-vous ou je peux trouver des tuto en français sur l'analyse de ces plans d’exécution. Je comprend la logique générale mais lorsqu'il faut rentrer dans le détails, je galère ...

Un tuto en francais non.
Un ebook gratuit en anglais ici.


Bonne lecture!

[Glouferu]

Merci Ptit_Dje, 251 pages de bonheur