Does Microsoft release its SQL Query Hash Algorithm..?
Does Microsoft release its SQL Query Hash Algorithm..?
No, Microsoft does not release the hashing algorithm. Additionally, hashing happens at a different layer than original query text - so even if you had the algorithm, you'd still need to normalize like SQL Server does, first.
This way I could do quick matches of the hash to be able see where in the codebase a particular query came from.
This assumes there also isn't anything like dynamic sql, SMO, etc., where there isn't an actual plain text query.
It sounds like all you want to do is get the Statement Text that corresponds to the query in dm_exec_requests/dm_exec_query_stats?
You can APPLY
the sys.dm_exec_sql_text()
function using the sql_handle
in the following way:
SELECT
deqs.plan_handle AS PlanHandle
,deqs.query_hash AS QueryHash
,Q.statementtext AS StatementText
,deqs.query_plan_hash AS QueryPlanHash
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
CROSS APPLY (VALUES (SUBSTRING(dest.text, (deqs.statement_start_offset/2)+1,
((CASE deqs.statement_end_offset WHEN -1 THEN DATALENGTH(dest.text)
ELSE deqs.statement_end_offset
END - deqs.statement_start_offset)/2)+1))) AS Q(statementtext)