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.
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.
Note: Query#2 will only work in SQL Server Management Studio 2008 onwards.
Here is a quick and easy way to do this:
Query#1
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 many times the query has been executed using that plan handle.
-- Shows SQL plan handles in plan cache for a specific piece of SQL text
SELECT plan_handle, usecounts AS ExecutionCount, st.text
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
WHERE text LIKE N'%<Enter Table name or partial Query here>%'
ORDER BY usecounts DESC
Query#2
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.
-- Show XML query plan ** Only works with SSMS2008 **
SELECT *
FROM
sys.dm_exec_query_plan(<Paste Query "plan_handle" value from previous query here>)
Example:
Query#1 Results of ‘%EVENTINBOX%’ 
Query#2 – XML result of plan_handle (0x06001100A23AD31D40031579060000000000000000000000) from highest exection count of Query#1
Query#2 – Visual Query Plan after clicking on XML result show above
