La version DBA d’un classique XKCD.

Lorsque vous identifiez que le reniflage de paramètres est un problème, vous devez tester si l’implémentation de conseils « recompiler » aidera à planifier la qualité. (Vous ne savez pas quel est le reniflement de paramètre? Apprenez de cet article de blog ou de cette vidéo gratuite de 30 minutes.) Vous devez décider: quel indice ou quelle commande utilisez-vous, et où le mettez-vous? Quels compromis faites-vous pour pouvoir ajuster les performances de votre serveur SQL à l’avenir?

Ce message passe par les options courantes que vous devez pousser (ou pousser) SQL Server à générer un nouveau plan d’exécution. Je vais donner quelques avantages et inconvénients pour chaque méthode et expliquer ce qui est utile et ce qui vaut la peine d’être évité.

Pour garder les choses relativement simples, je discute simplement de la façon dont cela s’applique aux procédures stockées aujourd’hui – cet article ne couvre pas les autres formes de requêtes paramétrées (ou non paramétrées). Si vous avez de grandes questions dans ces domaines, n’hésitez pas à le suggérer pour un futur article dans les commentaires.

Avertissement: Les astuces de recompilation peuvent tuer vos performances en allumant vos PROCESSEURS en feu lorsqu’ils sont mal utilisés. Manipulez avec soin!

Vues de gestion dynamiques utiles

Lorsque je parle d’impact sur le cache du plan d’exécution, je me référerai à deux DMV :

• sys.dm_exec_query_stats – Ce DMV est utile pour voir les principales instructions sur votre serveur SQL, qu’elles fassent partie d’une procédure ou non. Consultez un exemple de requête ici.
• sys.dm_exec_procedure_stats – Ce DMV n’est disponible que dans SQL Server 2008 et versions ultérieures. Il suit les métriques d’exécution des procédures stockées. Consultez un exemple de requête dans Books Online.

Pour ces deux DMV, avoir un plan d’exécution dans le cache est lié à la possibilité de voir les métriques d’exécution : nombre d’exécutions totales, CPU total et moyen, lectures logiques, etc. Lorsqu’un plan d’exécution est supprimé du cache en raison d’une recompilation, d’une pression de mémoire, d’un redémarrage ou d’autres actions, les métriques d’exécution associées sont également supprimées.

Exécution d’une procédure avec un indice de recompilation d’exécution

Une option que j’aime pour les tests rapides et faciles est la possibilité d’appeler une procédure stockée avec un indice de recompilation au moment de l’exécution. C’est génial car vous n’avez pas à changer de code compilé. Votre indice s’applique également à ce que vous faites au moment de l’exécution.

Pour ce faire, vous utilisez simplement une syntaxe comme celle-ci:

However, there’s a little bit of a gotcha. If you have nested stored procedures, the recompile hint only applies to code in the outermost procedure. Vous pouvez toujours obtenir un reniflage de paramètres sur tous les appels à des sous-procédures dans la procédure stockée elle-même.

Donc, même si j’aime parfois cela pour les tests, si vous avez une imbrication dans vos procédures stockées, cela peut ne pas vous aider à obtenir un plan d’exécution vraiment frais.

Utiliser la procédure stockée du système sp_recompile

Cela est parfois utile si vous devez intervenir lors d’un incident de production si vous avez un mauvais plan utilisé à plusieurs reprises par les sessions entrantes. You can force recompilation of plans by running sp_recompile against an object– most common choices are a table or a stored procedure. The command looks like this (a table in this example):

Lorsque cela est exécuté, les plans associés ne sont pas immédiatement supprimés du cache de plan d’exécution de SQL Server. Au lieu de cela, la magie se produit la prochaine fois que les requêtes référençant l’objet recompilé s’exécutent. À ce stade, les statistiques d’exécution existantes dans sys.dm_exec_query_stats seront réinitialisées pour les instructions du plan.

Les nombres d’exécution dans sys.dm_exec_procedure_stats ne seront pas nécessairement réinitialisés lors de la prochaine utilisation si vous marquez une table utilisée par une procédure stockée pour la recompilation. Mais si vous marquez la procédure stockée elle-même pour la recompilation, elle sera réinitialisée lors de la prochaine utilisation. (Ne vous embêtez pas à essayer de mémoriser cela, lisez simplement le paragraphe suivant.)

Il y a de gros inconvénients avec celui-ci. Cette commande nécessite une autorisation élevée — l’utilisateur qui l’exécute nécessite des autorisations « alter » sur la table. Il nécessite également un niveau élevé de verrouillage pour terminer. Sur les systèmes occupés, j’ai vu cette commande participer à des chaînes de blocage désagréables. Soyez prudent avec ça!

Bien que cela puisse parfois être utile pour le dépannage et les tests, ne le faites pas partie du code de production.

RECOMPILER les astuces dans les en-têtes de procédure stockés

