Optimisation de Procédure Stockée SQL Server


 

Une des problématiques récurrentes chez des clients concerne l’optimisation des procédures stockées. Elles sont utilisées à des fins de Reporting, pour standardiser du code T-SQL, pour améliorer les performances des applications, empêcher les injections SQL, …

SQLServerPerfomance

Le sujet est tellement vaste que l’on pourrait en écrire un livre, je serai donc “non exhaustif” mais essayerai de mettre en évidence quelques problèmes rencontrés et leurs solutions, je réfuterai par la même occasion quelques mythes.

A partir d’exemples concrets, nous verrons dans un premier temps les bénéfices d’utiliser les procédures stockées plutôt que des requêtes Ad-hoc, nous ferons appel aux Extended Events pour afficher les évènements de recompilation et connaitre leurs causes, nous verrons les causes d’obsolescences de plan d’exécution mise en cache. Enfin nous optimiserons une même procédure en appliquant différentes techniques.

Informations :
– L’article n’est pas un cours sur la façon de créer des procédures stockées
– L’article n’est pas un cours sur l’indexation, ni sur les statistiques
– Je ne parlerai pas des procédures stockées compilées nativement (Natively Compiled Stored Procedures)

Cela étant dit, allons-y !

 

Les procédures dans SQL Server

Une procédure est un groupe d’une ou de plusieurs instructions T-SQL enregistrées. Elles peuvent accepter des paramètres d’entrées et retourner ou non un ensemble de données, contenir des instructions effectuant des opérations sur une base de données, …

Pourquoi créer une procédure stockée ?

[list type=”arrow”]
[item]Pour réduire le trafic réseau : Les commandes d’une procédure sont exécutées en un seul lot. Cela réduit le trafic réseau entre le serveur et le client car uniquement l’appel à la procédure est envoyé sur le réseau. Sans l’encapsulation de code fourni par une procédure, chaque ligne de code aurait transité sur le réseau.[/item]
[item]Pour des questions de sécurité : Les utilisateurs peuvent effectuer des opérations sur les objets de base de données par le biais d’une procédure malgré le fait qu’ils n’aient pas d’autorisations directes sur ces objets. Cela élimine la nécessité d’accorder des autorisations au niveau de l’objet et simplifie les couches de sécurité. Remarque : La clause EXECUTE AS peut être spécifiée dans l’instruction CREATE PROCEDURE pour permettre l’emprunt de l’identité d’un autre utilisateur. Pour plus d’information, je vous conseille le lien suivant : Managing Permissions with Stored Procedures in SQL Server.[/item]
[item]Pour ne pas avoir à répéter le même code : Les procédures permettent de centraliser les instructions et ainsi d’éliminer la redondance de code.[/item]
[item]Pour faciliter la maintenance : Les procédures offrent une couche d’abstraction aux objets sous-jacents d’une base de données.[/item]
[item]Pour améliorer les performances : Par défaut, les procédures sont compilées lors de leurs premières exécutions, un plan d’exécution est alors créé et pourra être réutilisé pour les exécutions suivantes. Du fait que le plan d’exécution est déjà créé, les prochaines exécutions devraient être plus rapides (“devraient” car nous verrons dans la suite de l’article que ça n’est pas toujours le cas!)[/item]
[/list]

Voici le processus de traitement d’une procédure stockée jamais encore exécutée :

Lors de la création d’une procédure stockée, SQL Server analyse la procédure pour s’assurer qu’il n’y ait pas d’erreurs syntaxiques. Une fois que la procédure est analysée avec succès, l’objet est placé dans les tables système (ajout des métadonnées).

Nous réfutons ici un premier mythe qui est le fait de penser que le plan d’exécution de la procédure est mis en cache lors de sa création.

Concernant la tache de résolution, elle est présente lors de la création de la procédure stockée et lors de sa première exécution. Lors de la création de la procédure, SQL Server va déterminer si les objets utilisés par celle ci existent et si tel est le cas il vérifiera les dépendances. Si par contre un des objets utilisés par la procédure n’existe pas, les références seront vérifiées que lors de la première exécution.

Remarque : Un plan est généré lorsqu’aucun plan n’existe déjà en cache, les plans ne sont jamais enregistrés sur le disque et peuvent être supprimés par différentes opérations (redémarrage de l’instance, la commande DBCC FREEPROCCACHE, certaines modifications de configuration de l’instance, la commande SP_RECOMPILE, …). D’autre part certaines opérations peuvent invalider le plan d’exécution (bien que l’objet reste en cache).

Quelques commandes pour avoir des informations sur une procédure stockée :

 

Préambule

