Update Multiple Statistics On The Same Table Concurrently – Locks & Trace Flag 7471

Default Behaviour

By default, only one statistic object (index statistic or column statistic) on a single table can be updated at a time.  This is down to the locks acquired by the database engine during the statistic update operation.  For all automatic and manual statistic update operations, SQL Server acquires ‘Sch-S: SCHEMA’ and ‘X: UPDSTATS’ locks:

  • Sch-S: SCHEMA: The engine uses schema stability (Sch-S) locks when compiling and executing queries.  Sch-S locks do not block any transactional locks, including exclusive (X) locks.  However, concurrent DDL operations, and concurrent DML operations that acquire Sch-M locks, cannot be performed on the table.  In the context of the statistic operation, the schema stability lock ensures that the table itself or indexes on the table are not dropped while the statistic is being updated.
  • X: UPDSTATS: The exclusive (X) lock acquired by the statistics operation is on the UPDSTATS resource of the table – a resource only relevant to statistics operations and which prevents concurrent updates of multiple statistics against the same table.  The UPDSTATS resource exists to limit the number of statistic operations on a single table to one at a time.  Automatic statistics operations do not wait to acquire locks on this resource, they abort if another operation has an exclusive lock on it.  Conversely, manual statistics operations will wait for the resource.

Let’s take a look at this in action.  For this demo, I’m using the AdventureWorks2012 database and Jonathan Kehayias’ scripts to create the Sales.SalesOrderDetailEnlarged table.  In two separate SSMS windows, I will issue UPDATE STATISTICS commands against two different statistic objects belonging to the Sales.SalesOrderDetailEnlarged table.  I will then take a look at the locks and blocking caused by the concurrent statistics operations using Adam Machanic’s sp_whoisactive and the sys.dm_tran_locks DMV:

-- SELECT @@SPID = 56
UPDATE STATISTICS Sales.SalesOrderDetailEnlarged
(PK_SalesOrderDetailEnlarged_SalesOrderID_SalesOrderDetailID)
WITH FULLSCAN;

-- SELECT @@SPID = 57
UPDATE STATISTICS Sales.SalesOrderDetailEnlarged
(IX_SalesOrderDetailEnlarged_ProductID)
WITH FULLSCAN;

Once the update statistics commands have been issued, execute the sp_WhoIsActive stored procedure:

EXEC master.dbo.sp_WhoIsActive
	@get_locks = 1,
	@output_column_list = '[session_id],[status],[blocking_session_id], [wait_info],[locks],[sql_text]';

1

The output above shows that the second update statistic operation issued by session 57 is being blocked by session 56.  Session 57 will only complete once the first update statistics operation issued by session 56 completes and releases the exclusive (X) lock it holds.

Querying sys.dm_tran_locks reveals similar information:

SELECT 	request_session_id, resource_type,
		resource_subtype, resource_description,
		request_status, request_mode
FROM sys.dm_tran_locks
WHERE request_session_id IN (56,57)
AND request_owner_type != 'SHARED_TRANSACTION_WORKSPACE'
ORDER BY request_session_id

2

The output above again shows the exclusive (X) lock taken on the OBJECT.UPDSTATS resource by session 56 is blocking session 57 that is also attempting to acquire an exclusive (X) lock on the same resource.

Behaviour Under Trace Flag 7471

Running multiple UPDATE STATISTICS commands for different statistics on a single table concurrently has been available under global Trace Flag 7471 since SQL Server 2014 SP1 CU6 and SQL Server 2016 CU1.  Microsoft have documented this trace flag here and here.

Let’s enable the trace flag:

DBCC TRACEON (7471,-1);
GO

Now, re-issue the same update statistics commands and take a look at the locks/blocks using the same methods as before:3

The sp_WhoIsActive stored procedure output above shows that there is no blocking now.

4

Looking at sys.dm_tran_locks we can see the locking behaviour has changed under Trace Flag 7471.  Previously, an exclusive (X) lock was acquired on the OBJECT.UPDSTATS resource and now, under Trace Flag 7471 an update (U) lock is acquired on the METADATA.STATS resource for the specific statistic object that is being updated.  This can be seen in the rows highlighted in red in the image above and in the image below where the resource_ description column shows the update (U) locks taken on the separate statistic metadata resources; stats_id = 1 and stats_id = 3.

5

This change in locking behaviour allows the concurrent executions of UPDATE STATISTICS on the same table.

Conclusion

Trace Flag 4741 could be leveraged to potentially reduce the overall execution time of update statistics maintenance jobs as shown by Jonathan Kehayias.  It’s probably only practical for VLDBs running on capable hardware and with a sufficient I/O subsystem though.

Microsoft’s recommended best practice is to only use TF 4741 during dedicated maintenance windows due to the fact that it increases the possibility of deadlocks.  There are other factors to consider too though:

  • Trace Flag 4741 is a global trace flag meaning all databases on the SQL Server instance will be affected.
  • Information regarding this trace flag is sparse both in terms of official documentation and un-official documentation (blog posts etc) suggesting that it isn’t widely used.
  • Updating multiple statistics on a single table concurrently can be very resource intensive. Updating four separate statistics with a full scan on the same table concurrently will still require four scans of the table.

 

Advertisements
This entry was posted in Statistics, CE & QO. Bookmark the permalink.

One Response to Update Multiple Statistics On The Same Table Concurrently – Locks & Trace Flag 7471

  1. Pingback: Updating Multiple Statistics Concurrently – Curated SQL

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s