Lorsque vous avez des procédures que vous souhaitez générer de nouveaux plans, vous commencez à regarder comment utiliser les astuces dans la procédure. L’option que la plupart des gens découvrent en premier est d’utiliser ‘WITH RECOMPILE’ dans l’en-tête de la procédure stockée, comme ceci:

L’utilisation de la RECOMPILATION dans l’en—tête de procédure stockée est assez drastique – la procédure ne mettra pas en cache un plan d’exécution lorsqu’elle s’exécute. Cela signifie :

• Aucun plan d’exécution dans le cache à examiner
• Aucune statistique d’exécution enregistrée dans sys.dm_exec_query_stats
• Aucune statistique d’exécution enregistrée dans sys.dm_exec_procedure_stats

Wow, c’est une déception. Si vous devez identifier l’impact de cette procédure sur votre serveur, vous devez exécuter une sorte de session de suivi ou d’événements étendus et récolter et interpréter les résultats. Ce n’est pas rapide et l’exécution de traces peut avoir un impact sur les performances.

Une sorte de gros négatif. Pour cette raison, je n’aime pas beaucoup ce choix.

RECOMPILER des indices sur des instructions individuelles

Cette option est un peu plus de travail, mais elle a un bien meilleur rendement au fil du temps. With this option you take the RECOMPILE hint and apply it only to statements in the stored procedure where you’d like to implement the recompile, like this:

Même si vous devez utiliser l’indice sur toutes les instructions de la procédure plutôt que dans l’en-tête, cela présente toujours des avantages! En mettant l’indice de recompilation au niveau de l’instruction dans le processus, vous obtenez comme par magie:

• Plans d’exécution limités dans le cache à examiner (dernière exécution)
• Statistiques d’exécution limitées enregistrées dans sys.dm_exec_query_stats. Vous n’obtiendrez que des statistiques pour la dernière exécution, mais la colonne plan_generation_num s’incrémente, vous donnant au moins un aperçu que quelque chose se passe.
• Statistiques d’exécution enregistrées dans sys.dm_exec_procedure_stats

Le fait que vous obteniez des informations dans ces DMV peut être très utile au fil du temps. Appliquer des conseils de recompilation uniquement aux instructions qui en ont besoin est également tout simplement plus responsable – cela réduit votre risque de vous brûler au fil du temps avec la gravure du PROCESSEUR.

DBCC FREEPROCCACHE – l’option nucléaire

Ce n’est pas strictement un indice de recompilation – mais cela provoque certainement une recompilation. SQL Server a une commande que vous pouvez utiliser pour lui dire: « Recommencez avec de nouveaux plans d’exécution”. Il est généralement utilisé comme ceci:

This command makes everything start fresh– for every query:

• L’utilisation du PROCESSEUR peut augmenter à mesure que de nouveaux plans sont compilés
• Les statistiques d’exécution sont effacées dans sys.dm_exec_query_stats (immédiatement)
• Les statistiques d’exécution sont effacées dans sys.dm_exec_procedure_stats (immédiatement)

Cette commande n’est pas mauvaise – il y a un temps et une place dans le dépannage lors de son utilisation peut vous aider à trouver rapidement la cause première d’un problème de performance. Cependant, comme cela affecte l’ensemble du cache et peut également avoir un impact sur les performances, vous devez être très prudent en l’utilisant. Je ne recommande PAS d’utiliser cette commande dans des tâches régulières, des processus automatisés ou du code de production.

À partir de SQL Server 2008, vous pouvez utiliser cette commande un peu plus doucement – vous pouvez supprimer un plan spécifique du cache en utilisant le « handle de plan » ou le « handle sql » – mais bien sûr, vous devez comprendre ce qu’est ce handle et pourquoi vous voulez le supprimer. Cela pourrait être utile dans certaines situations de niche, mais dans la pratique, cela ne se produit pas beaucoup. Vous pouvez également effacer un pool de gouverneurs de ressources, mais, eh bien, vous devrez utiliser le gouverneur de ressources.

Qu’est-ce que tout cela signifie?

Lorsque vous testez, il y a une place dans votre boîte à outils pour la procédure ‘EXEC AVEC RECOMPILATION’ et la procédure sp_recompile. Utilisez-les avec précaution et méfiez-vous d’éventuelles répercussions, en particulier avec sp_recompile.

Lorsque vous implémentez des astuces dans le code, vous n’avez pas d’œillères de RECOMPILATION – vous pouvez parfois utiliser des astuces « OPTIMISER POUR » avec succès (bien que parfois vous optimisiez pour médiocre). Et parfois, sql dynamique peut également vous aider.

Mais si vous utilisez des astuces de RECOMPILATION, veuillez garder vos recompilations au niveau de l’instruction – et non dans les en-têtes de vos procédures.

Pour en savoir plus, consultez nos principes fondamentaux de la classe de reniflage de paramètres.