Share via

Query Hash Statistics, a query cost analysis tool, now available for download

  • Article

Some time ago I described the query fingerprint and query plan fingerprint (a.k.a. query hash / query plan hash) features that were added in SQL Server 2008. A fingerprint identifies all queries or all plans that have the same “shape”. With query fingerprints you can get the cumulative cost of all executions of a query even if the query is non-parameterized and has different inline literal values for each execution. Previously, the only way to get this type of query performance data was to capture a Profiler trace and run the trace through a post-processing tool like ReadTrace or ClearTrace. Now you can do it with a few DMV queries.

Query Hash Statistics is a free download that collects historical query and query plan fingerprint statistics and allows you easily to view the data in SSMS and see the true cumulative cost of the queries in each of your databases. You can use the project as-is, or – if you’re the tinkering type – disassemble it and build your own custom query hash stats solution. (Source code is included.)

Download Query Hash Statistics @ https://code.msdn.microsoft.com/QueryHashStatistics

 

 

Project Description:

Query Hash Statistics collects Microsoft SQL Server query execution statistics and uploads the data to a central warehouse. The solution also includes a set of reports to analyze and visualize this data within SQL Server Management Studio (SSMS). Query Hash Statistics differs from most other query cost analysis tools in that it builds on the "query fingerprint" and "query plan fingerprint" features that were added in SQL Server 2008. These features make it possible to calculate the cumulative cost of all executions of a query, even when the application doesn't parameterize its queries. A solution like Query Hash Statistics that builds on these features can do low-overhead query cost monitoring with a degree of accuracy that was impossible in prior releases of SQL Server.

 

Because the solution relies on new SQL engine features that were added in SQL Server 2008, it only works with SQL Server 2008 and later.

 

Source code is included, to demonstrate how to build similar solutions. In addition to highlighting the engine's query and query plan fingerprint features, the project shows how to use extensibility features in Data Collector and SSMS. By following the patterns demonstrated in this project, you can learn how to:  

  • Identify cumulative query cost without a profiler trace using the SQL Server "plan fingerprint" and "query fingerprint" features.
  • Use Data Collector to upload data from SQL DMVs to a central management database.
  • Build reports that can be hosted within SQL Server Management Studio (SSMS) to analyze the data in your central management database.

             

 

Check it out, and let us know about your experiences in the Discussions tab on the MSDN Code Gallery project. If you have a suggestion or think you've found a bug, please open a new issue under the Issue Tracker tab. Thanks!

 

 

Additional query keywords: QueryHashStats Query Hash Stats query performance query troubleshooting cumulative query cost analysis Microsoft SQL Server MSSQL MS SQL T-SQL TSQL Transact-SQL query plan cost cumulative plan cost find expensive queries find slow queries DMV dm_exec_query_stats plan fingerprints query fingerprints plan hash query hash query_plan_hash query_hash dm_exec_requests top queries most expensive queries

rpt_query_hash_thumbnails.png

Comments

  • Anonymous
    November 24, 2010
    Very cool!