j'ai un soucis de performance concernant une procédure stockées. le principe
de la procédure est de mettre à jour une "table dénormalisé" quotidiennement,
le traitement dure en moyenne 2 minutes. De temps en temps le traitement
explose et peut mettre plus de deux heures. Malheureusement en repartant d'un
backup impossible de reproduire le problème durée du traitement 2 minutes.
Des tests poussés ont était réalisé sur un serveur dédié au test, avec une
seul base. Restauration de la base et mise en place d'un job SQL qui tourne
en continue plus (d'une centaine de fois) le job fait :
0. activation trace SQL
1. reorganisation des index
2. calcul des stats
3. traitement pour la "table dénormalisé"
4. controle durée du traitement et nb execution retourne à l'étape 1
Ce job à était lancé 3 fois soit 300 execution du dit traitement il à
exploser trois fois.
1. job de la 1° à la 89° execution durée normal ( 2') , à la 90° 2Heure et
le plus surprennant est que de la 91° execution à la 100° la durée est
normal(2')
2. job plantage à la 35° execution et même condition que le 1° job
3. job plantage à la 60° execution et même condition que le 1° job
Le seul constat fait est qu'a chaque fois que le traitement "explose", le
plan d'execution n'est pas du tout le bon.
Je n'arrive pas expliquer ni a m'expliquer pourquoi le plan d'execution
change ?
Est ce que c'est un comportement normal ?
La seul solution que je vois serait de forcer le plan d'execution, ou une
autre piste serait elle envisageable ?
Si vous avez rencontrez ce genre de problème, si vous avez des ébauche
d'explication, je suis très intéressé.
Cette action est irreversible, confirmez la suppression du commentaire ?
Signaler le commentaire
Veuillez sélectionner un problème
Nudité
Violence
Harcèlement
Fraude
Vente illégale
Discours haineux
Terrorisme
Autre
Fred BROUARD
Bonjour,
Ceci est assez normal... Pour le comprendre venez à mon cours d'optimisation à Orsys par exemple.
Je vais cepandant tenter de vous l'expliquer..
Un plan de requête n'est pas quelque chose de statique. SQL Server regarde à chaque exécution s'il existe un delta entre le plan qu'il à mis en cache et les données.
Imaginons un cas parfait : la table vient d'être alimentée et tous les index viennent d'être reconstruits. Dans ce cas, les statistiques collectées sur les index sont parfaitement à jour (min, max, dispersion...) Dans ce cas le plan de requête établis sera parfait.
Au fur et à mesure de l'insertion, la suppression et la modification des données les index commençent à se fragmenter. SQL Server comptabilise le nombre des mise à jour produites : nombre d'insert + nombre de delete d'un côté et nombre d'update de l'autre. Au bout d'un certains temps ces compteurs dépassent un certain seuil (mettons 10%) et SQL Server considère que le plan de requête n'est plus pertinent puisque les statistiques ne sont plus à jour.
De là arrive votre problème : le nouveau plan conçut n'est peut être plus pertienent et de plus les index sont fragmentés.
Vous avez deux remèdes possibles : 1) soit figer le plan de requête au moment opportun 2) soit défragmenter les index régulièrement.
En sus, lorqu'il s'agit de mise à jour, alors il est très intéressant de désactiver les index, lorsque cela est possible. Les gains de temps dans ce cas sont intéressant.
A +
Leon a écrit :
bonjour,
j'ai un soucis de performance concernant une procédure stockées. le principe de la procédure est de mettre à jour une "table dénormalisé" quotidiennement, le traitement dure en moyenne 2 minutes. De temps en temps le traitement explose et peut mettre plus de deux heures. Malheureusement en repartant d'un backup impossible de reproduire le problème durée du traitement 2 minutes.
Des tests poussés ont était réalisé sur un serveur dédié au test, avec une seul base. Restauration de la base et mise en place d'un job SQL qui tourne en continue plus (d'une centaine de fois) le job fait : 0. activation trace SQL 1. reorganisation des index 2. calcul des stats 3. traitement pour la "table dénormalisé" 4. controle durée du traitement et nb execution retourne à l'étape 1
Ce job à était lancé 3 fois soit 300 execution du dit traitement il à exploser trois fois. 1. job de la 1° à la 89° execution durée normal ( 2') , à la 90° 2Heure et le plus surprennant est que de la 91° execution à la 100° la durée est normal(2') 2. job plantage à la 35° execution et même condition que le 1° job 3. job plantage à la 60° execution et même condition que le 1° job
Le seul constat fait est qu'a chaque fois que le traitement "explose", le plan d'execution n'est pas du tout le bon.
Je n'arrive pas expliquer ni a m'expliquer pourquoi le plan d'execution change ? Est ce que c'est un comportement normal ? La seul solution que je vois serait de forcer le plan d'execution, ou une autre piste serait elle envisageable ?
Si vous avez rencontrez ce genre de problème, si vous avez des ébauche d'explication, je suis très intéressé.
Merci. Leon
A +
-- Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com Audit, conseil, expertise, formation, modélisation, tuning, optimisation ********************* http://www.datasapiens.com ***********************
Bonjour,
Ceci est assez normal...
Pour le comprendre venez à mon cours d'optimisation à Orsys par exemple.
Je vais cepandant tenter de vous l'expliquer..
Un plan de requête n'est pas quelque chose de statique. SQL Server
regarde à chaque exécution s'il existe un delta entre le plan qu'il à
mis en cache et les données.
Imaginons un cas parfait :
la table vient d'être alimentée et tous les index viennent d'être
reconstruits. Dans ce cas, les statistiques collectées sur les index
sont parfaitement à jour (min, max, dispersion...)
Dans ce cas le plan de requête établis sera parfait.
Au fur et à mesure de l'insertion, la suppression et la modification des
données les index commençent à se fragmenter. SQL Server comptabilise le
nombre des mise à jour produites : nombre d'insert + nombre de delete
d'un côté et nombre d'update de l'autre.
Au bout d'un certains temps ces compteurs dépassent un certain seuil
(mettons 10%) et SQL Server considère que le plan de requête n'est plus
pertinent puisque les statistiques ne sont plus à jour.
De là arrive votre problème : le nouveau plan conçut n'est peut être
plus pertienent et de plus les index sont fragmentés.
Vous avez deux remèdes possibles :
1) soit figer le plan de requête au moment opportun
2) soit défragmenter les index régulièrement.
En sus, lorqu'il s'agit de mise à jour, alors il est très intéressant de
désactiver les index, lorsque cela est possible. Les gains de temps dans
ce cas sont intéressant.
A +
Leon a écrit :
bonjour,
j'ai un soucis de performance concernant une procédure stockées. le principe
de la procédure est de mettre à jour une "table dénormalisé" quotidiennement,
le traitement dure en moyenne 2 minutes. De temps en temps le traitement
explose et peut mettre plus de deux heures. Malheureusement en repartant d'un
backup impossible de reproduire le problème durée du traitement 2 minutes.
Des tests poussés ont était réalisé sur un serveur dédié au test, avec une
seul base. Restauration de la base et mise en place d'un job SQL qui tourne
en continue plus (d'une centaine de fois) le job fait :
0. activation trace SQL
1. reorganisation des index
2. calcul des stats
3. traitement pour la "table dénormalisé"
4. controle durée du traitement et nb execution retourne à l'étape 1
Ce job à était lancé 3 fois soit 300 execution du dit traitement il à
exploser trois fois.
1. job de la 1° à la 89° execution durée normal ( 2') , à la 90° 2Heure et
le plus surprennant est que de la 91° execution à la 100° la durée est
normal(2')
2. job plantage à la 35° execution et même condition que le 1° job
3. job plantage à la 60° execution et même condition que le 1° job
Le seul constat fait est qu'a chaque fois que le traitement "explose", le
plan d'execution n'est pas du tout le bon.
Je n'arrive pas expliquer ni a m'expliquer pourquoi le plan d'execution
change ?
Est ce que c'est un comportement normal ?
La seul solution que je vois serait de forcer le plan d'execution, ou une
autre piste serait elle envisageable ?
Si vous avez rencontrez ce genre de problème, si vous avez des ébauche
d'explication, je suis très intéressé.
Merci.
Leon
A +
--
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************
Ceci est assez normal... Pour le comprendre venez à mon cours d'optimisation à Orsys par exemple.
Je vais cepandant tenter de vous l'expliquer..
Un plan de requête n'est pas quelque chose de statique. SQL Server regarde à chaque exécution s'il existe un delta entre le plan qu'il à mis en cache et les données.
Imaginons un cas parfait : la table vient d'être alimentée et tous les index viennent d'être reconstruits. Dans ce cas, les statistiques collectées sur les index sont parfaitement à jour (min, max, dispersion...) Dans ce cas le plan de requête établis sera parfait.
Au fur et à mesure de l'insertion, la suppression et la modification des données les index commençent à se fragmenter. SQL Server comptabilise le nombre des mise à jour produites : nombre d'insert + nombre de delete d'un côté et nombre d'update de l'autre. Au bout d'un certains temps ces compteurs dépassent un certain seuil (mettons 10%) et SQL Server considère que le plan de requête n'est plus pertinent puisque les statistiques ne sont plus à jour.
De là arrive votre problème : le nouveau plan conçut n'est peut être plus pertienent et de plus les index sont fragmentés.
Vous avez deux remèdes possibles : 1) soit figer le plan de requête au moment opportun 2) soit défragmenter les index régulièrement.
En sus, lorqu'il s'agit de mise à jour, alors il est très intéressant de désactiver les index, lorsque cela est possible. Les gains de temps dans ce cas sont intéressant.
A +
Leon a écrit :
bonjour,
j'ai un soucis de performance concernant une procédure stockées. le principe de la procédure est de mettre à jour une "table dénormalisé" quotidiennement, le traitement dure en moyenne 2 minutes. De temps en temps le traitement explose et peut mettre plus de deux heures. Malheureusement en repartant d'un backup impossible de reproduire le problème durée du traitement 2 minutes.
Des tests poussés ont était réalisé sur un serveur dédié au test, avec une seul base. Restauration de la base et mise en place d'un job SQL qui tourne en continue plus (d'une centaine de fois) le job fait : 0. activation trace SQL 1. reorganisation des index 2. calcul des stats 3. traitement pour la "table dénormalisé" 4. controle durée du traitement et nb execution retourne à l'étape 1
Ce job à était lancé 3 fois soit 300 execution du dit traitement il à exploser trois fois. 1. job de la 1° à la 89° execution durée normal ( 2') , à la 90° 2Heure et le plus surprennant est que de la 91° execution à la 100° la durée est normal(2') 2. job plantage à la 35° execution et même condition que le 1° job 3. job plantage à la 60° execution et même condition que le 1° job
Le seul constat fait est qu'a chaque fois que le traitement "explose", le plan d'execution n'est pas du tout le bon.
Je n'arrive pas expliquer ni a m'expliquer pourquoi le plan d'execution change ? Est ce que c'est un comportement normal ? La seul solution que je vois serait de forcer le plan d'execution, ou une autre piste serait elle envisageable ?
Si vous avez rencontrez ce genre de problème, si vous avez des ébauche d'explication, je suis très intéressé.
Merci. Leon
A +
-- Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com Audit, conseil, expertise, formation, modélisation, tuning, optimisation ********************* http://www.datasapiens.com ***********************
