Hello Geeks and welcome to the Day 39 of the long series of One DMV a day. In this series of blogs I will be exploring and explaining the most useful DMVs in SQL Server. As we go ahead in this series we will also talk about the usage, linking between DMVs and some scenarios where these DMVs will be helpful while you are using SQL Server. For the complete list in the series please click here.
Today I am going to talk about a very less known and used DMV. sys.dm_exec_background_job_queue is a DMV which gives information about background tasks. These are query processor jobs which run asynchronously. Currently this DMV returns only information for asynchronous update statistics.
Sys.dm_exec_background_job_queue is very useful in troubleshooting few scenarios. When there is a problem with your update statistics taking longer. Or when your auto update statistics takes lock on a single user database. The interested columns are as described below.
time_queued – time when the job is queued.
database_id – database on which this job will execute.
object_id1 to 4 – Currently used 1 and 2 for object id and statistics id. 3 and 4 are for internal use.
Error_code – Last error code only if reinserted due to failure.
Retry_count – Number of times the job is reinserted due to any failures or lack of resources.
In_progress – started = 1, queued and waiting = 0.
Session_id – to relate to session_id in sys.dm_exec_sessions.
SELECT time_queued, job_id, database_id, object_id1, object_id2, object_id3, object_id4, error_code, request_type, retry_count, in_progress, session_id FROM sys.dm_exec_background_job_queue
sys.dm_exec_background_job_queue helps you in seeing the running and queued jobs as above. From the above output you can observe that the auto update statistics on object id 1064090239 is running. All other are waiting in queue.
So now you know or re-learnt there is sys.dm_exec_background_job_queue to check currently running or queued auto update statistics. Tomorrow I will be covering one more execution related DMV. Stay tuned. Till then
Happy Learning,
Manu
Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook |
Follow me on Twitter | Follow me on FaceBook
