{"id":851,"date":"2017-07-27T09:44:45","date_gmt":"2017-07-27T16:44:45","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/erin\/?p=851"},"modified":"2017-07-27T09:44:45","modified_gmt":"2017-07-27T16:44:45","slug":"sqlskills-101-the-other-bad-thing-about-clearing-procedure-cache","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/erin\/sqlskills-101-the-other-bad-thing-about-clearing-procedure-cache\/","title":{"rendered":"SQLskills 101: The Other Bad Thing About Clearing Procedure Cache"},"content":{"rendered":"<p>Every time you execute a query against SQL Server, it goes through optimization and compilation and a query plan is generated. Most of the time that plan goes into cache (there&#8217;s always exceptions, which is a discussion for a separate time), which means that the next time that query executes, SQL Server can use the same plan.\u00a0 Now, re-using the same plan is typically a good thing; SQL Server doesn&#8217;t have to go through full optimization and compilation again.\u00a0 But sometimes that plan isn&#8217;t ideal for the query and you want to remove it from the plan cache.\u00a0 You can do this manually.\u00a0 Plans can also age out if they&#8217;re not being used, but if I want to purposely evict a plan from cache, I have several options:<\/p>\n<ul>\n<li>issue <a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/relational-databases\/system-stored-procedures\/sp-recompile-transact-sql\">sp_recompile<\/a> for the object<\/li>\n<li>use <a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/t-sql\/database-console-commands\/dbcc-freeproccache-transact-sql\">DBCC FREEPROCCACHE<\/a> with a plan_handle to remove a specific plan from cache<\/li>\n<li>use DBCC FLUSHPROCINDB (&lt;database_id&gt;) to remove all plans for a database from cache<\/li>\n<li>issue <a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/t-sql\/statements\/alter-database-scoped-configuration-transact-sql\">ALTER  SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE<\/a> to remove all plans for a database from cache<\/li>\n<li>use <a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/t-sql\/database-console-commands\/dbcc-freeproccache-transact-sql\">DBCC FREEPROCCACHE<\/a> to remove *all* plans from cache<\/li>\n<\/ul>\n<p>Ideally, you should remove only what&#8217;s absolutely necessary.\u00a0 Using DBCC FREEPROCCACHE is a sledgehammer approach and typically creates a spike in CPU as all subsequent queries need to have their plans re-generated.\u00a0 Glenn gives examples on how to use each statement (and others) in his post <a href=\"https:\/\/www.sqlskills.com\/blogs\/glenn\/eight-different-ways-to-clear-the-sql-server-plan-cache\/\">Eight Different Ways to Clear the SQL Server Plan Cache<\/a>, and I want to show you one more thing that happens when you clear a plan (or all plans) from cache.<\/p>\n<p>For this demo script, I recommend running it against a TEST\/DEV\/QA environment because I am removing plans from cache which can adversely affect performance.<\/p>\n<p>We&#8217;ll\u00a0 run one statement and one stored procedure multiple times against the WideWorldImporters database:<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\n\/*\r\nCreate a stored procedure to use for testing\r\n*\/\r\nUSE &#x5B;WideWorldImporters];\r\nGO\r\n\r\nDROP PROCEDURE IF EXISTS &#x5B;Application].&#x5B;usp_GetCountryInfo];\r\nGO\r\n\r\nCREATE PROCEDURE &#x5B;Application].&#x5B;usp_GetCountryInfo]\r\n@Country_Name NVARCHAR(60)\r\nAS\r\nSELECT *\r\nFROM &#x5B;Application].&#x5B;Countries] &#x5B;c]&#x5B;\/c]\r\nJOIN &#x5B;Application].&#x5B;StateProvinces] &#x5B;s]\r\nON &#x5B;s].&#x5B;CountryID] = &#x5B;c]&#x5B;\/c].&#x5B;CountryID]\r\nWHERE &#x5B;c]&#x5B;\/c].&#x5B;CountryName] = @Country_Name;\r\nGO\r\n\r\n\/*\r\nRemove everything from cache\r\n*\/\r\nDBCC FREEPROCCACHE;\r\nGO\r\n\r\n\/*\r\nRun the stored procedure 20 times\r\n*\/\r\nEXECUTE &#x5B;Application].&#x5B;usp_GetCountryInfo] N'United States';\r\nGO 20\r\n\r\n\/*\r\nRun the query 20 times\r\n*\/\r\nSELECT\r\n&#x5B;s].&#x5B;StateProvinceName],\r\n&#x5B;s].&#x5B;SalesTerritory],\r\n&#x5B;s].&#x5B;LatestRecordedPopulation],\r\n&#x5B;s].&#x5B;StateProvinceCode]\r\nFROM &#x5B;Application].&#x5B;Countries] &#x5B;c]&#x5B;\/c]\r\nJOIN &#x5B;Application].&#x5B;StateProvinces] &#x5B;s]\r\nON &#x5B;s].&#x5B;CountryID] = &#x5B;c]&#x5B;\/c].&#x5B;CountryID]\r\nWHERE &#x5B;c]&#x5B;\/c].&#x5B;CountryName] = 'United States';\r\nGO 20\r\n<\/pre>\n<p>Now let&#8217;s see what&#8217;s in cache, and what&#8217;s also in <a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/relational-databases\/system-dynamic-management-views\/sys-dm-exec-query-stats-transact-sql\">sys.dm_exec_query_stats<\/a> (which gives us execution statistics).<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nSELECT\r\n&#x5B;qs].&#x5B;last_execution_time],\r\n&#x5B;qs].&#x5B;execution_count],\r\n&#x5B;qs].&#x5B;total_logical_reads]\/&#x5B;qs].&#x5B;execution_count] &#x5B;AvgLogicalReads],\r\n&#x5B;qs].&#x5B;max_logical_reads],\r\n&#x5B;t].&#x5B;text]&#x5B;\/text],\r\n&#x5B;p].&#x5B;query_plan]\r\nFROM sys.dm_exec_query_stats &#x5B;qs]\r\nCROSS APPLY sys.dm_exec_sql_text(&#x5B;qs].sql_handle) &#x5B;t]\r\nCROSS APPLY sys.dm_exec_query_plan(&#x5B;qs].&#x5B;plan_handle]) &#x5B;p]\r\nWHERE &#x5B;t].&#x5B;text]&#x5B;\/text] LIKE '%Application%';\r\nGO\r\n<\/pre>\n<figure id=\"attachment_852\" aria-describedby=\"caption-attachment-852\" style=\"width: 1024px\" class=\"wp-caption alignleft\"><a href=\"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/2017\/07\/query_stats_output.jpg\"><img fetchpriority=\"high\" decoding=\"async\" class=\"size-large wp-image-852\" src=\"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/2017\/07\/query_stats_output-1024x49.jpg\" alt=\"Text, plan, and query stats from the DMOs\" width=\"1024\" height=\"49\" srcset=\"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/2017\/07\/query_stats_output-1024x49.jpg 1024w, https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/2017\/07\/query_stats_output-300x14.jpg 300w, https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/2017\/07\/query_stats_output-900x43.jpg 900w, https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/2017\/07\/query_stats_output.jpg 1762w\" sizes=\"(max-width: 1024px) 100vw, 1024px\" \/><\/a><figcaption id=\"caption-attachment-852\" class=\"wp-caption-text\">Text, plan, and query stats from the DMOs<\/figcaption><\/figure>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>Cool, I see that each one executed 20 times, and in addition to the text and the plan I pulled information about I\/Os.<\/p>\n<p>Now let&#8217;s clear procedure cache, and we&#8217;ll take the good old sledgehammer approach because we&#8217;re lazy and run DBCC FREEPROCACHE.\u00a0 Then run the query to see what&#8217;s in sys.dm_exec_query_stats&#8230;<\/p>\n<figure id=\"attachment_853\" aria-describedby=\"caption-attachment-853\" style=\"width: 910px\" class=\"wp-caption alignleft\"><a href=\"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/2017\/07\/query-stats-after-freeproccache.jpg\"><img decoding=\"async\" class=\"size-full wp-image-853\" src=\"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/2017\/07\/query-stats-after-freeproccache.jpg\" alt=\"DMO information *after* running DBCC FREEPROCCACHE\" width=\"910\" height=\"111\" srcset=\"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/2017\/07\/query-stats-after-freeproccache.jpg 910w, https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/2017\/07\/query-stats-after-freeproccache-300x37.jpg 300w, https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/2017\/07\/query-stats-after-freeproccache-900x110.jpg 900w\" sizes=\"(max-width: 910px) 100vw, 910px\" \/><\/a><figcaption id=\"caption-attachment-853\" class=\"wp-caption-text\">DMO information *after* running DBCC FREEPROCCACHE<\/figcaption><\/figure>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>Do you see what happened?\u00a0 In addition to removing the plan, we also lost all the query execution stats from dm_exec_query_stats.\u00a0 We have no history about query performance (unless we were writing it to another table on a regular basis, or have a third-party tool that captures baselines).\u00a0 If you don&#8217;t believe me, just run SELECT * FROM sys.dm_exec_query_stats&#8230;all that data is gone (but will start to re-populate as you run queries and plans go into cache).<\/p>\n<p>This is important to know!  If you&#8217;re troubleshooting a performance issue and you want to try removing the plan from cache, be aware that you&#8217;re removing execution statistics too (and maybe save those off first in case you need them for reference\/comparison later).<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Every time you execute a query against SQL Server, it goes through optimization and compilation and a query plan is generated. Most of the time that plan goes into cache (there&#8217;s always exceptions, which is a discussion for a separate time), which means that the next time that query executes, SQL Server can use the [&hellip;]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[13,15,47],"tags":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v21.9.1 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>SQLskills 101: The Other Bad Thing About Clearing Procedure Cache - Erin Stellato<\/title>\n<meta name=\"description\" content=\"Clearing procedure cache in SQL Server does more than simply remove the plan from cache; do you know what other information you lose?\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.sqlskills.com\/blogs\/erin\/sqlskills-101-the-other-bad-thing-about-clearing-procedure-cache\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SQLskills 101: The Other Bad Thing About Clearing Procedure Cache - Erin Stellato\" \/>\n<meta property=\"og:description\" content=\"Clearing procedure cache in SQL Server does more than simply remove the plan from cache; do you know what other information you lose?\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/erin\/sqlskills-101-the-other-bad-thing-about-clearing-procedure-cache\/\" \/>\n<meta property=\"og:site_name\" content=\"Erin Stellato\" \/>\n<meta property=\"article:published_time\" content=\"2017-07-27T16:44:45+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/2017\/07\/query_stats_output-1024x49.jpg\" \/>\n<meta name=\"author\" content=\"Erin Stellato\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Erin Stellato\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/sqlskills-101-the-other-bad-thing-about-clearing-procedure-cache\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/sqlskills-101-the-other-bad-thing-about-clearing-procedure-cache\/\",\"name\":\"SQLskills 101: The Other Bad Thing About Clearing Procedure Cache - Erin Stellato\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#website\"},\"datePublished\":\"2017-07-27T16:44:45+00:00\",\"dateModified\":\"2017-07-27T16:44:45+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158\"},\"description\":\"Clearing procedure cache in SQL Server does more than simply remove the plan from cache; do you know what other information you lose?\",\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/sqlskills-101-the-other-bad-thing-about-clearing-procedure-cache\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/erin\/sqlskills-101-the-other-bad-thing-about-clearing-procedure-cache\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/sqlskills-101-the-other-bad-thing-about-clearing-procedure-cache\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SQLskills 101: The Other Bad Thing About Clearing Procedure Cache\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/\",\"name\":\"Erin Stellato\",\"description\":\"The SQL Sequel\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158\",\"name\":\"Erin Stellato\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/0c8b485bd54ea26b57e99f79b525f409?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/0c8b485bd54ea26b57e99f79b525f409?s=96&d=mm&r=g\",\"caption\":\"Erin Stellato\"},\"sameAs\":[\"http:\/\/3.209.169.194\/blogs\/erin\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/author\/erin\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"SQLskills 101: The Other Bad Thing About Clearing Procedure Cache - Erin Stellato","description":"Clearing procedure cache in SQL Server does more than simply remove the plan from cache; do you know what other information you lose?","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.sqlskills.com\/blogs\/erin\/sqlskills-101-the-other-bad-thing-about-clearing-procedure-cache\/","og_locale":"en_US","og_type":"article","og_title":"SQLskills 101: The Other Bad Thing About Clearing Procedure Cache - Erin Stellato","og_description":"Clearing procedure cache in SQL Server does more than simply remove the plan from cache; do you know what other information you lose?","og_url":"https:\/\/www.sqlskills.com\/blogs\/erin\/sqlskills-101-the-other-bad-thing-about-clearing-procedure-cache\/","og_site_name":"Erin Stellato","article_published_time":"2017-07-27T16:44:45+00:00","og_image":[{"url":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/2017\/07\/query_stats_output-1024x49.jpg"}],"author":"Erin Stellato","twitter_misc":{"Written by":"Erin Stellato","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/sqlskills-101-the-other-bad-thing-about-clearing-procedure-cache\/","url":"https:\/\/www.sqlskills.com\/blogs\/erin\/sqlskills-101-the-other-bad-thing-about-clearing-procedure-cache\/","name":"SQLskills 101: The Other Bad Thing About Clearing Procedure Cache - Erin Stellato","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#website"},"datePublished":"2017-07-27T16:44:45+00:00","dateModified":"2017-07-27T16:44:45+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158"},"description":"Clearing procedure cache in SQL Server does more than simply remove the plan from cache; do you know what other information you lose?","breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/sqlskills-101-the-other-bad-thing-about-clearing-procedure-cache\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/erin\/sqlskills-101-the-other-bad-thing-about-clearing-procedure-cache\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/sqlskills-101-the-other-bad-thing-about-clearing-procedure-cache\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/erin\/"},{"@type":"ListItem","position":2,"name":"SQLskills 101: The Other Bad Thing About Clearing Procedure Cache"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/erin\/","name":"Erin Stellato","description":"The SQL Sequel","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/erin\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158","name":"Erin Stellato","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/0c8b485bd54ea26b57e99f79b525f409?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/0c8b485bd54ea26b57e99f79b525f409?s=96&d=mm&r=g","caption":"Erin Stellato"},"sameAs":["http:\/\/3.209.169.194\/blogs\/erin"],"url":"https:\/\/www.sqlskills.com\/blogs\/erin\/author\/erin\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/posts\/851"}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/comments?post=851"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/posts\/851\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/media?parent=851"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/categories?post=851"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/tags?post=851"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}