Query returns null for sql text

amomen 381 Reputation points
2021-03-27T02:12:05.227+00:00

Dear everybody,

I am trying to get information for the columns of the following query: 

select (select text from sys.dm_exec_sql_text(q.sql_handle)) as [Script Text],
DB_NAME(dbid) as [Database],
program_name,
nt_username,
loginame,
last_execution_time
from sys.dm_exec_query_stats q
full join sys.sysprocesses p on q.sql_handle = p.sql_handle
where spid <> @@spid
order by last_execution_time desc

But the columns 'Script Text' and 'last_execution_time' have all NULL values. Could you kindly say why this happens and how I can get it to give the desired values? I appreciate your help.

Regards,
Ali

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,547 questions

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Viorel 111.7K Reputation points
    2021-03-27T08:47:40.307+00:00

    Check if the next query gives the desired results: 

    select  
    (select text from sys.dm_exec_sql_text(sql_handle)) as [Script Text],  
    DB_NAME(dbid) as [Database],  
    program_name,  
    nt_username,  
    loginame,  
    last_batch  
from sys.sysprocesses  
where spid <> @@spid  
and sql_handle <> 0x00 -- remove if not needed

    What system information do you need?

  2. Erland Sommarskog 100.8K Reputation points MVP
    2021-03-27T10:17:35.717+00:00

    Your query has all sorts of problems, and I am not sure that it is even meaningful.

    You are making a full join, which as such means that you will get all rows in sys.dm_exec_query_stats and sys.sysprocesses and on the rows where there is no match on the join conditions, you will get NULL for the columns for the other view.

    But then you have the filter

    where spid <> @@spid

    Which means that you filter out the rows where the sql_handle does not exist in sys.sysprocesses. (Because when p.spid is null, the condition evaluates to UNKNOWN, not to TRUE.)

    At the same time, you are only passing q.sql_handle to sys.dm_exec_sql_text, so if this is NULL, you get nothing back obviously.

    So if we only look at this from the point of view of how to write a full join properly, the query should be: 

    select (select text from sys.dm_exec_sql_text(isnull(q.sql_handle, p.sql_handle))) as [Script Text],
        DB_NAME(dbid) as [Database],
        program_name,
        nt_username,
        loginame,
        last_execution_time
from sys.dm_exec_query_stats q
full join (SELECT * FROM sys.sysprocesses WHERE spid <> @@spid) p on q.sql_handle = p.sql_handle
order by last_execution_time desc

    But when I ran this query, I got this error: 

    Msg 569, Level 16, State 4, Line 54
The handle that was passed to dm_exec_sql_text was invalid.

    This is because sys.sysprocesses has the handle of the query more recently executed, but the plan for that query may no longer be in cache. And then there a lot of system processes of which the handle is 0x0.

    Also, sys.sysprocesses is an old compatibility view which you should not use, but rather you should use sys.dm_exec_sessions, sys.dm_exec_connections or sys.dm_exec_requests, a little depending on what you want find.

    At the same time sys.dm_exec_query_stats holds all plans currently in the cache, no matter if there is a process executing the query right now.

    But it all boils down to what you really want to achieve.

    0 comments
  3. MelissaMa-MSFT 24,176 Reputation points
    2021-03-29T07:06:42.237+00:00

    Hi @amomen-8749，

    Welcome to Microsoft Q&A!

    from sys.dm_exec_query_stats q
    full join sys.sysprocesses p on q.sql_handle = p.sql_handle

    The sql_handle from sys.sysprocesses is binary(20) and represents the currently executing batch or object.

    The sql_handle from sys.dm_exec_query_stats is varbinary(64) and is a token that uniquely identifies the batch or stored procedure that the query is part of.

    The sql_handle, together with statement_start_offset and statement_end_offset, can be used to retrieve the SQL text of the query by calling the sys.dm_exec_sql_text dynamic management function

    So it is not working if you would like to join these two tables with sql_handle.

    Please refer below and check whether it is working: 

    select q.text [Script Text],   
DB_NAME(t.dbid) as [Database],  
program_name,  
nt_username,  
loginame,  
last_batch last_execution_time  
from sys.sysprocesses t  
cross apply sys.dm_exec_sql_text (t.sql_handle) q  
where spid <> @@spid  
order by last_batch desc

    Best regards
    Melissa

    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments