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]';
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
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
The sp_WhoIsActive stored procedure output above shows that there is no blocking now.
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.
This change in locking behaviour allows the concurrent executions of UPDATE STATISTICS on the same table.
Trace Flag 7471 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 7471 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 7471 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.