sys.dm_exec_background_job_queue – Day 39 – One DMV a Day

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

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

   

About Manohar Punna

Manohar Punna is a Microsoft Data Platform Consultant and a Data Platform MVP. In his day to day job he works on building database and BI systems, automation, building monitoring systems, helping customers to make value from their data & explore and learn. By passion Manohar is a blogger, speaker and Vice President of DataPlatformGeeks. He is a community enthusiast and believes strongly in the concept of giving back to the community. Manohar is a speaker at various Data Platform events from SQL Server Day, various user groups, SQLSaturdays, SQLBits, MS Ignite, SSGAS & DPS. His One DMV a Day series is the longest one day series on any topic related to SQL Server available so far.

View all posts by Manohar Punna →

Leave a Reply

Your email address will not be published.