sys.dm_exec_background_job_queue_stats (Transact-SQL)

Article
02/28/2023

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

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

Note

To call this from Azure Synapse Analytics or Analytics Platform System (PDW), use the name sys.dm_pdw_nodes_exec_background_job_queue_stats. This syntax is not supported by serverless SQL pool in Azure Synapse Analytics.

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 Synapse Analytics, Analytics Platform System (PDW) 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 and SQL Managed Instance, requires VIEW SERVER STATE permission.

On SQL Database Basic, S0, and S1 service objectives, and for databases in elastic pools, the server admin account, the Microsoft Entra admin account, or membership in the ##MS_ServerStateReader## server role is required. On all other SQL Database service objectives, either the VIEW DATABASE STATE permission on the database, or membership in the ##MS_ServerStateReader## server role is required.

Permissions for SQL Server 2022 and later

Requires VIEW SERVER PERFORMANCE STATE permission on the server.

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 + enqueue_failed_full_count + enqueue_failed_duplicate_count AS float) * 100 AS varchar(20))   
        END AS [Percent Enqueue Failed]  
FROM sys.dm_exec_background_job_queue_stats;  
GO