Returns a row that provides aggregate statistics for each query processor job submitted for asynchronous (background) execution.
Maximum length of the queue.
Number of requests successfully posted to the queue.
Number of requests that started execution.
Number of requests serviced to either success or failure.
Number of requests that failed due to lock contention or deadlock.
Number of requests that failed due to other reasons.
Number of requests that failed because retry limit has been reached.
Number of failed enqueue attempts because the queue is full.
Number of duplicate enqueue attempts.
Average elapsed time of request in milliseconds.
Elapsed time of the longest request in milliseconds.
This view returns information only for asynchronous update statistics jobs. For more information about asynchronous update statistics, see Using Statistics to Improve Query Performance.
A. Determining the percentage of failed background jobs
The following example returns the percentage of failed background jobs for all executed queries.
SELECT CASE ended_count WHEN 0 THEN 'No jobs ended' ELSE CAST((failed_lock_count + failed_giveup_count + failed_other_count) / CAST(ended_count AS float) * 100 AS varchar(20)) END AS [Percent Failed] FROM sys.dm_exec_background_job_queue_stats; GO
B. Determining the percentage of failed enqueue attempts
The following example returns the percentage of failed enqueue attempts for all executed queries.
SELECT CASE enqueued_count WHEN 0 THEN 'No jobs posted' ELSE CAST((enqueue_failed_full_count + enqueue_failed_duplicate_count) / CAST(enqueued_count AS float) * 100 AS varchar(20)) END AS [Percent Enqueue Failed] FROM sys.dm_exec_background_job_queue_stats; GO