Events
Mar 31, 11 PM - Apr 2, 11 PM
The ultimate SQL, Power BI, Fabric, and AI community-led event. March 31 - April 2. Use code MSCUST for a $150 discount. Prices go up Feb 11th.
Register todayThis browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
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. |
This view returns information only for asynchronous update statistics jobs. For more information about asynchronous update statistics, see Statistics.
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.
Requires VIEW SERVER PERFORMANCE STATE permission on the server.
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
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
Dynamic Management Views and Functions (Transact-SQL)
Execution Related Dynamic Management Views and Functions (Transact-SQL)
Events
Mar 31, 11 PM - Apr 2, 11 PM
The ultimate SQL, Power BI, Fabric, and AI community-led event. March 31 - April 2. Use code MSCUST for a $150 discount. Prices go up Feb 11th.
Register today