dm_exec_requests.text 8K max size question RRS feed

  • Question

  • have been trying to find a way to get the sql text, for queries larger than 8k...currently I use...'cross apply

    sys.dm_exec_sql_text(r.sql_handle)'...the normal is returning text strings just fine. But not all active spid are being returned.  If I change the 'cross apply' to 'outer apply' on the dm_exec_sql_text(r.sql_handle) bit above...I get more rows from the query (more like sp_who2)...but the text port of results is empty!?  My understanding is this due to internal caching constraint of 8K...

    is there some other way to view a query > 8K?  Have been reading bunches of blogs...but nothing addresses this need...

    thanks in advance.

    mike t.

    Thursday, April 2, 2015 8:46 PM


  • sp_who2 will show all the threads (running,sleeping,suspended) that may not doing anything on sql server side.

    a much better procedure is sp_whoisactive  by adam mechanic..just google that, if you want..

    session_id <50 are internally used,so you will not get any plans for those is there any difference between these two..

    SELECT  st.*
    FROM sys.dm_exec_requests AS qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
    SELECT  st.*
    FROM sys.dm_exec_requests AS qs
    outer apply sys.dm_exec_sql_text(qs.sql_handle) AS st
    where session_ID>50

    Hope it Helps!!

    Thursday, April 2, 2015 9:07 PM