{"id":125,"date":"2012-04-20T15:03:04","date_gmt":"2012-04-20T14:03:04","guid":{"rendered":"http:\/\/www.darrencoupland.com\/blog\/?p=125"},"modified":"2012-08-14T14:39:33","modified_gmt":"2012-08-14T13:39:33","slug":"show-sql-plans-in-plan-cache-for-a-table-name-or-matching-partial-query-text","status":"publish","type":"post","link":"https:\/\/www.darrencoupland.com\/blog\/2012\/04\/show-sql-plans-in-plan-cache-for-a-table-name-or-matching-partial-query-text\/","title":{"rendered":"Show SQL Plans in Plan Cache for a table name or matching partial query text"},"content":{"rendered":"

Ever wondered what queries are calling what tables or do you have a query and want to see how many times it’s been called (since last plan cache flush) and view the query plan for it.<\/p>\n

These queries are a good to use when performance tuning indexes and should be a part of your toolbox. \u00a0You need to know what queries are using your table before you can start to plan the best indexing strategy for your table.<\/p>\n

Note: Query#2 will only work in SQL Server Management Studio 2008 onwards.<\/p>\n

Here is a quick and easy way to do this:<\/p>\n

Query#1<\/span>\u00a0<\/strong><\/p>\n

This query will show you the plan handle value you need to extract the query plan from the plan cache, it will also show you how\u00a0many times the query has been executed using that plan handle.<\/p>\n

-- Shows SQL plan handles in plan cache for a specific piece of SQL text\r\nSELECT plan_handle, usecounts AS ExecutionCount, st.text\r\nFROM sys.dm_exec_cached_plans\r\nCROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st\r\nWHERE text LIKE N'%<Enter Table name or partial Query here>%'\r\nORDER BY usecounts DESC<\/pre>\n
\u00a0Query#2\u00a0<\/span><\/strong><\/p>\n
Using the plan_handle value from Query#1 insert it into the where clause and this will give you XML result which you can click on to view the graphical query plan.<\/p>\n
-- Show XML query plan ** Only works with SSMS2008 **\r\nSELECT *\r\nFROM\r\nsys.dm_exec_query_plan(<Paste Query "plan_handle" value from previous query here>)<\/pre>\n
 <\/p>\n
Example:<\/p>\n
Query#1 Results of ‘%EVENTINBOX%’\u00a0\"\"<\/p>\n
Query#2 – XML result of plan_handle (0x06001100A23AD31D40031579060000000000000000000000) from highest exection count of Query#1\"\"<\/p>\n
 <\/p>\n
Query#2 – Visual Query Plan after clicking on XML result show above\"\"<\/p>\n","protected":false},"excerpt":{"rendered":"
Ever wondered what queries are calling what tables or do you have a query and want to see how many times it’s been called (since last plan cache flush) and view the query plan for it.<\/p>\n
These queries are a good to use when performance tuning indexes and should be a part of your toolbox.  You need to know what queries are using your table before you can start to plan the best indexing strategy for your table.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[3],"tags":[8,22,24,23,60],"class_list":["post-125","post","type-post","status-publish","format-standard","hentry","category-dynamics-ax-2009","tag-ax2009","tag-performance","tag-plan-cache","tag-query-plans","tag-sql-server"],"_links":{"self":[{"href":"https:\/\/www.darrencoupland.com\/blog\/wp-json\/wp\/v2\/posts\/125","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.darrencoupland.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.darrencoupland.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.darrencoupland.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.darrencoupland.com\/blog\/wp-json\/wp\/v2\/comments?post=125"}],"version-history":[{"count":0,"href":"https:\/\/www.darrencoupland.com\/blog\/wp-json\/wp\/v2\/posts\/125\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.darrencoupland.com\/blog\/wp-json\/wp\/v2\/media?parent=125"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.darrencoupland.com\/blog\/wp-json\/wp\/v2\/categories?post=125"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.darrencoupland.com\/blog\/wp-json\/wp\/v2\/tags?post=125"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}