sys.dm_exec_background_job_queue_stats (Transact-SQL)

 

THIS TOPIC APPLIES TO: yesSQL Server (starting with 2008)yesAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel Data Warehouse

Returns a row that provides aggregate statistics for each query processor job submitted for asynchronous (background) execution.

System_CAPS_ICON_note.jpg Note


To call this from Azure SQL Data Warehouse or Parallel Data Warehouse, use the name sys.dm_pdw_nodes_exec_background_job_queue_stats.

Column nameData typeDescription
queue_max_lenintMaximum length of the queue.
enqueued_countintNumber of requests successfully posted to the queue.
started_countintNumber of requests that started execution.
ended_countintNumber of requests serviced to either success or failure.
failed_lock_countintNumber of requests that failed due to lock contention or deadlock.
failed_other_countintNumber of requests that failed due to other reasons.
failed_giveup_countintNumber of requests that failed because retry limit has been reached.
enqueue_failed_full_countintNumber of failed enqueue attempts because the queue is full.
enqueue_failed_duplicate_countintNumber of duplicate enqueue attempts.
elapsed_avg_msintAverage elapsed time of request in milliseconds.
elapsed_max_msintElapsed time of the longest request in milliseconds.
pdw_node_idintApplies to: Azure SQL Data Warehouse, Parallel Data Warehouse

The identifier for the node that this distribution is on.

This view returns information only for asynchronous update statistics jobs. For more information about asynchronous update statistics, see Statistics.

On SQL Server requires VIEW SERVER STATE permission on the server.

On SQL Database Premium Tiers requires the VIEW DATABASE STATE permission in the database. On SQL Database Standard and Basic Tiers requires the SQL Database admin account.

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  

Dynamic Management Views and Functions (Transact-SQL)
Execution Related Dynamic Management Views and Functions (Transact-SQL)

Community Additions

ADD
Show: