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. ```ruby 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.
TopAnswers
Databases
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`