SelectEtoile : Base de connaissance SGBD

Pb de perfs avec la procedure sp_executesql

1

Symptômes :


                Temps d'exécutions aléatoires des requêtes suite à une mise en PROD
                Non réponses aléatoire de certaines requêtes  

 

Causes  :


                Comportement du sp_executesql (ce n’est pas un bug, c’est une limite d’utilisation)

                Cette proc permet d'exécuter du code mais de garder le plan en cache pour une utilisation ultérieure, le problème c'est que les paramètres qui sont passés dans notre cas modifient beaucoup la sélectivité de la requête du coup le plan réutilisé n'est peut être pas approprié.

 

Mise en évidence :


                        'TRAPPER' la requête applicative via perfmon + le jeu de SET commande
               
                                Example:

                                        set quoted_identifier on
                                        set arithabort off
                                        set numeric_roundabort off
                                        set ansi_warnings on
                                        set ansi_padding on
                                        set ansi_nulls on
                                        set concat_null_yields_null on
                                        set cursor_close_on_commit off
                                        set implicit_transactions off
                                        set language Français
                                        set dateformat dmy
                                        set datefirst 1
                                        set transaction isolation level read committed
                               
                                exec sp_executesql N'SELECT top 110000 this_.IdOrdreEspece as IdOrdreE1_996_0_, this_.CodSens as CodSens996_0_, this_.IdCleRegroupement as IdCleReg3_996_0_,                                                 this_.CodEtablissementTiers as CodEtabl4_996_0_, this_.CodGuichetTiers as CodGuich5_996_0_, this_.CodCompteTiers as CodCompt6_996_0_, this_.CodCleRibTiers as                                                 CodCleRi7_996_0_, this_.DatEffet as DatEffet996_0_, this_.DatCreation as DatCreat9_996_0_, this_.DatModification as DatModi10_996_0_, this_.LoginCreation as LoginCr11_996_0_,                                 this_.LoginModification as LoginMo12_996_0_, this_.MntOrdre as MntOrdre996_0_, this_.TypEtatOrdre as TypEtat14_996_0_, this_.LibDebit as LibDebit996_0_, this_.LibCredit as                                 LibCredit996_0_, this_.LibCommentaire as LibComm17_996_0_, this_.LibBeneficiaire as LibBene18_996_0_, this_.TypOrdre as TypOrdre996_0_, this_.CodCompte as CodCompte996_0_,                                 this_.LibIntitule as LibInti21_996_0_, this_.CodDevise as CodDevise996_0_, this_.LibMessageErreur as LibMess23_996_0_, this_.MntEspeceJCP as MntEspe24_996_0_,                                                 this_.MntEspeceJ1CP as MntEspeceJ25_996_0_, this_.MntPatrimoineJCP as MntPatr26_996_0_, this_.MntPatrimoineJ1CP as MntPatr27_996_0_, this_.MntEspeceJCT as                                                 MntEspe28_996_0_, this_.MntEspeceJ1CT as MntEspeceJ29_996_0_, this_.MntPatrimoineJCT as MntPatr30_996_0_, this_.MntPatrimoineJ1CT as MntPatr31_996_0_ FROM                                         db_ldw.dbo.LUT_V_CarnetOrdreEspece this_ WHERE this_.DatCreation >= @p0 and this_.DatCreation < @p1 and this_.IdCleRegroupement = @p2',N'@p0 datetime,@p1 datetime,@p2                                 bigint',@p0='12/12/2010 00:00:00',@p1='20/12/2010 00:00:00',@p2=4678913124410789674
       
        Attention !!        Par defautl sql management studio met le set arithabort à 'on' alors que l'application le met à 'off', du coup les plans changent dans le procédure cache.
                       
                        1        DBCC FREEPROCCACHE;                (attention en prod ca peut couter cher)
                        2        jouer les SET commandes et la requête sur un petit périmètre (ici c'est le @p2 qui spécifie le carnet d'ordre des clients (petit pour 1 client mais gros pour un ensemble de clients)

  • normalement l'exécution est bonne et le plan est en cache
  • select * from master..syscacheobjects where sql like '%un bout de votre code%'                

                        3        jouer les SET commandes et la requête sur un Gros périmètre

  • La, c'est le drame, ça rame, le plan réutilisé n'est pas adapté pour un gros périmètre (confirmation avec le show plan )

 

Faites l'inverse :

                        1        DBCC FREEPROCCACHE;                (attention en prod ca peut couter cher)
                        2        Jouer les SET commandes et la requête sur un Gros périmètre

  • Normalement l'exécution est bonne et le plan est en cache
  • select * from master..syscacheobjects where sql like '%un bout de votre code%'

     
                        3        Jouer les SET commandes et la requête sur un petit périmètre

  • La, ca dépote, le plan plus 'complet' du gros périmètre est aussi valable pour les petites requêtes.


Erreur à ne pas faire :

Mettre à jour les stats à outrance, nous avons l'impression que ca va mieux, mais ce n'est pas à cause de la mise à jour des stats mais à cause de l'effet de bord :  la mise à jour des stats ayant invalidée les plans d'exécutions du cache pour les requêtes référençant les tables ou index mis à jour.

 

 

Solution :

Pour une fois, il est possible d'utiliser la commande (RECOMPILE) :


                        exec sp_executesql N'SELECT top 110000 this_.IdOrdreEspece as IdOrdreE1_996_0_, this_.CodSens as CodSens996_0_, this_.IdCleRegroupement as IdCleReg3_996_0_,                                                 this_.CodEtablissementTiers as CodEtabl4_996_0_, this_.CodGuichetTiers as CodGuich5_996_0_, this_.CodCompteTiers as CodCompt6_996_0_, this_.CodCleRibTiers as                                                 CodCleRi7_996_0_, this_.DatEffet as DatEffet996_0_, this_.DatCreation as DatCreat9_996_0_, this_.DatModification as DatModi10_996_0_, this_.LoginCreation as LoginCr11_996_0_,                                 this_.LoginModification as LoginMo12_996_0_, this_.MntOrdre as MntOrdre996_0_, this_.TypEtatOrdre as TypEtat14_996_0_, this_.LibDebit as LibDebit996_0_, this_.LibCredit as                                 LibCredit996_0_, this_.LibCommentaire as LibComm17_996_0_, this_.LibBeneficiaire as LibBene18_996_0_, this_.TypOrdre as TypOrdre996_0_, this_.CodCompte as CodCompte996_0_,                                 this_.LibIntitule as LibInti21_996_0_, this_.CodDevise as CodDevise996_0_, this_.LibMessageErreur as LibMess23_996_0_, this_.MntEspeceJCP as MntEspe24_996_0_,                                                 this_.MntEspeceJ1CP as MntEspeceJ25_996_0_, this_.MntPatrimoineJCP as MntPatr26_996_0_, this_.MntPatrimoineJ1CP as MntPatr27_996_0_, this_.MntEspeceJCT as                                                 MntEspe28_996_0_, this_.MntEspeceJ1CT as MntEspeceJ29_996_0_, this_.MntPatrimoineJCT as MntPatr30_996_0_, this_.MntPatrimoineJ1CT as MntPatr31_996_0_ FROM                                         db_ldw.dbo.LUT_V_CarnetOrdreEspece this_ WHERE this_.DatCreation >= @p0 and this_.DatCreation < @p1 and this_.IdCleRegroupement = @p2 OPTION (RECOMPILE) ',N'@p0                         datetime,@p1 datetime,@p2 bigint',@p0='12/12/2010 00:00:00',@p1='20/12/2010 00:00:00',@p2=4678913124410789674
       

Impacts :

 

  • Légère augmentation CPU dû à la recompilation des plans à chaque appels
  • Augmentation du temps de traitement de la requête pour les mêmes raisons

 

 

 Merci Matthieu !!!

Ajouter un Commentaire


Code de sécurité
Rafraîchir