CODEWITHSUNDEEP
sql-serversql-server-2012
4Register Tony
4Register Tony

Reputation: 41

Why is the sql_handle for the same session not consistent between sysprocesses and dm_exec_requests?

When I was trying to figure out which SQL query made the blocks on the SQL Server 2012, the SQL handler value for the same SPID inquired by different command returns different values:

Query #1:

select * 
from sys.sysprocesses

with SPID = 281, returned the value of 

"0x010006001838FB01D091A31B1602000000000000"

Query #2:

select * 
from sys.dm_exec_requests

with SPID = 281, returned the value of 

"0x060006001838FB0100DFA2B21402000001000000000000000000000000000000000000000000000000000000"

Furthermore, the more important to me is that the return of SQL context inquiry command Sql ( sys.dm_exec_sql_text{sql_handle_value} ):

Query #1:

"0x010006001838FB01D091A31B1602000000000000"

context return is empty;

Query #2:

"0x060006001838FB0100DFA2B21402000001000000000000000000000000000000000000000000000000000000"

context return is valid query record.

Can anyone help me?

Upvotes: 1

Views: 652

Answers (1)

Martin Smith
Martin Smith

Reputation: 453563

They are not the same thing

sys.dm_exec_requests

sql_handle varbinary(64) Is a token that uniquely identifies the batch or stored procedure that the query is part of. Is nullable.

sys.sysprocesses (deprecated)

sql_handle binary(20) Represents the currently executing batch or object.

Note This value is derived from the batch or memory address of the object. This value is not calculated by using the SQL Server hash-based algorithm.

They have different datatypes and are calculated differently. The (deprecated) function sys.fn_get_sql accepts both formats though 

SELECT spid, sql_handle, text 
FROM sysprocesses
cross apply  sys.fn_get_sql(sql_handle)

select * 
from sys.dm_exec_requests
cross apply  sys.fn_get_sql(sql_handle)

but you should be using the newer DMOs (sys.dm_exec_requests and sys.dm_exec_sql_text) not the deprecated ones

Upvotes: 1

Related Questions