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.

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 name Data type Description
queue_max_len int Maximum length of the queue.
enqueued_count int Number of requests successfully posted to the queue.
started_count int Number of requests that started execution.
ended_count int Number of requests serviced to either success or failure.
failed_lock_count int Number of requests that failed due to lock contention or deadlock.
failed_other_count int Number of requests that failed due to other reasons.
failed_giveup_count int Number of requests that failed because retry limit has been reached.
enqueue_failed_full_count int Number of failed enqueue attempts because the queue is full.
enqueue_failed_duplicate_count int Number of duplicate enqueue attempts.
elapsed_avg_ms int Average elapsed time of request in milliseconds.
elapsed_max_ms int Elapsed time of the longest request in milliseconds.
pdw_node_id int Applies to: Azure SQL Data Warehouse, Parallel Data Warehouse

The identifier for the node that this distribution is on.

Remarks

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

Permissions

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.

Examples

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  

See Also

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