Site icon Andy M Mallon – AM²

Decoding sql_handle

Andy

If you’ve ever looked at sys.sysprocesses or sys.dm_exec_requests (or a number of other DMVs), you’ve noticed there is a column called “sql_handle” that contains some binary gobbledygook. Books Online gives the (un)helpful definition as “Hash map of the SQL text of the request.”

sp_WhoIsActive

In the context of currently-running processes, most people decode sql_handle with Adam Machanic’s (blog|twitter) sp_WhoIsActive. It’s really easy & straightforward–if you’re not familiar with Adam’s utility, download it and check it out. My friend Mike Kane (blog|twitter) has an interesting post on parameter combinations he regularly uses when using sp_WhoIsActive.

sys.dm_exec_sql_text

Perhaps you’re on a server that doesn’t have sp_WhoIsActive installed, and you want to see what is running. You can just use the DMF sys.dm_exec_sql_text() to convert sql_handle back into SQL:

SELECT *
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t;

The output of the DMF will give you (for example) the definition of the currently-executing stored procedure.

 

But wait there’s more!

If you use sp_WhoIsActive, you know that it actually determines the exact statement within that stored procedure that is running. If you want to get that statement, you’ll need to use the statement_start_offset and statement_end_offset columns in sys.dm_exec_requests. I wish the formula were a little more straightforward, but you’ll need to do something like this:

SELECT db_name(t.dbid) as DbName,
    object_name(t.objectid,t.dbid) as ObjectName,
    t.text, 
    SUBSTRING(t.text, (r.statement_start_offset/2)+1, (
            (CASE r.statement_end_offset
               WHEN -1 THEN DATALENGTH(t.text)
               ELSE r.statement_end_offset
             END - r.statement_start_offset)
          /2) + 1) AS SqlStatement
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t;

 

Sometimes you have to do the hard work

If you have a sql_handle that doesn’t correspond to currently-executing SQL, then sp_WhoIsActive isn’t much help. In that case, you’re on your own.

I ran into an issue where the following error was logged in the SQL Server logs:

A possible infinite recompile was detected for 
SQLHANDLE 0x03001F00E5FFB07CC6F96E019AA400000100000000000000, 
PlanHandle 0x05001F00E5FFB07C4021C822020000000000000000000000, 
starting offset 20940, 
ending offset 21280.

 

It gives you all the important bits, but since it’s coming from the log file there’s no DMV to select from. You still need to decode that binary SQLHANDLE value, so you’ll have to work for it. It’s just a matter of taking the above SELECT statement and substituting the right values into the right places:

DECLARE @statement_start_offset int = 20940,
        @Statement_end_offset   int = 21280,
        @sql_handle   varbinary(64) = 0x03001F00E5FFB07CC6F96E019AA400000100000000000000;
SELECT db_name(t.dbid) as DbName,
        object_name(t.objectid,t.dbid) as ObjectName,
        t.text, 
        SUBSTRING(t.text, (@statement_start_offset/2)+1, (
                (CASE @Statement_end_offset
                   WHEN -1 THEN DATALENGTH(t.text)
                   ELSE @Statement_end_offset
                 END - @statement_start_offset)
              /2) + 1) AS SqlStatement
FROM sys.dm_exec_sql_text(@sql_handle) t;

 

This should help you get to the bottom of your problem. In my case, this gave me not just the stored procedure, but also the specific statement that triggered the infinite recompile event. Armed with that information, I was able to dig further into the problem and quickly get to the root cause and find a solution.

Exit mobile version