{"id":475,"date":"2012-07-16T16:21:00","date_gmt":"2012-07-16T16:21:00","guid":{"rendered":"\/blogs\/jonathan\/post\/Identifying-High-Compile-Time-Statements-from-the-Plan-Cache.aspx"},"modified":"2017-04-13T14:41:32","modified_gmt":"2017-04-13T18:41:32","slug":"identifying-high-compile-time-statements-from-the-plan-cache","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/identifying-high-compile-time-statements-from-the-plan-cache\/","title":{"rendered":"Identifying High Compile Time Statements from the Plan Cache"},"content":{"rendered":"<p>\nIf you don&rsquo;t already know it, I love query the plan cache in SQL Server to identify problems that might exist in a server.&nbsp; I&rsquo;ve blogged a number of ways to use plan cache data to identify and diagnose problems the last few years:\n<\/p>\n<p>\n<a href=\"https:\/\/www.sqlskills.com\/blogs\/jonathan\/finding-what-queries-in-the-plan-cache-use-a-specific-index\/\">Finding what queries in the plan cache use a specific index<\/a>    <br \/>\n<a href=\"https:\/\/www.sqlskills.com\/blogs\/jonathan\/tuning-cost-threshold-for-parallelism-from-the-plan-cache\/\">Tuning &lsquo;cost threshold for parallelism&rsquo; from the Plan Cache<\/a>    <br \/>\n<a href=\"http:\/\/sqlblog.com\/blogs\/jonathan_kehayias\/archive\/2010\/01\/08\/finding-implicit-column-conversions-in-the-plan-cache.aspx\" target=\"_blank\">Finding Implicit Column Conversions in the Plan Cache<\/a>     <br \/>\n<a href=\"http:\/\/sqlblog.com\/blogs\/jonathan_kehayias\/archive\/2009\/07\/27\/digging-into-the-sql-plan-cache-finding-missing-indexes.aspx\">Digging into the SQL Plan Cache: Finding Missing Indexes<\/a>\n<\/p>\n<p>\nLast week on the forums a question was asked about <a href=\"https:\/\/social.msdn.microsoft.com\/Forums\/sqlserver\/en-US\/2e653f9f-f123-459c-9c88-698003510843\/tracking-statement-parse-and-compile-time-using-extended-events?forum=sqldatabaseengine\" target=\"_blank\">how to use Extended Events to identify queries that took excessive amounts of time to compile<\/a>.&nbsp; What intrigued me was that the person asking the question claimed to have done this type of analysis with SQL Trace, so in 2012, that should mean that we can do it with Extended Events, right?&nbsp; As Borat would say, not so much!\n<\/p>\n<p>\nAfter seeing the events being used I took a look at the Event map, and while there is a loose mapping between the trace events that were being used to Extended Events, not all the same data is generated.&nbsp; The primary event in trace that was being used for this is the Performance Statistics event, which is has a trace event id of 165.&nbsp; If we lookup this event in the sys.trace_xe_event_map table in SQL Server 2012, we&rsquo;ll find that it maps to three different events in Extended Events; query_cache_removal_statistics, query_pre_execution_showplan, and uncached_sql_batch_statistics.&nbsp;\n<\/p>\n<p>\nImmediately, I said to myself, &ldquo;No, this isn&rsquo;t possible in Extended Events.&rdquo; simply because the overhead of turning on query_pre_execution_showplan, will kill performance of any production server (see <a href=\"https:\/\/connect.microsoft.com\/SQLServer\/feedback\/details\/732870\/sqlserver-query-post-execution-showplan-performance-impact\">http:\/\/connect.microsoft.com\/SQLServer\/feedback\/details\/732870\/sqlserver-query-post-execution-showplan-performance-impact<\/a> which is for the query_post_execution_showplan event, but all of the showplan events have the same impact).&nbsp; Looking at the columns for the other two events, left me basically in the same mindset, you can&rsquo;t do this with Extended Events.&nbsp; The uncached_sql_batch_statistics event only captures the statement that wasn&rsquo;t cached, and the query_cache_removal_statistics only gives you the execution statistics, not the parse and compile times.&nbsp; Even if you were to turn on the query_pre_execution_showplan event, it doesn&rsquo;t provide you with the CPU and duration columns for output, so it is not going to be useful here.\n<\/p>\n<p>\nTo be honest, I didn&rsquo;t think that this was going to be possible, but then I thought about some of the fun we had in our <a href=\"https:\/\/www.sqlskills.com\/sql-server-training\/iepto2\/\" target=\"_blank\">Immersion Event<\/a> week two in London looking at query compile issues, and I remembered that I wrote a parser between modules to pull compile time information from the showplans in the plan cache.&nbsp; After searching through some scratch code files in my user profile I found a relevant one and started playing around a little with the XML parsing.&nbsp; The result is the below query, which identifies the TOP 10 most expensive to compile statements in the cache currently, and includes the query stats for the statement, based on the query_hash, though it could be easily modified to use query_plan_hash to match on instead.\n<\/p>\n<pre class=\"code\">\r\n<span style=\"color: green\">-- Find high compile resource plans in the plan cache\r\n<\/span><span style=\"color: blue\">SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED<\/span><span style=\"color: gray\">;\r\n<\/span><span style=\"color: blue\">WITH <\/span>XMLNAMESPACES \r\n<span style=\"color: gray\">(<\/span><span style=\"color: blue\">DEFAULT <\/span><span style=\"color: red\">&#39;http:\/\/schemas.microsoft.com\/sqlserver\/2004\/07\/showplan&#39;<\/span><span style=\"color: gray\">)\r\n<\/span><span style=\"color: blue\">SELECT TOP <\/span>10\r\nCompileTime_ms<span style=\"color: gray\">,\r\n<\/span>CompileCPU_ms<span style=\"color: gray\">,\r\n<\/span>CompileMemory_KB<span style=\"color: gray\">,\r\n<\/span>qs<span style=\"color: gray\">.<\/span>execution_count<span style=\"color: gray\">,\r\n<\/span>qs<span style=\"color: gray\">.<\/span>total_elapsed_time<span style=\"color: gray\">\/<\/span>1000 <span style=\"color: blue\">AS <\/span>duration_ms<span style=\"color: gray\">,\r\n<\/span>qs<span style=\"color: gray\">.<\/span>total_worker_time<span style=\"color: gray\">\/<\/span>1000 <span style=\"color: blue\">as <\/span>cputime_ms<span style=\"color: gray\">,\r\n(<\/span>qs<span style=\"color: gray\">.<\/span>total_elapsed_time<span style=\"color: gray\">\/<\/span>qs<span style=\"color: gray\">.<\/span>execution_count<span style=\"color: gray\">)\/<\/span>1000 <span style=\"color: blue\">AS <\/span>avg_duration_ms<span style=\"color: gray\">,\r\n(<\/span>qs<span style=\"color: gray\">.<\/span>total_worker_time<span style=\"color: gray\">\/<\/span>qs<span style=\"color: gray\">.<\/span>execution_count<span style=\"color: gray\">)\/<\/span>1000 <span style=\"color: blue\">AS <\/span>avg_cputime_ms<span style=\"color: gray\">,\r\n<\/span>qs<span style=\"color: gray\">.<\/span>max_elapsed_time<span style=\"color: gray\">\/<\/span>1000 <span style=\"color: blue\">AS <\/span>max_duration_ms<span style=\"color: gray\">,\r\n<\/span>qs<span style=\"color: gray\">.<\/span>max_worker_time<span style=\"color: gray\">\/<\/span>1000 <span style=\"color: blue\">AS <\/span>max_cputime_ms<span style=\"color: gray\">,\r\n<\/span><span style=\"color: #ff00ff\">SUBSTRING<\/span><span style=\"color: gray\">(<\/span>st<span style=\"color: gray\">.<\/span><span style=\"color: blue\">text<\/span><span style=\"color: gray\">, (<\/span>qs<span style=\"color: gray\">.<\/span>statement_start_offset <span style=\"color: gray\">\/ <\/span>2<span style=\"color: gray\">) + <\/span>1<span style=\"color: gray\">,\r\n(<\/span><span style=\"color: blue\">CASE <\/span>qs<span style=\"color: gray\">.<\/span>statement_end_offset\r\n<span style=\"color: blue\">WHEN <\/span><span style=\"color: gray\">-<\/span>1 <span style=\"color: blue\">THEN <\/span><span style=\"color: #ff00ff\">DATALENGTH<\/span><span style=\"color: gray\">(<\/span>st<span style=\"color: gray\">.<\/span><span style=\"color: blue\">text<\/span><span style=\"color: gray\">)\r\n<\/span><span style=\"color: blue\">ELSE <\/span>qs<span style=\"color: gray\">.<\/span>statement_end_offset\r\n<span style=\"color: blue\">END <\/span><span style=\"color: gray\">- <\/span>qs<span style=\"color: gray\">.<\/span>statement_start_offset<span style=\"color: gray\">) \/ <\/span>2 <span style=\"color: gray\">+ <\/span>1<span style=\"color: gray\">) <\/span><span style=\"color: blue\">AS <\/span>StmtText<span style=\"color: gray\">,\r\n<\/span>query_hash<span style=\"color: gray\">,\r\n<\/span>query_plan_hash\r\n<span style=\"color: blue\">FROM\r\n<\/span><span style=\"color: gray\">(\r\n<\/span><span style=\"color: blue\">SELECT \r\n<\/span>c<span style=\"color: gray\">.<\/span>value<span style=\"color: gray\">(<\/span><span style=\"color: red\">&#39;xs:hexBinary(substring((@QueryHash)[1],3))&#39;<\/span><span style=\"color: gray\">, <\/span><span style=\"color: red\">&#39;varbinary(max)&#39;<\/span><span style=\"color: gray\">) <\/span><span style=\"color: blue\">AS <\/span>QueryHash<span style=\"color: gray\">,\r\n<\/span>c<span style=\"color: gray\">.<\/span>value<span style=\"color: gray\">(<\/span><span style=\"color: red\">&#39;xs:hexBinary(substring((@QueryPlanHash)[1],3))&#39;<\/span><span style=\"color: gray\">, <\/span><span style=\"color: red\">&#39;varbinary(max)&#39;<\/span><span style=\"color: gray\">) <\/span><span style=\"color: blue\">AS <\/span>QueryPlanHash<span style=\"color: gray\">,\r\n<\/span>c<span style=\"color: gray\">.<\/span>value<span style=\"color: gray\">(<\/span><span style=\"color: red\">&#39;(QueryPlan\/@CompileTime)[1]&#39;<\/span><span style=\"color: gray\">, <\/span><span style=\"color: red\">&#39;int&#39;<\/span><span style=\"color: gray\">) <\/span><span style=\"color: blue\">AS <\/span>CompileTime_ms<span style=\"color: gray\">,\r\n<\/span>c<span style=\"color: gray\">.<\/span>value<span style=\"color: gray\">(<\/span><span style=\"color: red\">&#39;(QueryPlan\/@CompileCPU)[1]&#39;<\/span><span style=\"color: gray\">, <\/span><span style=\"color: red\">&#39;int&#39;<\/span><span style=\"color: gray\">) <\/span><span style=\"color: blue\">AS <\/span>CompileCPU_ms<span style=\"color: gray\">,\r\n<\/span>c<span style=\"color: gray\">.<\/span>value<span style=\"color: gray\">(<\/span><span style=\"color: red\">&#39;(QueryPlan\/@CompileMemory)[1]&#39;<\/span><span style=\"color: gray\">, <\/span><span style=\"color: red\">&#39;int&#39;<\/span><span style=\"color: gray\">) <\/span><span style=\"color: blue\">AS <\/span>CompileMemory_KB<span style=\"color: gray\">,\r\n<\/span>qp<span style=\"color: gray\">.<\/span>query_plan\r\n<span style=\"color: blue\">FROM <\/span><span style=\"color: green\">sys<\/span><span style=\"color: gray\">.<\/span><span style=\"color: green\">dm_exec_cached_plans <\/span><span style=\"color: blue\">AS <\/span>cp\r\n<span style=\"color: gray\">CROSS APPLY <\/span><span style=\"color: green\">sys<\/span><span style=\"color: gray\">.<\/span><span style=\"color: green\">dm_exec_query_plan<\/span><span style=\"color: gray\">(<\/span>cp<span style=\"color: gray\">.<\/span>plan_handle<span style=\"color: gray\">) <\/span><span style=\"color: blue\">AS <\/span>qp\r\n<span style=\"color: gray\">CROSS APPLY <\/span>qp<span style=\"color: gray\">.<\/span>query_plan<span style=\"color: gray\">.<\/span>nodes<span style=\"color: gray\">(<\/span><span style=\"color: red\">&#39;ShowPlanXML\/BatchSequence\/Batch\/Statements\/StmtSimple&#39;<\/span><span style=\"color: gray\">) <\/span><span style=\"color: blue\">AS <\/span>n<span style=\"color: gray\">(<\/span>c<span style=\"color: gray\">)\r\n) <\/span><span style=\"color: blue\">AS <\/span>tab\r\n<span style=\"color: gray\">JOIN <\/span><span style=\"color: green\">sys<\/span><span style=\"color: gray\">.<\/span><span style=\"color: green\">dm_exec_query_stats <\/span><span style=\"color: blue\">AS <\/span>qs\r\n<span style=\"color: blue\">ON <\/span>tab<span style=\"color: gray\">.<\/span>QueryHash <span style=\"color: gray\">= <\/span>qs<span style=\"color: gray\">.<\/span>query_hash\r\n<span style=\"color: gray\">CROSS APPLY <\/span><span style=\"color: green\">sys<\/span><span style=\"color: gray\">.<\/span><span style=\"color: green\">dm_exec_sql_text<\/span><span style=\"color: gray\">(<\/span>qs<span style=\"color: gray\">.<\/span><span style=\"color: blue\">sql_handle<\/span><span style=\"color: gray\">) <\/span><span style=\"color: blue\">AS <\/span>st\r\n<span style=\"color: blue\">ORDER BY <\/span>CompileTime_ms <span style=\"color: blue\">DESC\r\nOPTION<\/span><span style=\"color: gray\">(<\/span><span style=\"color: blue\">RECOMPILE<\/span><span style=\"color: gray\">, <\/span><span style=\"color: blue\">MAXDOP <\/span>1<span style=\"color: gray\">);<\/span>\r\n<\/pre>\n<p>\nRunning this against a couple of test servers, I found a couple of queries that had 12 second compile times with 3 second execution durations.&nbsp; Now the challenge becomes testing if the query can be simplified to reduce the time it takes the optimizer to compile it, without reducing execution performance.&nbsp; The above code should work on any version of SQL Server from 2005 Service Pack 2 onwards, since the CompileTime, Compile CPU, and CompileMemory attributes were added in Service Pack 2.&nbsp; I don&rsquo;t have a SQL Server 2005 instance to test it on, but I did test this on SQL Server 2008 and 2012.\n<\/p>\n<p>\n<strong>EDIT:<\/strong>\n<\/p>\n<p>\nThis only works on 2008 and higher because the query fingerprints in dm_exec_query_stats weren&#39;t in SQL Server 2005. &nbsp;Using plan_handle will get you close, but that only tells you the plan had a high compile cost statement in it. &nbsp;You will have to dig into the plan to see which specific statement, which I am trying to avoid with the code above. &nbsp;I&#39;ll have to figure out another way to match these up based on the XML and blog the 2005 solution.&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>If you don&rsquo;t already know it, I love query the plan cache in SQL Server to identify problems that might exist in a server.&nbsp; I&rsquo;ve blogged a number of ways to use plan cache data to identify and diagnose problems the last few years: Finding what queries in the plan cache use a specific index [&hellip;]<\/p>\n","protected":false},"author":4,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[31,38,39],"tags":[],"class_list":["post-475","post","type-post","status-publish","format-standard","hentry","category-plan-cache","category-sql-server-2008","category-sql-server-2012"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.4 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>Identifying High Compile Time Statements from the Plan Cache - Jonathan Kehayias<\/title>\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\/jonathan\/identifying-high-compile-time-statements-from-the-plan-cache\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Identifying High Compile Time Statements from the Plan Cache - Jonathan Kehayias\" \/>\n<meta property=\"og:description\" content=\"If you don&rsquo;t already know it, I love query the plan cache in SQL Server to identify problems that might exist in a server.&nbsp; I&rsquo;ve blogged a number of ways to use plan cache data to identify and diagnose problems the last few years: Finding what queries in the plan cache use a specific index [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/jonathan\/identifying-high-compile-time-statements-from-the-plan-cache\/\" \/>\n<meta property=\"og:site_name\" content=\"Jonathan Kehayias\" \/>\n<meta property=\"article:published_time\" content=\"2012-07-16T16:21:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2017-04-13T18:41:32+00:00\" \/>\n<meta name=\"author\" content=\"Jonathan Kehayias\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Jonathan Kehayias\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"5 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/identifying-high-compile-time-statements-from-the-plan-cache\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/identifying-high-compile-time-statements-from-the-plan-cache\\\/\"},\"author\":{\"name\":\"Jonathan Kehayias\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/#\\\/schema\\\/person\\\/01c10d94f3648654ef706d5e6305f69c\"},\"headline\":\"Identifying High Compile Time Statements from the Plan Cache\",\"datePublished\":\"2012-07-16T16:21:00+00:00\",\"dateModified\":\"2017-04-13T18:41:32+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/identifying-high-compile-time-statements-from-the-plan-cache\\\/\"},\"wordCount\":763,\"commentCount\":16,\"articleSection\":[\"Plan Cache\",\"SQL Server 2008\",\"SQL Server 2012\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/identifying-high-compile-time-statements-from-the-plan-cache\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/identifying-high-compile-time-statements-from-the-plan-cache\\\/\",\"url\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/identifying-high-compile-time-statements-from-the-plan-cache\\\/\",\"name\":\"Identifying High Compile Time Statements from the Plan Cache - Jonathan Kehayias\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/#website\"},\"datePublished\":\"2012-07-16T16:21:00+00:00\",\"dateModified\":\"2017-04-13T18:41:32+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/#\\\/schema\\\/person\\\/01c10d94f3648654ef706d5e6305f69c\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/identifying-high-compile-time-statements-from-the-plan-cache\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/identifying-high-compile-time-statements-from-the-plan-cache\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/identifying-high-compile-time-statements-from-the-plan-cache\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Plan Cache\",\"item\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/category\\\/plan-cache\\\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"Identifying High Compile Time Statements from the Plan Cache\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/#website\",\"url\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/\",\"name\":\"Jonathan Kehayias - The Rambling DBA\",\"description\":\"The Rambling DBA\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/#\\\/schema\\\/person\\\/01c10d94f3648654ef706d5e6305f69c\",\"name\":\"Jonathan Kehayias\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/86630e27f5deecc5c393ea57fc7c3b6a068949f4fd6b5309f81de5a276f12855?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/86630e27f5deecc5c393ea57fc7c3b6a068949f4fd6b5309f81de5a276f12855?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/86630e27f5deecc5c393ea57fc7c3b6a068949f4fd6b5309f81de5a276f12855?s=96&d=mm&r=g\",\"caption\":\"Jonathan Kehayias\"},\"sameAs\":[\"http:\\\/\\\/3.209.169.194\\\/blogs\\\/jonathan\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Identifying High Compile Time Statements from the Plan Cache - Jonathan Kehayias","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\/jonathan\/identifying-high-compile-time-statements-from-the-plan-cache\/","og_locale":"en_US","og_type":"article","og_title":"Identifying High Compile Time Statements from the Plan Cache - Jonathan Kehayias","og_description":"If you don&rsquo;t already know it, I love query the plan cache in SQL Server to identify problems that might exist in a server.&nbsp; I&rsquo;ve blogged a number of ways to use plan cache data to identify and diagnose problems the last few years: Finding what queries in the plan cache use a specific index [&hellip;]","og_url":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/identifying-high-compile-time-statements-from-the-plan-cache\/","og_site_name":"Jonathan Kehayias","article_published_time":"2012-07-16T16:21:00+00:00","article_modified_time":"2017-04-13T18:41:32+00:00","author":"Jonathan Kehayias","twitter_misc":{"Written by":"Jonathan Kehayias","Est. reading time":"5 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/identifying-high-compile-time-statements-from-the-plan-cache\/#article","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/identifying-high-compile-time-statements-from-the-plan-cache\/"},"author":{"name":"Jonathan Kehayias","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/#\/schema\/person\/01c10d94f3648654ef706d5e6305f69c"},"headline":"Identifying High Compile Time Statements from the Plan Cache","datePublished":"2012-07-16T16:21:00+00:00","dateModified":"2017-04-13T18:41:32+00:00","mainEntityOfPage":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/identifying-high-compile-time-statements-from-the-plan-cache\/"},"wordCount":763,"commentCount":16,"articleSection":["Plan Cache","SQL Server 2008","SQL Server 2012"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.sqlskills.com\/blogs\/jonathan\/identifying-high-compile-time-statements-from-the-plan-cache\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/identifying-high-compile-time-statements-from-the-plan-cache\/","url":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/identifying-high-compile-time-statements-from-the-plan-cache\/","name":"Identifying High Compile Time Statements from the Plan Cache - Jonathan Kehayias","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/#website"},"datePublished":"2012-07-16T16:21:00+00:00","dateModified":"2017-04-13T18:41:32+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/#\/schema\/person\/01c10d94f3648654ef706d5e6305f69c"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/identifying-high-compile-time-statements-from-the-plan-cache\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/jonathan\/identifying-high-compile-time-statements-from-the-plan-cache\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/identifying-high-compile-time-statements-from-the-plan-cache\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/"},{"@type":"ListItem","position":2,"name":"Plan Cache","item":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/category\/plan-cache\/"},{"@type":"ListItem","position":3,"name":"Identifying High Compile Time Statements from the Plan Cache"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/","name":"Jonathan Kehayias - The Rambling DBA","description":"The Rambling DBA","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/#\/schema\/person\/01c10d94f3648654ef706d5e6305f69c","name":"Jonathan Kehayias","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/86630e27f5deecc5c393ea57fc7c3b6a068949f4fd6b5309f81de5a276f12855?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/86630e27f5deecc5c393ea57fc7c3b6a068949f4fd6b5309f81de5a276f12855?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/86630e27f5deecc5c393ea57fc7c3b6a068949f4fd6b5309f81de5a276f12855?s=96&d=mm&r=g","caption":"Jonathan Kehayias"},"sameAs":["http:\/\/3.209.169.194\/blogs\/jonathan"]}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/posts\/475","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/users\/4"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/comments?post=475"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/posts\/475\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/media?parent=475"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/categories?post=475"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/tags?post=475"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}