Dans la suite de l’article, nous travaillerons sur la base de données Adventure Works 2014 que vous pouvez télécharger sur Codeplex, vous pourrez ainsi rejouer les scripts.

Afin de faciliter la lecture des plans d’exécutions mises en cache, création de la procédure “ProcedurePlans” :

Pour afficher les évènements de recompilation et leurs causes, nous allons utiliser la procédure suivante :

La précédente procédure se base sur les évènements étendus, voici comment démarrer une session :

Pour supprimer les sessions en cours :

 

Optimisation : Compilation

Dans ce chapitre nous allons utiliser les deux précédentes procédures afin d’illustrer la recompilation et l’invalidation des plans mise en cache. Créons dans un premier temps une procédure composée de deux requêtes T-SQL :

Après une première exécution de la procédure, voici les plans d’exécution en cache et leurs plans :

RCA_ProcedurePlans2

RCA_ProcedurePlans1

Le cache, vidé auparavant contient désormais le plan d’exécution des deux requêtes de la procédure [RCA_StateProvince] ayant étés exécutées une fois.

Créons maintenant un nouvel index afin d’optimiser les performances (suppression d’un index existant au profil d’un Covered Index) :

Best Practice : La clause INCLUDE ajoute les données au niveau le plus bas (feuille), plutôt que dans l’arbre d’index. Elle est généralement utilisée si la colonne n’est pas dans la clause WHERE / JOIN / GROUP BY / ORDER BY, mais seulement dans la liste des colonnes de la clause SELECT. Cela signifie qu’elle n’est pas vraiment utile pour les prédicats, tri, … La clause INCLUDE devrait avoir les champs dont vous avez besoin après qu’une ligne ait été trouvée, cela permet d’économiser des allers retour pour accéder à ces données.

Rééxecution des mêmes requêtes :

RCA_ProcedurePlans4

RCA_ProcedurePlans3

En cache, on peut voir que la première requête a été exécutée avec le même plan d’exécution tandis que l’exécution de la seconde requête a généré un nouveau plan d’exécution (VersionOfPlan). L’ancien plan d’exécution a été invalidé au profil d’un nouveau plan plus performant. Nous pouvons exécuter la procédure [RCA_RecompileEvents] pour en connaitre la cause (Offsets 482 à 746) :

SQLRecompile

D’autres actions peuvent causer l’obsolescence ou l’invalidation de plan d’exécution :

SQLRecompileCauses

Informations : La réutilisation des plans peut être bénéfique lorsque différents paramètres passés à la procédure ne changent pas le plan optimal, SQL Server économise alors le temps de compilation et du temps CPU. La réutilisation des plans peut être néfaste lorsque les plans optimaux varient selon le jeu de paramètres. Si un plan d’exécution n’est pas bon, il est alors préférable de ne pas le sauvegarder, en effet le coût de la recompilation peut-être beaucoup moins impactant que le coût d’exécution d’un mauvais plan !

 

Optimisation : Conditions

Après la mise en place d’index (attention à ne pas trop en mettre !) la première optimisation rencontrée chez des clients est l’ajout de conditions dans une procédure.

Lorsqu’une procédure stockée est compilée ou recompilée, les valeurs des paramètres sont “snifés” (Parameter Sniffing) et utilisées pour l’estimation des cardinalités. Le plan d’exécution est optimisé par rapport à ces valeurs.

Prenons l’exemple suivant :

La procédure [RCA_GetPerson] a été initialement créée pour accélérer les traitements de recherche d’information d’un employé en fonction de son nom et cela à l’aide d’une logique conditionnelle. Si le paramètre @LastName contient le caractère ‘%’ alors la première requête sera exécutée sinon ça sera la deuxième. Pensez vous que cela accélère les temps d’exécution ?

Faisons quelques tests :

ParameterSniffing

ParameterSniffing2

 

Voyez vous le problème ?

La première exécution de la procédure est hautement sélective, seules deux lignes ont été retournées contrairement à la seconde exécution qui retourne presque 10000 lignes. Le plan optimal de la seconde exécution a été construit à l’aide du paramètre “snifé” lors de la première exécution de la procédure, à savoir Duffy ! Le plan d’exécution n’est donc pas optimal, un Index Scan aurait été préférable plutôt qu’un Index Seek, et pour vous le confirmer :

ParameterSniffing3

Nous réfutons ici un deuxième mythe qui est le fait de penser que l’ajout de ramifications, de conditions dans une procédure optimise celle ci.

Best Practice : L’option “WITH RECOMPILE”, est à utiliser lors de tests, il recompile le plan uniquement pour cette exécution sans affecter le plan existant (en cache).

