SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Query Performance Report

By Steven Guest,

I find this really useful for those “nothing has changed” type problems.
Run the entire script. I have used a pre-existing DBA adminstration database named dbadmin to store the collected data.
Do a find and replace if you want to store it elsewhere.

The script creates a table to store the query stats, text & plans, a daily job to collect it and a procedure to run the report.
Snapshots are taken daily by default but can be run more often if the cache clears out too quickly.
Ideally Data Collector would be used for this, but it doesn't capture enough information and sql_handles aren't always persistent.

After the script has been run, a report can be generated by passing a query hash to the stored procedure.
exec usp_QueryStatsRep <query_hash> --eg. 0x574A95C5903BAFD0 (no quotes)

The results include the real time stats from sys.dm_exec_query_stats as well.
It saves us from having to do a manual snapshot to get statistics for the current day included.

With the output we can see day to day changes in query performance and explain any differences.
Different query_plan_hash and poor performance compared to other days = bad statistics, hint/plan guide needed etc.
Same query_plan_hash but double the execution time = data volume changes, hardware resources etc.

If the report shows a problem with a query or plan, then we can dive further into the query_stats table to get the actual XML plan and full text.

In the example below, the 0xA9166F5F631D74ED plan is giving terrible performance compared to the others.

Total article views: 5006 | Views in the last 30 days: 6
Related Articles

Which Stored Procedure changed

Which Stored Procedure changed


Storing IPv4 Addresses for Performance

An in-depth analysis on how to store IPv4 addresses to achieve maximum efficiency and performance in...


Query Slow Performance

Query Slow Performance


Replay your workload through the Query Store Replay script!

One of the great features of the Query Store is that it stores query execution plans and runtime per...


Finding Your Query in Query Store

Query Store is pretty amazing. I’m loving working with it. I think it’s likely to change how query t...