question

amomen avatar image
0 Votes"
amomen asked ErlandSommarskog commented

Query returns null for sql text

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

sql-server-transact-sql
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @amomen-8749,

Could you please validate all the answers so far and provide any update?

Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread. 

Thank you for understanding!

Best regards
Melissa

0 Votes 0 ·

Sure Melissa :), I am analyzing. momentarily.

Regards,
Ali (@amomen-8749)

0 Votes 0 ·
Viorel-1 avatar image
0 Votes"
Viorel-1 answered ErlandSommarskog commented

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?

· 5
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you Viorel. Aside from this information, I also need last_execution_time of query text from sys.dm_exec_query_stats and I need to sort the output by it. Also I need to know spid, program_name, nt_username, loginame etc. from sys.sysprocesses. That's where the idea of join comes from, and the sole affinity between sys.sysprocesses and sys.dm_exec_query_stats is sql_handle. I searched through Microsoft's documentation and found out that sql_handle in sys.sysprocesses is of type binary(20) in contrast with sys.dm_exec_query_stats which is varbinary(64) (I am unaware of the reason to this discrepancy, the sql_handle argument in sys.dm_exec_sql_text is of type varbinary(64)). Moreover, there are some other views that share sql_handle and have some crucial information that I need to combine via join as noted in Microsoft docs on the link below in the future:

0 Votes 0 ·

Thank you Viorel. Aside from this information, I also need last_execution_time of query text from sys.dm_exec_query_stats and I need to sort the output by it. Also I need to know spid, program_name, nt_username, loginame etc. from sys.sysprocesses.

But what are you trying to achieve? If you want to know which processes that executed a certain plan, this is nothing you can find out. That process may have exited. Or it may have moved on to a new query.

And as I have already noted, sys.sysprocesses is old and dusty. sys,dm_exec_sessions/requests/connections is where you should look for session information.

0 Votes 0 ·

Thank you so much Erland!! I definitely take your precious advice. Actually, if I am to divulge my complete purpose, we have a network monitoring software built upon SQL Server. I have to make very specialized reports (like listing the devices that were unresponsive for at least a week) that the software doesn't make itself but the database has every information. For this purpose I have to know what data is saved where, so I need a little bit of reverse engineering because I am not the author of the software. In order to do this I open a webpage from software, containing some desired information, an operation during which the software runs some query on SQL Server to read information. At the same time if I fetch the sql text, it would be very helpful. I also need to apply some filters like program_name, loginame, database etc. that I mentioned to narrow down the query results, as the server is busy with many processes.

0 Votes 0 ·
Show more comments
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.