Is it possible to calculate the SQL Server sql_handle for a given SQL statement?

Posted on

Question :

The following site mentions the sql_handle is a MD5 hash of the entire batch text

https://techcommunity.microsoft.com/t5/sql-server/2-0-sql-handle-and-plan-handle-explained/ba-p/383204

However, I’m unable to calculate it manually. From dm_exec_query_stats we determine the following statement has the following sql_handle.

SQL: (@P1 bigint)SELECT NID MimeTypeID, Name FROM DDocumentClass WHERE Type = @P1
SQL_HANDLE: 0x02000000CA17931BEF7F24A1787BF580EA365A56408697B30000000000000000000000000000000000000000

The following Ruby script calculates the MD5.

require 'digest'

sql1 = 'SELECT NID MimeTypeID, Name FROM DDocumentClass WHERE Type = @P1'
sql2 = '(@P1 bigint)SELECT NID MimeTypeID, Name FROM DDocumentClass WHERE Type = @P1'

puts Digest::MD5.hexdigest sql1
puts Digest::MD5.hexdigest sql2

Output:

81707e73438befe82957c766dca86799
2652dc1d05e927ac7c5bfc985a3d1483

There must be more to the algorithm.

We can calculate Oracle’s SQL_ID, I’m hoping to be able to also calculate SQL Server’s SQL_HANDLE.

Answer :

The 44-byte sql_handle has the following components (in order):

  1. 4 bytes: Byte-reversed integer code for the target cache e.g.
    • 0x02000000 (2) for SQL Plans (CACHESTORE_SQLCP)
    • 0x03000000 (3) for Object Plans (CACHESTORE_OBJCP)
  2. 4 bytes: Byte-reversed integer object id
  3. 16 bytes: MD5 hash of the batch text including (parameter definitions) prefix, if present.
  4. 20 bytes 0000000000000000000000000000000000000000

Leave a Reply

Your email address will not be published.