Why is the query hash so useful?

An example of how I use the query hash

I wrote a blog on what the query hash is, right here. That post describes the query hash, but it doesn’t help you if you’re looking at a slow query.

This post will try to answer that, in a few ways that you can use the query hash. Here’s our hypothetical scenario, which isn’t that far from reality.

Scene start: You log in for the day, and you have several alerts about your production server’s resource usage. It looks like something changed, but you don’t know what. Luckily, one of your fellow DBA teammates is already online and has identified the problematic query.

What users and servers are sending us this query?

The first question that we want to find out is what users are sending this query to our server, and what server they’re sending it from. Naturally, you ask your fellow DBA for the query text that they have. Instead, they tell you that they just have the execution plan.

Just ask for the query hash.

The execution plan has the query hash in the XML or in the graphical execution plan. Say that the other DBA sent you this hash, 0x90F7BA12EAE9502E. Let’s take a look at the queries we can use.

The first place: the plan cache

The plan cache is really useful in SQL Server, let’s use the query hash there. I think there’s a lot of excellent plan cache queries on the internet, so I’ll just write a really simple one for this demo.

SELECT    s.creation_time,
	  s.last_execution_time,
	  s.execution_count,
	  sql_text.text,
	  sql_plan.query_plan
FROM sys.dm_exec_query_stats as s 
CROSS APPLY sys.dm_exec_sql_text(s.sql_handle) as sql_text
CROSS APPLY sys.dm_exec_query_plan(s.plan_handle) as sql_plan
WHERE query_hash = 0x90F7BA12EAE9502E

That’s one way to look at it, and it gave us a lot of information on when the query ran! But I want to show you one more place where the query hash is useful: in currently running queries.

Finding currently running queries by query hash

Let’s find all the users and host servers that are currently running this query, and find the text that they input, using dm_exec_input_buffer.

SELECT s.host_name,
       s.login_name,
       input_buffer.event_info
FROM sys.dm_exec_sessions AS s
JOIN sys.dm_exec_requests AS r
    ON r.session_id = s.session_id
CROSS APPLY sys.dm_exec_input_buffer
    (s.session_id, r.request_id) as input_buffer
WHERE s.is_user_process = 1
    AND r.query_hash = 0x90F7BA12EAE9502E

So what do we do from here? Well, we have the query plan, we have the input text and we know the user who ran the query. That’s enough information to start investigating if there’s an issue in any of those places.

Thanks for reading, stay tuned!