0

I am looking at both the SQL Server expensive queries report and the query below, but both are showing this mysterious NULL query as the slowest query on my server.

Is there any way I can find out more about this NULL query and why it might be so slow?

Is this some internal query? It doesn't seem like this should be showing up in the report if so.

enter image description here

This is the query which is also showing NULL as the slowest query on my server:

select 
    r.session_id,
    r.status,
    r.command,
    r.cpu_time,
    r.total_elapsed_time,
    t.text
from 
    sys.dm_exec_requests as r
cross apply 
    sys.dm_exec_sql_text(r.sql_handle) as t

enter image description here

How can I find out what this query is and why it's so slow?

  • 1
    That's a system session. DBCC INPUTBUFFER(37); might give a clue what it's doing, but since it's a background task I'm not sure what you could possibly do to tune it... if you're just randomly checking dm_exec_requests you should probably limit it to non-system sessions (join to sys.dm_exec_sessions where is_user_process = 1). Why it's not filtered out of the report, no idea, I tend to not use built-in reports because I (and vendors) build much better versions. – Aaron Bertrand Jun 26 at 16:01
  • @AaronBertrand The response is "No Event" when executing DBCC INPUTBUFFER(37);. When I join and filter on is_user_process = 1 the query disappears from the results as you'd implied from its being a system session. So, is it typical to see this NULL query in the expensive queries report? I don't recall seeing it on other servers so I thought it might be an issue of some sort. We are experiencing intermittent slowness on the server so I am trying to identify the root cause. – user1477388 Jun 26 at 16:18
  • 1
    So, is it typical to see this NULL query in the expensive queries report? I don't know. Like I said, I don't use those reports for monitoring. – Aaron Bertrand Jun 26 at 16:20
  • @AaronBertrand Gotcha - well since this is a red herring please feel free to post your answer of "That's a system session" and I will accept. Otherwise, I can close this question as too being too localized. Also, if there are any tools or queries which you may recommend to isolate intermittent poor performance on SQL Server 2016, you are certainly welcome to share! – user1477388 Jun 26 at 16:31
2

That's a system session.

DBCC INPUTBUFFER(37); might give a clue what it's doing, or CROSS APPLY with sys.dm_exec_input_buffer(), but you might not be able to understand any more about what it's doing. Since it's a background task, it is unlikely to be a cause for any other perceived slowness but, being internal, I'm not sure what you could possibly do to tune it anyway...

If you're just randomly checking dm_exec_requests you should probably limit it to non-system sessions (join to sys.dm_exec_sessions where is_user_process = 1). Why it's not filtered out of the report, I have no no idea, I tend to not use built-in reports because I (and vendors) build much better versions.

| improve this answer | |
  • For these system tasks, the command column in sys.dm_exec_requests is sometimes also helpful, especially when input buffer isn't. – AMtwo Jun 27 at 11:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.