none
SQL Handle values from dm_exec_connections versus dm_exec_query_stats ???

    Question

  • All,

    I'm seeing what appears to be a difference in sql_handle values between 2 different DMVs, and this difference doesn't line up with what I (thought I) understood about the Plan Cache in the DB Engine.

    SELECT @@Version = "Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (X64)  Apr 22 2011 19:23:43  Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)"

    In a nutshell: the sql_handle value in sys.dm_exec_connections can be used with sys.dm_exec_sql_text(<handle value>), but isn't searchable in sys.dm_exec_query_stats.

    A similar thread appears here, but the original author's confusion about when sys.dm_exec_requests will and won't have rows seems to have distracted the responders from (one of) the underlying issues that the author is facing: http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/a95342f6-ecf2-41f4-9fcd-609a8de0f183 

    Here's what I mean:

    1) run DBCC FREEPROCCACHE to clear the cache

    2) Run this query (assuming you have the right sample DB...any query will do, really): SELECT @@SPID, COUNT(*) FROM AdventureWorksDW2008R2.dbo.FactInternetSalesReason

    3) I get back SPID 93, and of course the rowcount (64515)

    4) Now, grab the most recent SQL handle: SELECT most_recent_sql_handle from sys.dm_exec_connections where session_id = 93

    5) this gives me the following hash: 0x01000900E398AF3610D87383000000000000000000000000

    6) If I plug that into sys.dm_exec_sql_handle, I get the expected text: SELECT * FROM sys.dm_exec_sql_text(0x01000900E398AF3610D87383000000000000000000000000)

    dbid objectid number encrypted text
    NULL NULL NULL 0 SELECT @@SPID, COUNT(*) FROM AdventureWorksDW2008R2.dbo.FactInternetSalesReason

    7) However, if I search sys.dm_exec_query_stats for that sql_handle, I get nothing: SELECT * FROM sys.dm_exec_query_stats WHERE sql_handle = 0x01000900E398AF3610D87383000000000000000000000000

    8) If I search the Query Stats DMV based on a text search, I can get the SQL_Handle that I SHOULD be using, apparently: SELECT txt.*, qs.sql_handle FROM sys.dm_exec_query_stats qs outer apply sys.dm_exec_sql_text(qs.sql_handle) txt WHERE txt.text like '%FactInternetSalesReas%'

    then I get these results:

    dbid objectid number encrypted text sql_handle
    NULL NULL NULL 0 SELECT @@SPID, COUNT(*) FROM AdventureWorksDW2008R2.dbo.FactInternetSalesReason
     0x02000000E398AF366996AD11479600C6F64043FD5961D0A6

    as you can see, the query hash has some similarities, but nothing close enough for me to guess at a repeatable way to get the match. What I'm seeing indicates to me that my query is in the plan cache, since I can get it via the sys.dm_exec_sql_text DMF, but...what is going on?

    In case you're wondering, yes there is a real-world use for this.  I can share the details if you're curious, but otherwise I don't want to clutter an already long post. (Basically, I want to grab the Query Plan for the last-run batch on a specific connection...sorta like DBCC INPUTBUFFER but for grabbing the query plan. So I'm trying to use sys.dm_exec_query_stats since it has a mapping to plan_handle.)

    Thanks,

    Aaron M.

    Thursday, April 19, 2012 7:35 PM

Answers

  • Hi Aaron,
    I can reproduce this issue according to your description. Meanwhile, I find a feedback on Microsoft Connect about this issue:
    The values from most_recent_sql_handle in sys.dm_exec_connections are indeed not matching the handles in for example sys.dm_exec_query_stats in certain situations. We have multiple types of sql_handles, and in these cases sys.dm_exec_connections was displaying a different kind of handle, starting with 0x01 instead of 0x02, as sys.dm_exec_query_stats and the other views display. Hence, the handles did not match between them.

    >>Basically, I want to grab the Query Plan for the last-run batch on a specific connection
    If you want to dynamically get the Query Plan for the latest batch for a specified the connection, you may have a try to join the results from sys.dm_exec_connections and sys.dm_exec_query_stats based on the query text content. However, if there is more than one queries with the same query text from sys.dm_exec_query_stats, the result can be wide.

    select * from (
    select qs.*, txt.text as query_text, txt.text as plan_text FROM sys.dm_exec_query_stats qs 
    -- get the query text
    outer apply sys.dm_exec_sql_text(qs.sql_handle) txt 
    -- get the plan text
    cross apply sys.dm_exec_sql_text(qs.plan_handle ) txt2 
    ) a inner join 
    (SELECT * FROM sys.dm_exec_connections qs outer apply sys.dm_exec_sql_text(qs.most_recent_sql_handle) txt 
    WHERE session_id =93) b 
    -- join the results if query texts are the same
    on a.query_text =b.text 

    TechNet Subscriber Support
    If you are TechNet Subscription user and have any feedback on our support quality, please send your feedback here.


    Stephanie Lv

    TechNet Community Support

    • Marked as answer by Ammo005 Friday, April 20, 2012 4:31 AM
    Friday, April 20, 2012 2:45 AM

All replies

  • Hi Aaron,
    I can reproduce this issue according to your description. Meanwhile, I find a feedback on Microsoft Connect about this issue:
    The values from most_recent_sql_handle in sys.dm_exec_connections are indeed not matching the handles in for example sys.dm_exec_query_stats in certain situations. We have multiple types of sql_handles, and in these cases sys.dm_exec_connections was displaying a different kind of handle, starting with 0x01 instead of 0x02, as sys.dm_exec_query_stats and the other views display. Hence, the handles did not match between them.

    >>Basically, I want to grab the Query Plan for the last-run batch on a specific connection
    If you want to dynamically get the Query Plan for the latest batch for a specified the connection, you may have a try to join the results from sys.dm_exec_connections and sys.dm_exec_query_stats based on the query text content. However, if there is more than one queries with the same query text from sys.dm_exec_query_stats, the result can be wide.

    select * from (
    select qs.*, txt.text as query_text, txt.text as plan_text FROM sys.dm_exec_query_stats qs 
    -- get the query text
    outer apply sys.dm_exec_sql_text(qs.sql_handle) txt 
    -- get the plan text
    cross apply sys.dm_exec_sql_text(qs.plan_handle ) txt2 
    ) a inner join 
    (SELECT * FROM sys.dm_exec_connections qs outer apply sys.dm_exec_sql_text(qs.most_recent_sql_handle) txt 
    WHERE session_id =93) b 
    -- join the results if query texts are the same
    on a.query_text =b.text 

    TechNet Subscriber Support
    If you are TechNet Subscription user and have any feedback on our support quality, please send your feedback here.


    Stephanie Lv

    TechNet Community Support

    • Marked as answer by Ammo005 Friday, April 20, 2012 4:31 AM
    Friday, April 20, 2012 2:45 AM
  • Thanks Stephanie, I appreciate your time, and the insight into different SQL_Handles.

    Most of the code that I'm interested in will be coming in via Stored Procedures (as opposed to ad-hoc), so I can use database_id/object_id from sys.dm_exec_sql_text and join that with the database_id/object_id values from either that DMF on sys.dm_exec_query_stats or sys.dm_exec_cached_plans.

    I had been hoping to avoid this approach (as it means instead of joining individual records, my code will scan the plan cache looking for a DBID/OBJID match), but it is an acceptable workaround for me.

    Thanks again,

    Aaron Morelli

    Friday, April 20, 2012 4:31 AM