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.

 

 

script to retrive sql plans :-

 

Resultset :-

Screen Shot 2015-04-22 at 6.22.47 PM

 

 

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.

 

 

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