Informations : SQL Server ne sera optimiser l’instruction qu’au moment de son l’exécution. Tout comme les variables d’entrées, la ramification d’une instruction conditionnelle est inconnue. Le conditionnement n’influencera pas les performances comme vous le souhaiteriez, au contraire elle peut dégrader les performances de certaines requêtes comme dans cet exemple.

 

Optimisation : Modularisation

Plutôt que d’avoir de gros blocs dans les procédures stockées, il est préférable de la diviser en petits blocs, et ce, afin d’optimiser plus finement les transactions. La modularisation permet cela, au détriment de la maintenance d’un plus grand nombre d’objets.

La procédure “principale” porte le même nom, votre application n’aura donc pas être modifiée. Regardons les plans d’exécution :

SQL_Modularisation

Si on regarde les plans d’exécution en cache, nous avons biens les deux procédures [RCA_GetPersonWithoutLike] et [RCA_GetPersonWithLike] :

Modularisation_Plan

Avons nous vraiment optimisé la procédure ?

Nous avons résolu le problème rencontré précédemment dans le chapitre Condition, à savoir quand est ce que mon plan est optimisé :
– Dans un conditionnement : lors de sa première exécution
– Dans des sous procédures : lors de l’exécution de chacune d’entre elles.

Une procédure ne sera pas optimisée tant qu’elle n’aura pas été appelée contrairement aux différents conditionnements optimisés par la première exécution de la procédure. Cependant le plan est t’il stable pour chacune de ces procédures ? Pas forcément, cela dépend du code et de la distribution des données !

Information : La modularisation permet à SQL Server d’optimiser plus finement les blocs, il n’entrera pas dans les sous procédures à moins qu’il doive l’exécuter.

 

Optimisation : Options

Nous avons vu jusqu’ici l’importance de la compilation des plans d’exécutions, il existe différentes options dans SQL Server pour “jouer” avec celle ci :

[list type=”arrow”]
[item]Recompilation de la procédure :

[/item]
[item]L’option “WITH RECOMPILE” : force la recompilation d’une procédure lors de son exécution.[/item]
[item]L’option “RECOMPILE” : force la recompilation d’une procédure à chacune de ces exécutions.[/item]
[item]L’option “OPTIMIZE FOR …” : force la création d’un plan en cache à partir de la valeur passée, cela empêche le Sniffing de variable.[/item]
[item]L’option “OPTION (OPTIMIZE FOR UNKNOWN)” : force la création d’un plan en cache à partir d’une moyenne (distribution), cela empêche le Sniffing de variable.[/item]
[/list]

 

Pour tester la variation d’un plan d’exécution :

SQL_RECOMPILE

 

Modification de ma procédure initiale et ajout de l’option “WITH RECOMPILE” : Utilisation de l’option “WITH RECOMPILE”

SQL_WITH_RECOMPILE

Les résultats sont similaires qu’avec la modularisation mais aucun plan d’exécution n’est sauvegardé en cache (EXEC [dbo].[RCA_ProcedurePlans];). Nous perdons donc l’un des avantages de l’utilisation des procédures stockées, à savoir la capacité ) rééxecuter des plans d’exécution optimisées.

 

Après avoir effectué quelques tests : analyse des paramètres passés à une procédure, analyse de la distribution des données de la table questionnée, … Il peut s’avérez utile de privilégier un plan d’exécution. L’option “OPTIMIZE FOR …” permet cela :

SQL_OPTIMIZE-FOR

Après nous être aperçus que la plupart des requêtes ayant le caractère ‘%’ étaient plus performantes à l’aide d’un Index Scan, nous avons décidé de forcer son utilisation. Pour le paramètre ‘D%’ la procédure initiale effectuait un Index Seek, maintenant la deuxième branche de la procédure effectuera que des Index Scan.

 

Si par contre aucun pattern ne se dégage de l’analyse effectuée, l’option “OPTIMIZE FOR UNKNOWN” permet d’optimiser le plan en fonction d’une moyenne de distribution (utilisation de DBCC SHOW_STATISTICS) :

SHOW_STATISTICS

La moyenne de lignes retournées pour un nom de famille dans la table [Person] est de 16.56. Alors que le nom ‘Abba’ retourne une ligne, ‘Alexander’ en retourne 123.

DBCC-SHOW_STATISTICS

On retrouve en rouge les 16.56 lignes de la moyenne estimée. En vert, la requête utilise l’histogramme d’où le nombre estimé de lignes égal à 1.

