Étant le jour 12 du mois de commande DBCC à SteveStedman.com , la commande DBCC présentée aujourd’hui est DBCC FREEPROCCACHE.
Description :
DBCC FREEPROCCACHE est utilisé pour purger tous les plans de requête analysés de la mémoire. Ceci est couramment utilisé dans les environnements de développement, mais pas aussi courant dans un environnement de production.
L’utilisation dans un environnement de développement est courante, par exemple lorsque vous travaillez sur le réglage des performances ou le paramétrage des requêtes. Vous pouvez effacer le cache de procédures avec DBCC FreeProcCache, exécuter le programme ou la page Web qui peut utiliser la base de données, puis voir ce qui se trouve dans le cache de procédures. Cela peut être utile pour trouver des requêtes qui peuvent avoir besoin d’être paramétrées. Une autre façon d’utiliser serait de savoir quelles requêtes sont exécutées par un programme. Pour ce faire, vous commenceriez par travailler avec une base de données qui n’est pas utilisée par d’autres, en effaçant le cache de procédures avec DBCC FreeProcCache, puis exécutez le programme que vous essayez de comprendre, puis regardez ce qui se trouve dans le cache, encore une fois c’est quelque chose qui pourrait être fait dans un environnement de développement ou de test, mais je ne recommanderais pas de le faire en production.
L’utilisation dans un environnement de production devrait être rare, c’est l’une des choses courantes à essayer lorsque SQL Server a des difficultés. Si vous êtes au point que SQL Server est extrêmement lent à répondre et que vous n’avez pas réussi à trouver la cause, une chose à essayer est de libérer le cache de procédure avec DBCC FreeProcCache et de voir si cela résout le problème.
Syntaxe DBCC FreeProcCache:
dbcc freeproccache
Exemple :
L’exemple suivant provient d’un environnement de développement utilisant la base de données AdventureWorks2012.
Nous nous connectons d’abord à AdventureWorks2012 et voyons ce qui se trouve dans le cache.
USE AdventureWorks2012;GOSELECT size_in_bytes, text FROM sys.dm_exec_cached_plans CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st;
Ici, nous voyons qu’il y en a beaucoup dans le cache. Ensuite, nous nettoyons le cache avec DBCC FreeProcCache et examinons à nouveau ce qui se trouve dans le cache.
DBCC FREEPROCCACHE;SELECT size_in_bytes, text FROM sys.dm_exec_cached_plans CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st;
Après avoir exécuté DBCC FreeProcCache, vous pouvez voir qu’il ne reste plus rien dans le cache.
Lorsque la requête suivante est exécutée, elle devra être réparée plutôt que d’utiliser une requête déjà analysée dans le cache. Cela prendra un peu plus de temps que s’il y avait déjà un plan analysé à exécuter. Permet d’exécuter 3 requêtes, puis de jeter un œil au cache.
GOSELECT FirstName, MiddleName, LastName FROM person.Person p WHERE FirstName like 'John';GOSELECT FirstName, MiddleName, LastName FROM person.Person p WHERE FirstName like 'Mary';GOSELECT FirstName, MiddleName, LastName FROM person.Person p WHERE FirstName like 'Bill';
Notez l’instruction GO entre chaque requête. Cela indique à SSMS d’exécuter chaque requête en tant que lot séparé. Sans l’instruction GO, les 3 requêtes auraient été analysées en un seul lot.
Nous voyons ici les résultats des trois requêtes. Les deux premiers ont renvoyé des résultats et le troisième n’avait aucune ligne dans le jeu de résultats. Regardons maintenant le cache
SELECT size_in_bytes, text FROM sys.dm_exec_cached_plans CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st;
Ci-dessous, nous voyons un total de 20 éléments dans le cache maintenant. l’élément supérieur surligné en bleu est la requête que nous avons utilisée pour voir ce qui se trouvait dans le cache, le deuxième bloc surligné en rouge contient les 3 requêtes ci-dessus, et le troisième reste sont des requêtes exécutées par SQL ou d’autres requêtes de support. Par exemple, la ligne 13 est le dm_exec_sql_text qui est appelé à partir de la requête ci-dessus qui vérifie le plan.
Si nous voulions filtrer cela uniquement aux requêtes que nous avions écrites, vous pouvez le faire en ajoutant un texte WHERE COMME la clause WHERE à la requête comme indiqué ici.
SELECT size_in_bytes, text FROM sys.dm_exec_cached_plans CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS stWHERE text LIKE N'SELECT FirstName%';
Ici, nous voyons que seules les trois requêtes apparaissent et que chacune de ces trois prend environ 40 Ko de mémoire sur SQL Server. 
Alors pourquoi y a-t-il trois copies de la même instruction SELECT, cela semble un peu inutile. En effet, pour plus d’informations, consultez un article précédent intitulé Combien de mémoire cache de procédure une instruction SQL occupe-t-elle? Il existe des moyens de corriger cela.
Utilisation de DBCC FreeProcCache Pour Un Handle de plan spécifique
Si vous vouliez effacer un seul handle de plan, et non tous les handles de plan, vous pouvez utiliser le paramètre facultatif appelé @handle.
Pour obtenir le handle du plan, nous commençons par modifier notre requête précédente pour nous montrer ce qui se trouve dans le cache du plan. Vous pouvez laisser de côté la clause WHERE sur votre propre système, mais je l’ai ici pour nous montrer seulement les trois requêtes en question d’en haut.
SELECT size_in_bytes, text, plan_handle FROM sys.dm_exec_cached_plans CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS stWHERE text LIKE N'SELECT FirstName%';
Ici, nous voyons les trois mêmes plans de requête antérieurs, avec une colonne d’addition appelée plan_handle. Pour libérer un seul handle de plan, nous copions simplement le handle de plan numérique et l’ajoutons à la requête DBCC FreeProcCache.
DBCC FREEPROCCACHE(0x060007000100FF3310B8DA7D0600000001000000000000000000000000000000000000000000000000000000);SELECT size_in_bytes, text, plan_handle FROM sys.dm_exec_cached_plans CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS stWHERE text LIKE N'SELECT FirstName%';
Où nous ne voyons que 2 des trois requêtes d’origine dans le cache de plan.
Quelle est la taille de mon cache de procédure ?
Vous pouvez exécuter la requête suivante pour vérifier la taille de votre cache de procédure.
SELECT count(*) AS NumPlans, objtype as Type,SUM(size_in_bytes)/(1024.0*1024.0) AS size_in_mbFROM sys.dm_exec_cached_plansGROUP BY objtype;
Qui produit les résultats suivants sur mon serveur de test.
Rapports d’intégrité de base de données et Cache de plan
Vous pouvez également afficher le cache de plan à l’aide de l’application Rapports d’intégrité de base de données, comme indiqué ici.
Remarques:
Pour plus d’informations, voir TSQL Wiki DBCC freeproccache.
Mois de commande DBCC à SteveStedman.com est presque aussi amusant que de manger de la jello.
Steve et l’équipe de Stedman Solutions sont là pour tous vos besoins SQL Server.
Contactez-nous dès aujourd’hui pour votre consultation gratuite de 30 minutes..
Nous sommes prêts à vous aider!
