4

Query_hash definition from sys.dm_exec_query_stats is:

query_hash: Binary hash value calculated on the query and used to identify queries with similar logic. You can use the query hash to determine the aggregate resource usage for queries that differ only by literal values.

But when I search for one particular query_hash (as shown below), I am getting multiple query texts that differ heavily in logic and size_in_bytes (for plan).

Note: I am using SQL Server 2016

DECLARE @QueryHashTest BINARY(8)
SET @QueryHashTest = CONVERT(BINARY(8), 'Ð…U¹üŒv¿')

SELECT  
        QCP.objtype
        ,qStat.query_hash,
        CONVERT(VARCHAR(100),  qStat.query_Hash) AS VARCHAR_query_hash
        ,sText.text AS QueryText
        ,QCP.size_in_bytes
        ,qStat.creation_time
        ,qp.query_plan  
FROM ( 
        SELECT query_hash,
               COUNT(query_hash) AS PlanCount
        FROM sys.dm_exec_query_stats
        GROUP BY query_hash
    ) AS MultipleQ
INNER JOIN sys.dm_exec_query_stats qStat ON MultipleQ.query_hash = qStat.query_hash
INNER JOIN sys.dm_exec_cached_plans  QCP
    ON QCP.plan_handle = qStat.plan_handle
CROSS APPLY sys.dm_exec_sql_text(qStat.sql_handle) AS sText
CROSS APPLY sys.dm_exec_query_plan(qStat.plan_handle) AS qp
WHERE PlanCount > 1
AND QCP.objtype = 'Proc'
AND qStat.query_hash= @QueryHashTest 
AND (size_in_bytes >= 2179072 OR size_in_bytes <= 262144)
ORDER BY size_in_bytes DESC

Result enter image description here

Why are the unrelated queries showing same query_hash? Is it a bug or is there something unexpected going on in the database?

0
6

While hash collisions are possible, I suspect what's happening is you're pulling the text for the whole batch, not the particular statement. A procedure with multiple statements can get a cache entry for each statement. And if some statements are identical across procedures, they'll have the same query hash. Let me demonstrate:

USE TestDB
GO

--DROP TABLE dbo.employees
CREATE TABLE dbo.employees (
ID INT IDENTITY PRIMARY KEY,
title VARCHAR(20),
salary TINYINT,
has_been_fired_yet BIT)

INSERT dbo.employees
VALUES ('Senior DBA',30,0),('Grumpy DBA',50,0)
GO

CREATE OR ALTER PROC dbo.Payroll
AS

SELECT 'Your total payroll is '+CONVERT(VARCHAR(10),SUM(salary))+' query bucks'
FROM dbo.employees
WHERE has_been_fired_yet = 0
GO

CREATE OR ALTER PROC dbo.RightSizing
AS

UPDATE dbo.employees
SET has_been_fired_yet = 1
WHERE salary > 40

SELECT 'Your total payroll is '+CONVERT(VARCHAR(10),SUM(salary))+' query bucks'
FROM dbo.employees
WHERE has_been_fired_yet = 0
GO

--EXEC to get them in the cache
EXEC dbo.Payroll
EXEC dbo.RightSizing

Use the below query to see the how the same statement that is in each proc shows up separately.

SELECT
st.text AS batch_text,
SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,  
    ((CASE statement_end_offset   
        WHEN -1 THEN DATALENGTH(st.text)  
        ELSE qs.statement_end_offset 
    END - qs.statement_start_offset)/2) + 1
) AS statement_text,
qs.execution_count,
qs.query_hash
FROM sys.dm_exec_query_stats AS qs  
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as st 
WHERE st.objectid IN (OBJECT_ID('dbo.Payroll'),OBJECT_ID('dbo.RightSizing'))

query results If you use the query in your question, it fails to pick up the identical statement, since it looks at only the batch text.

-1

Hashing means "mapping" something potentially large to something limited in space (the query_hash). The query hash is a binary(8). So in 8 bytes, you want to identify something that can, essentially, have an endless possibility of values (the query). Bottom line is that you can get collisions - different queries resulting in the same hash. This is expected.

3
  • 3
    I wouldn't go so far as to say it's "expected," especially since the OP mentioned the query text and logic is very different - though you're right that it's possible because of hash collisions. Jan 23 '19 at 14:44
  • 3
    If the hash function is good you shouldn't expect a hash collision unless you have billions of inputs for 8 bytes. crypto.stackexchange.com/a/41063/6652 Jan 23 '19 at 15:24
  • What I meant is that you must expect that you can get hash collisions (English isn't my native language). It is true, of course, that it isn't very common. But don't be surprised when it happens. :-) Jan 24 '19 at 7:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.