sql-server add tag
hooinator (imported from SE)
The following site mentions the `sql_handle` is a MD5 hash of the entire batch text


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



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.
Top Answer
Paul White (imported from SE)
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*
   - For adhoc and prepared SQL this is an [internal hash of the batch text](https://www.sql.kiwi/2020/10/sqlhandle-and-sql-server-batch-text-hash.html) as returned by `@@PROCID` or `objectid` in `sys.dm_exec_plan_attributes`.
3. **16 bytes:** MD5 hash of the batch text including (parameter definitions) prefix, if present.
4. **20 bytes** `0000000000000000000000000000000000000000`

This room is for discussion about this question.

Once logged in you can direct comments to any contributor here.

Enter question or answer id or url (and optionally further answer ids/urls from the same question) from

Separate each id/url with a space. No need to list your own answers; they will be imported automatically.