"No one is harder on a talented person than the person themselves" - Linda Wilkinson ; "Trust your guts and don't follow the herd" ; "Validate direction not destination" ;

March 15, 2010

How many times my query has been executed ?

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.

No comments: