Capture long running queries using query_hash

Sql server 2008 provides powerful feature to capture expensive queries using query identifier also called as query finger print.

Whenever sql server executes any query, it will generate unique query_hash which will be used to troubleshoot expensive queries.

All Compiled plans can be retrieved from plan_handle and sql_text can be retrived from sql_handle.

In order to demo this usecase, I would like to take use of AdventureWorks2008R2. Same will be applicable for next versions.

Use AdventureWorks2008R2

Go

select * from Person.person where BusinessEntityID > 1 and BusinessEntityID < 30000

go

select * from Person.person where BusinessEntityID > 1 and BusinessEntityID < 30000

go

select * from Person.person where BusinessEntityID > 1 and BusinessEntityID < 3

go

select * from Person.person where BusinessEntityID > 1 and BusinessEntityID < 500

go

select * from Person.person where BusinessEntityID > 1 and BusinessEntityID < 1

go

select * from Person.person where BusinessEntityID > 1 and BusinessEntityID < 2

go

select * from Person.person where BusinessEntityID > 1 and BusinessEntityID < 30000

go

script to retrive sql plans :-

SELECT query_hash, query_plan_hash ,execution_count, eq.max_elapsed_time,
eq.creation_time,last_execution_time FROM sys.dm_exec_query_stats eq 
CROSS APPLY sys.dm_exec_sql_text(plan_handle) es 
OUTER APPLY sys.dm_exec_query_plan([plan_handle]) AS qp 
where text like '%BusinessEntityID%' 
option(recompile)

Resultset :-

If you look into above result set, for same query we can see multiple plans generated.

However we see unique values among the result set. i.e query_hash or query_plan_hash

Query_hash is unique foot print for every query.

Apart from query_hash there are few other interesting columns

execution_count :- It gives number of times that the plan has been executed since it was last compiled.

max_elapsed_time :- It reported in microseconds (but only accurate to milliseconds), for any completed execution of this plan.

last_elapsed_time :- Elapsed time, reported in microseconds (but only accurate to milliseconds), for the most recently completed execution of this plan.

max_elapsed_time :-  Maximum elapsed time, reported in microseconds (but only accurate to milliseconds), for any completed execution of this plan.

If we aggregate based query_hash we will come to know multiple plans and execution time for same query.

 Note :- Change query_hash value in below script , with the one you retrieved from above result set.

SELECT es.text,eq.plan_handle, query_hash, query_plan_hash, eq.sql_handle, eq.max_elapsed_time,eq.creation_time, qp.query_plan,last_execution_time,last_worker_time,total_worker_time,total_worker_time/execution_count AS avg_cpu_cost, execution_count
FROM sys.dm_exec_query_stats eq
CROSS APPLY sys.dm_exec_sql_text(plan_handle) es
OUTER APPLY sys.dm_exec_query_plan([plan_handle]) AS qp
where query_hash =0xC2BBE9FD7315F4F3
option(recompile)

By executing above query, we will come to know unique query with different execution plans and max execution time for each plan.