Export (0) Print
Expand All
Expand Minimize

sys.dm_exec_background_job_queue_stats (Transact-SQL)

Applies To: SQL Server 2014, SQL Server 2016 Preview

Topic Status: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).

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

Applies to: SQL Server (SQL Server 2008 through current version), SQL Database V12, Azure SQL Data Warehouse Public Preview.

Note Note

To call this from SQL 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.

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
Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2015 Microsoft