Blog

SQL Server : Compilation et recompilation


Votre CPU est trop occupé ? Peut-être est-il monopolisé par des tâches de compilation/recompilation à répétition… Quelle est la différence entre compilation et recompilation ? Comment les provoquer et les détecter ? C’est bien ou mal finalement ? La réponse est comme à l’accoutumée « Ca dépend », ce que nous verrons dans cet article.

Expert SQL Server - SQL Server : Compilation et recompilation - SQL Server  - High_CPU

Le processus de compilation calcule le plan d’exécution d’une requête ou d’un objet, soit le meilleur itinéraire possible et le monte en mémoire pour réutilisation. Ce plan d’exécution peut être remis en question au fils du temps. Deux évènements peuvent donc avoir lieu :
– Compilation : Elle a lieu quand un plan d’exécution n’est pas en cache (ex : jamais exécuté, sortie du cache par manque de mémoire).
– Recompilation : Quand le plan est déjà disponible en cache, le plan sera recompilé pour les raisons suivantes :

– Schéma modifié
– Statistiques modifiées
– Compilation différée
– Option SET modifiée
– Table temporaire modifiée
– Resordset distant modifié
– Autorisation FOR BROWSE modifiée
– Environnement de notification de requête modifié
– Vue partitionnée modifiée
– Options de curseur modifiées
– OPTION (RECOMPILE)

Que ce soit une compilation ou recompilation, cette opération a un coût CPU à relativiser dans certains cas. Sauf exception, la norme est de ne pas avoir un taux de compilations et recompilations qui excède de 10% de l’ensemble des batch SQL exécutés. Nous verrons plus tard comment le calculer.

Recompilation à la demande

Parlons un peu de l’exception, il y a des contextes où l’on peut avoir besoin de forcer la recompilation d’un objet ou d’une requête quand le parameter sniffing a des effets négatifs telles qu’une forte consommation d’I/O et un allongement de la durée d’exécution. En d’autres termes, quand la distribution des données n’est pas homogène, donc avec une cardinalité qui varie fortement suivant la valeur des paramètres en entrée, la recompilation peut être une solution. Avant de crier au loup « parameter sniffing », assurez-vous que vos statistiques sont bien à jour.  Sinon voici comment lancer une recompilation sur une requête ou un objet (procédure stockée, UDF ou autre) pour répondre à une problématique de parametter sniffing individuelle :

Pour éviter la recompilation sur une requête, à partir de SQL Server 2008, envisagez l’option OPTIMIZE FOR UNKNOWN qui utilise le vecteur de densité. Le but est d’obtenir une estimation « générique » du nombre de lignes afin de stabiliser les temps d’exécution. Sur une version plus ancienne de SQL Server, une autre méthode consiste à utiliser des variables locales.

Pour répondre à une problématique de parametting sniffing généralisée, il est possible de vider le cache stockant les plans d’exécution pour une base de données spécifique (DBCC FLUSHPROCINDB) ou pour l’instance (DBCC FREEPROCCACHE).

Le recours aux commandes DBCC bien que consommateur en CPU, le temps du calcul des plans, sera toujours plus acceptable que de redémarrer l’instance moteur. En clientèle, je ne compte plus le nombre de fois où j’ai entendu « Dès qu’on redémarre SQL Server, ça va beaucoup mieux ! ». Il y a parfois du parametting sniffing derrière.

Expert SQL Server - SQL Server : Compilation et recompilation - SQL Server  - NO

Collecte des compilations  et recompilations via le perfmon

Comment détecter les compilations et recompilations et plus précisément celles qu’on a pas demandé ? 3 indicateurs, soit le nombre de compilations et recompilations comparé au nombre de batch SQL, peuvent être collectés depuis le perfmon dans la section SQL Statistics :

Expert SQL Server - SQL Server : Compilation et recompilation - SQL Server  - perfmon_SQLStatistics

Collecte des compilations et recompilations via la DMV sys.dm_os_performance_counters

Les informations sont aussi disponibles en interrogeant la DMV sys.dm_os_performance_counters mais sous forme de cumule (cntr_type = 272696576).

Pour restituer les valeurs non cumulées entre deux collectes dans un intervalle de 5 secondes, on peut procéder comme suit :

NB : les options SET ARITHABORT OFF & SET ANSI_WARNINGS OFF sont utilisées pour éviter l’erreur Division par zéro :

Divide by zero error encountered

Date de mise en cache des PS via les DMV sys.dm_exec_procedure_stats et dm_exec_query_stats

La date de mise en cache des procédures stockées et requêtes associées peut être obtenue par les requêtes suivantes :

Les procédures stockées utilisant l’option WITH RECOMPILE ne seront évidemment pas présentes. Pour vous en assurer, effectuez la recherche suivante :

Collecte des objets sujets à la recompilation via la DMV  sys.dm_exec_query_stats

Au préalable à une trace profiler ou XEvent, comme nous le verrons plus tard, il est possible de recenser les requêtes et objets associés qui sont sujets à la recompilation en s’appuyant sur la DMV sys.dm_exec_query_stats et plus précisément sur la colonne plan_generation_num. Elle correspond à un numéro de séquence permettant de distinguer les instances de plans après une recompilation.

Collecte des recompilations via le profiler

Au delà du nombre de recompilations et des objets recompilés identifiés, il peut être intéressant de les analyser plus finement en les associant avec l’instruction en cause avec la raison comme énuméré plus haut. On peut procéder de plusieurs manières, en utilisant le profiler et plus précisément l’événement SQL:StmtRecompile ou SP:Recompile :

Expert SQL Server - SQL Server : Compilation et recompilation - SQL Server  - profiler_StmtRecompile

Les colonnes en sortie qui nous intéressent sont les suivantes :

– EventSubClass : Cause de la recompilation
– TextData : Instruction concernée
– ObjectName : Nom de l’objet associé à l’instruction

Il est recommandé d’appliquer des filtres en amont pour éviter de recenser un nombre d’événements de recompilation trop important.

La requête suivante permet d’obtenir le référentiel des raisons de recompilations au besoin :

Expert SQL Server - SQL Server : Compilation et recompilation - SQL Server  - reompile_reason-1

Collecte des recompilations via XEvent

Le profiler est supposé être déprécié à terme. Il est bien évidemment possible de collecter les mêmes informations en créant une trace XEvent. Dans la définition de la session ci-dessous, bien qu’il ait été commenté, un filtre est présent pour ne retenir les recompilations occasionnées sur les objets TempTableProc et VarTempTableProc.

Voici comment explorer la trace XEvent :

Conclusion

Reste à optimiser les requêtes identifiées pour réduire le nombre de recompilations. Typiquement :
– Evitez les opérations DDL post création (ex : ajout de colonnes à la volée)
– Eliminez les DELETE sur des tables ##temp et #temp, filtrez plutôt en amont
– Sur une faible cardinalité, remplacez les tables ##temp et #temp par des variables table
– Soyez le plus fin possible dans vos ordres de recompilation
– Etc

Pour rappel :

http://www.concatskills.com/2017/12/10/tables-tempoaires-variable-table/

Auteur

Expert SQL Server - SQL Server : Compilation et recompilation - SQL Server  - avatar_ninja_tete-150x150
Sarah Bessard
Experte SQL Server Prod/Etude avec un bonus sur la BI, les maîtres mots sont : performance, industrialisation, méthodologie & bonne humeur. Besoin d'une expertise SQL Server ? N'hésitez pas à me contacter.

Leave a comment

Your email address will not be published.

error: