6

We use SQL Spotlight in our environment. it's pretty handy. we use particularly the output of sys.dm_exec_requests and sys.dm_exec_query_stats.

Spotlight pulls the query plan from the plan cache for you using the hash which is nice.

Problem is, unless you're experienced with the code base, it's quite difficult to know where that query came from.

I had an idea that if I could:

  • Parse the codebase
  • Pull the SQL queries
  • Hash them the same way microsoft does

This way I could do quick matches of the hash to be able see where in the codebase a particular query came from.

Alternatively I'd have to do some very slow regex...

7

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.

5

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)    
1

Late to the party, but I do have a workaround. Sean is correct that Microsoft does not publish the hashing algorithm and that some normalization happens first (for example, if you insert a newline into your query text the query hash is unchanged).

If you have access to a SQL Server instance, you can find the query hash. The quickest way to do it without running the query would be via the estimated plan:

SET SHOWPLAN_XML ON
GO
SELECT * FROM MyTable

This will return you the showplan xml, which contains the query hash and query plan hash. In this example it's in the <StmtSimple> tag.

<?xml version="1.0" encoding="utf-16"?>
<ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"  xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
  <BatchSequence>
<Batch>
  <Statements>
    <StmtSimple StatementCompId="1" StatementEstRows="61440" StatementId="1" StatementOptmLevel="TRIVIAL" CardinalityEstimationModelVersion="150" StatementSubTreeCost="0.484199" StatementText="select * from SourceTable" StatementType="SELECT" QueryHash="0x6BDE57303A0617E2" QueryPlanHash="0x3A7F77E18FC7FA01" RetrievedFromCache="false" SecurityPolicyApplied="false">

QueryHash="0x6BDE57303A0617E2"
QueryPlanHash="0x3A7F77E18FC7FA01"

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.