How useful are query_hash and query_plan_hash for troubleshooting?

After my last post, I was contacted by email about the usefulness of query_hash in actual troubleshooting scenarios since the query_hash value is based on the statement and not the database in a system.  It just happened that the example template from SQL Server 2012 that I showed had the query_hash included in it’s definition.  The premise for the question was that Software as a Service (Saas) solutions have multiple databases, sometimes hundreds to even thousands on the same server, that have the exact same code base, so using query_hash to aggregate data is not as useful in these scenarios since the data is not guaranteed to be evenly distributed.  My first feedback on this was that my blog post didn’t actually relate any information to the query_hash action in Extended Events directly, but there happens to be more to this situation.

The query_hash and query_plan_hash provide the query finger prints in SQL Server 2008+, and simplify the effort required for analyzing adhoc/prepared workloads as well as workloads that use stored procedures in multiple databases.  To look at how to leverage this information in SaaS scenarios to identify the database that caused the most executions of the query_hash being reviewed we’ll make use of the following workload to show the accumulated effects of a specific query_hash per database.

CREATE DATABASE Test;
GO
USE Test;
GO
CREATE PROCEDURE dbo.TestProc
AS
BEGIN
    SELECT TOP 10 *
    FROM master..spt_values
    WHERE type = N’P’;
END
GO
CREATE DATABASE Test1
GO
USE Test1
GO
CREATE PROCEDURE dbo.TestProc
AS
BEGIN
    SELECT TOP 10 *
    FROM master..spt_values
    WHERE type = N’P’;
END
GO
CREATE DATABASE Test2
GO
USE Test2
GO
CREATE PROCEDURE dbo.TestProc
AS
BEGIN
    SELECT TOP 10 *
    FROM master..spt_values
    WHERE type = N’P’;
END
GO
DBCC FREEPROCCACHE;
GO

After creating the databases and objects, we’ll setup an Extended Events session to capture the statement level completed events for the session_id running the test queries and capture the query_hash to show how to use it to look at execution information per database from the query statistics in SQL Server. Since I am using SQL Server 2012, I am not using a target with the event session, but will instead capture the information using the Live Data Viewer to make it easier to show in this post.

CREATE EVENT SESSION [SQLskills_Query_Hash] ON SERVER
ADD EVENT sqlserver.sp_statement_completed(
    ACTION(sqlserver.query_hash)
    WHERE ([sqlserver].[session_id]=(66))),
ADD EVENT sqlserver.sql_statement_completed(
    ACTION(sqlserver.query_hash)
    WHERE ([sqlserver].[session_id]=(66)));

Once the session is created and started, we can run the following tests and review the output:

USE Test
GO

EXECUTE dbo.TestProc
GO 2

SELECT TOP 10 *
FROM master..spt_values
WHERE type = N’P’;
GO 5

USE Test1
GO

EXECUTE dbo.TestProc
GO 4

SELECT TOP 10 *
FROM master..spt_values
WHERE type = N’P’;
GO 7

USE Test2
GO

EXECUTE dbo.TestProc
GO 8

SELECT TOP 10 *
FROM master..spt_values
WHERE type = N’P’;
GO 3

After collecting the data, I disconnected the UI from the live stream and then grouped the events by the query_hash and summed the values for logical_reads, duration, cpu_time, and writes

image

Here we can see that all of the statements that executed had the same query_hash value, even though they were a mix of adhoc executions and procedures from different databases.  If we want to break this down to determine each databases executions we can do that by taking the query_hash value, converting it into a binary(8) data type from the bigint value produced by Extended Events, and then query sys.dm_exec_query_stats.

SELECT
    sql_handle,
    plan_handle,
    execution_count,
    total_logical_reads,
    total_elapsed_time,
    dbid,
    objectid
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
WHERE query_hash = CONVERT(BINARY(8), CONVERT(BIGINT, 1640387627010439277));

image

Using this method we can further breakdown the information to see where our biggest impacts are.  Using the query_hash at the server level in Extended Events makes it possible to identify statements in a workload that might not have long executions but execute frequently leading to a "death by a thousand cuts".  If you need to dig into the database level information, you could add the sqlserver.database_id action to the statement level events, or just query the information out of the query statistics maintained inside of SQL Server.

One thought on “How useful are query_hash and query_plan_hash for troubleshooting?

  1. I do consider all of the concepts you have offered on your post. They’re really convincing and can certainly work. Still, the posts are too brief for starters. Could you please extend them a little from subsequent time? Thank you for the post.

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.