Remarque : Plus les enregistrements seront proches de la moyenne, plus leurs requêtes seront efficientes, au contraire si une valeur est loin de la moyenne alors son exécution sera pénalisée.

Peut être que sans le savoir, vous utilisez déjà l’option OPTIMIZE FOR UNKNOWN, en effet SQL Server n’utilisera pas l’histogramme, mais le vecteur de densité lorsqu’une variable est modifiée dans la procédure :

Informations :
– La recompilation n’est pas forcément une mauvaise pratique ! Si vous exécutez 15 fois une transaction et que vous obtenez 15 plans différents et qu’aucun pattern ne s’en dégage alors les options de recompilation peuvent s’avérer intéressantes.
– L’option “WITH RECOMPILE” ne s’applique qu’à la procédure “mère”, les sous procédures ne seront pas recompilées !
– La procédure “sp_recompile” peut être utilisée sur une table, cela rendra obsolètes les plans en cache faisant appel à cette table.
– Si vous ne connaissez pas les données ou si vous ne voulez pas imposer une valeur pour optimiser le plan d’exécution d’une procédure et que la répartition des données est plutôt équilibrée, l’option “OPTIMIZE FOR UNKNOWN” peut s’avérer intéressante.

 

Optimisation 4 : Dynamique T-SQL

Rappelons avant tout qu’il ne faut JAMAIS utiliser “*” mais spécifier uniquement les colonnes nécessaires, pas comme dans mes précédents scripts T-SQL 😉

Voici la dernière technique d’optimisation que je présenterai dans cet article. Très souvent les procédures stockées ne prennent pas qu’un mais de multiples paramètres en entrée.

Cela nous amène à nous poser plusieurs questions :
– Que faire lorsqu’un des paramètres est NULL
– Que faire lorsque tous les paramètres sont NULL
– Quelles sont les combinaisons de paramètres à regrouper
– …

Plutôt que de longs discours, voici une des solutions que je préconise :

Pour valider la procédure, faisons quelques tests :

SQL_PROCEDURE

Après 3 exécutions de la procédure [RCA_GetPerson], regardons le cache des transactions :

dm_exec_sql_text

Le plan en cache a été réutilisé 3 fois, celui ci est optimal, car il utilise des Index Seek.

OPTION-RECOMPILE_INDEX-SCAN

La recherche n’étant pas suffisamment sélective en vue du nombre d’emails correspondant, une recompilation du plan a été faite tout comme un Index Scan.

Après quelques tests, regardons une dernière fois le cache et vérifions le ré usage de certains plans :

SQLServer_CACHE

Sur les 15 exécutions: 11 ont été paramétrées, compilées, et réutilisées (Recompilation pour les autres non suffisamment sélective) ! Il y aura un plan d’exécution en cache par combinaisons sélectives.

Informations : Cette procédure est à titre d’exemple une option d’optimisation. Cependant, il n’existe pas de stratégie d’optimisation adéquate à tous les problèmes de performance. En effet, il faut utiliser les méthodes en fonction des données et des requêtes, mais savoir ce qui fonctionne et savoir tester est la clé pour effectuer de bonnes optimisations !

 

Compléments

Voici une liste (en vrac) de sujets non abordés dans l’article pouvant impacter les performances d’exécution d’une procédure :

[list type=”arrow”]
[item]La commande “SET NOCOUNT ON” permet de gagner en performance puisque SQL Server ne renverra pas le nombre de lignes impacté par la transaction / procédure. Le paramètre de configuration suivant affecte cette valeur au serveur : “SP_CONFIGURE ‘user options’, 512”.[/item]
[item]Utilise les index pour stabiliser les plans d’exécution d’une procédure.[/item]
[item]Attention aux types de format des données, utiliser la tache SSIS Data Profiling pour valider la pertinence des types.[/item]
[item]Lorsqu’une procédure stockée commence par SP_, SQL Server va d’abord chercher la procédure stockée dans la base Master avant de revenir sur la base donnée courante. Cela pénalise les performances.[/item]
[item]Plan Caching and Recompilation in SQL Server 2012 : SQL Server Technical Article.[/item]
[item]Utilisez la procédure stockée SP_EXECUTESQL au lieu de l’instruction EXECUTE pour l’exécution de requête dynamique (SET @Query = ‘SELECT * …’).[/item]
[item]Évitez les CURSEURS si possible.[/item]
[/list]

A titre de conclusion, je dirais que le sujet est trop vaste pour être traité dans un article de blog. J’espère cependant vous avoir appris quelques astuces concernant l’optimisation de procédures stockées dans SQL Server !

PS : Je n’avais jamais autant passé de temps sur la rédaction d’un article 😮

Commentez cet article