sys.dm_exec_background_job_queue_stats – Day 40 – One DMV a Day

Hello Geeks and welcome to the Day 40 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.

Yesterday I have posted on sys.dm_exec_background_job_queue. This DMV shows what is currently running the background queue. Today I am going to talk about another DMV related to background queue. sys.dm_exec_background_job_queue_stats is a DMV which will expose the stats of the background queue.

Sys.dm_exec_background_job_queue_stats, like any other stats related DMV, is cumulative. The data gets flushed on a restart. Currently this DMV only collects stats for auto update statistics. So you will see the stats for asynchronous update statistics.

Sys.dm_exec_background_job_queue_stats provides the below details.

queue_max_len – Length of the background job queue.
enqueued_count – Total number of requests queued to the queue.
started_count – Number of requests started execution. There can be few which are queued but never started due to resource crunch or other reasons.
ended_count – requests either ended successfully or failed.
failed_lock_count – requests failed due to blocking or deadlock.
failed_other_count – requests failed due to other reasons.
failed_giveup_count – requests failed after retrying the limited number of times.
enqueued_failed_full_count – Number of failures because of queue being full.
enqueued_failed_duplicate_count – Number of attempts which are duplicated as previous request is already in queue.
elapsed_avg_time – Average time for requests in the queue (ms).
elapsed_max_ms – The maximum time taken by any request in the queue.

SELECT queue_max_len,
	 enqueued_count,
	 started_count,
	 ended_count,
	 failed_lock_count,
	 failed_other_count,
	 failed_giveup_count,
	 enqueue_failed_full_count,
	 enqueue_failed_duplicate_count,
	 elapsed_avg_ms,
	 elapsed_max_ms
FROM sys.dm_exec_background_job_queue_stats

sys.dm_exec_background_job_queue_stats

To append to yesterday’s knowledge, use sys.dm_exec_background_job_queue_stats to get the stats for asynchronous 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

Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

Avatar

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. Required fields are marked *