Thanks to Balmukund for his help. I wanted to know how many times a query has been executed from its query plan. Here is quick steps for this.
Step 1 - Create necessary tables for demo
use tempdb
CREATE TABLE DBO.TESTTable
(
Id INT Identity(10,10),
Name VARCHAR(20)
)
INSERT INTO DBO.TESTTable(Name)
Values ('Testvalue')
GO 50
Step 2 - Run below query
SELECT * FROM TESTTable WHERE Id = 20
Step 3 - I would like to know the plan for this query - Below DMV query would help
SELECT *
FROM sys.dm_exec_query_stats
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
CROSS APPLY sys.dm_exec_query_plan(plan_handle)
WHERE TEXT LIKE '%TestTable%'
Step 4 - Fetch the Plan Handle from above step. Use the DMV sys.dm_exec_query_stats to find execution count of that plan based on plan handle
SELECT execution_count,* FROM sys.dm_exec_query_stats where plan_handle = 0x0600020078374D0040213585000000000000000000000000
This execution count would tell you how many times this plan has been executed. To check plan is cached query cached_plans DMV can be used
SELECT * FROM sys.dm_exec_cached_plans where plan_handle = 0x0600020078374D0040213585000000000000000000000000
Hope it was useful. Have a Good Day.
March 15, 2010
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment