SQL Server Query Hash Statistics

Advanced troubleshooting can be very difficult in SQL Server, particularly when you’re trying to dig into performance issues coming from queries. Typically, the only reliable method is to capture traces of SQL Server activity and then perform laborious and slow analysis of the trace files to find the “smoking gun” query that’s slowing things down. But late in the SQL Server 2008 release cycle, the developers added a little-known feature called query fingerprints (also known as a query hash); this feature calculates query plan cost without the need for a SQL Profiler or server-side trace. These statistics are automatically calculated in the sys.dm_exec_query_stats Dynamic Management View (DMV).

Now that this information is built in to SQL Server 2008 and later versions, it requires only a small step to build a tool to collect and report on the query hash details. Enter the Query Hash Statistics tool (which I’ll call HashStats, for short).

HashStats collects these little-known query performance metrics and loads them into a central database. HashStats also includes a set of reports to analyze and visualize performance information directly within SQL Server Management Studio (SSMS). Unlike many tools, HashStats exacts a low overhead because it doesn’t query all of the databases about the queries running on a SQL Server instance, only the query fingerprint. And, because it pulls data directly from the DMVs, it can track the cumulative cost of all executions of a transaction, even when that transaction is unparameterized.

Features

HashStats has several significant and useful features:

System Requirements

HashStats requires both a host server to house the query fingerprint data and target servers to monitor. Both host and target servers must run SQL Server 2008 or later. After you install HashStats and you install the reports within SSMS, wait about 10 minutes for HashStats to collect enough results to show some data in the reports. The reports grow in accuracy and detail as the database accumulates more data about the transactions running on the monitored SQL Server machine(s).

Because parts of HashStats rely on the Management Data Warehouse (MDW) feature set’s SQL Server data collector service, you can use HashStats only on SQL Server 2008 and later. Note that the MDW requires a database on a SQL Server instance to store its data, and you must enable the data collector service on that instance.

For details about how to install the MDW, read the HashStats Prerequisites information, or see the Microsoft article “Managing Data Collection Using SQL Server Management Studio”. Microsoft’s Bart Duncan has an excellent discussion of the infrastructure and DMVs that support the HashStats tool, in his blog post “Query Fingerprints and Plan Fingerprints (The Best SQL 2008 Feature That You’ve Never Heard Of)”. You can download the HashStats tool and read the program details here.