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

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"?

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.

StackExchange Database Administrators Q#136778, answer score: 3