{"id":81,"date":"2014-09-10T00:00:00","date_gmt":"2013-01-01T00:00:00","guid":{"rendered":"https:\/\/www.sqlhammer.com\/blog\/query-cached-execution-plans"},"modified":"2026-03-18T21:51:13","modified_gmt":"2026-03-18T21:51:13","slug":"query-cached-execution-plans","status":"publish","type":"post","link":"https:\/\/sqlhammer.com\/index.php\/2014\/09\/10\/query-cached-execution-plans\/","title":{"rendered":"Query Cached Execution Plans"},"content":{"rendered":"<p>Often I need to examine an execution plan from a production server. Just as often, I can\u2019t re-run the stored procedure in that environment. There are several techniques for simulating the environment on a test server such as exporting statistics and using the un-documented <a href=\"http:\/\/www.mssqltips.com\/sqlservertip\/3246\/sql-server-performance-tuning-with-hypothetical-indexes\/\">DBCC AUTOPILOT<\/a> command.<\/p>\n<p>These methods take a bit of setup and time. So my first step is usually to hit up the cached execution plan, assuming that the query or procedure has been run since the last server restart. Below is a simple script to return the execution plan and some stats for any object whether it be a stored procedure, function, etc.<\/p>\n<h2>Query the query cache<\/h2>\n<pre><code class=\"language-\">SELECT UseCounts, Cacheobjtype, Objtype, TEXT, query_plan\nFROM sys.dm_exec_cached_plans cp\nCROSS APPLY sys.dm_exec_sql_text(plan_handle) sqlT\nCROSS APPLY sys.dm_exec_query_plan(plan_handle) qp\nINNER JOIN sys.objects o ON sqlT.objectid = o.object_id\nINNER JOIN sys.schemas s ON s.schema_id = o.schema_id\nWHERE o.name = 'object_name'\n\tAND s.name = 'schema_name';<\/code><\/pre>\n<hr \/>\n<p><img decoding=\"async\" src=\"\/wp-content\/uploads\/dh.jpg\" alt=\"\" \/><strong>Written by Derik Hammer of <a href=\"http:\/\/www.sqlhammer.com\/\">SQL Hammer<\/a><\/strong><\/p>\n<p>Derik is a data professional focusing on Microsoft SQL Server. His passion focuses around <a href=\"http:\/\/www.sqlhammer.com\/blog\/category\/high-availability\/\">high-availability<\/a>, <a href=\"http:\/\/www.sqlhammer.com\/blog\/category\/general\/disaster-recovery\/\">disaster recovery<\/a>, continuous integration, and automated maintenance. his experience has spanned long-term database administration, consulting, and\u00a0entrepreneurial ventures.<\/p>\n<p>Derik gives the SQL community credit for plugging the gaps in his knowledge when he was a junior DBA and, now that his skills have matured, started <a href=\"http:\/\/www.sqlhammer.com\/\">SQLHammer.com<\/a> as one small way to give back and continue the cycle of shared learning.<\/p>\n<p>Derik is the owner and lead author of SQL Hammer, a <a href=\"http:\/\/www.sqlhammer.com\/\">Microsoft SQL Server resource<\/a>.<\/p>\n<p>For more information, visit <a href=\"http:\/\/www.sqlhammer.com\/\">http:\/\/www.sqlhammer.com<\/a>. Follow Derik on <a href=\"http:\/\/twitter.com\/SQLHammer\">Twitter<\/a> for <strong>SQL tips and chat<\/strong><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Often I need to examine an execution plan from a production server. Just as often, I can\u2019t re-run the stored procedure in that environment. There are several techniques for simulating the environment on a test server such as exporting statistics and using the un-documented DBCC AUTOPILOT command. These methods take a bit of setup and [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":523,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-81","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts\/81","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/comments?post=81"}],"version-history":[{"count":1,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts\/81\/revisions"}],"predecessor-version":[{"id":344,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts\/81\/revisions\/344"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/media\/523"}],"wp:attachment":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/media?parent=81"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/categories?post=81"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/tags?post=81"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}