What sort of exec requests don't have a sql handle? (sys.dm_exec_requests)

Question

I have crafted a query against SQL Server 2012 that looks like the following:

SELECT r.session_id, r.sql_handle, t.text -- more details excluded for brevity
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t

Now, this helpfully shows me what is running on my server -- and using a query like this I've been able to find things that consuming more resources than they should.

However, it bothers me that (because of the cross apply, I believe) the entries in sys.dm_exec_requests that don't have a sql_handle value, aren't included. I also worry that at times, the number of requests returned (typically about a dozen) is below what I would expect based on load.

I understand (or think I do) that this is the most finite snapshot in time view I could look at, and SQL can only do so many things at once, but I also wonder, what is my query missing to present me a picture of "what is using my server right now"?

Answer 1

The issue with your current query is that you are only looking for queries that could be running at that very moment. To get a good full picture of your server state, you could try to run SQL Server Profiler or check for connections with a built-in stored procedure likesp_who. These approaches are more complicated and time-consuming than using a stored procedure that's already been developed and tested by other DBAs.

I suggest that you install Adam Machanic's free stored procedure, called sp_whoisactive. The most recent version, should be sufficient to help you dig into activity on your server. This procedure will also give you more detailed information than writing a query referencing one or two DMVs. From the documentation, sp_whoisactive uses 15 DMVs.

Answer 2

What sort of exec requests don't have a sql handle? (sys.dm_exec_requests)

Sql_handle is a hash value which identifies SQL text of the batch/query being submitted to the server. Text can be NULL for encrypted objects so this can cause sql_handle to be NULL