Locks with SQL Server UPDATE STATISTICS WITH FULLSCAN

SQL Server UPDATE STATISTICS WITH FULLSCAN is a process that is used to update statistics in SQL Server. This article discusses how SQL Server locking works with the SQL Server Update Statistic command.

Suppose you are investigating a query performance issue; you identify that your statistics are not updated or that the sampling percentage of SQL Server UPDATE STATISTICS is low. The DBA decides to run the UPDATE STATISTICS WITH FULL SCAN to help the query optimizer prepare an optimized query execution plan.

Note: You can refer to the article Find SQL Server Statistics Updated Date to get statistics last update date, percent sample, modification counter.

It is obvious to think whether running the update statistics with a full scan cause any blockings? Let’s find out in this article.

I have a demo table [dbo].[BillingInfo] with 1000000 rows and following indexes.

SELECT i.name AS index_name 
    ,i.type_desc  
    ,is_unique  
    ,ds.type_desc AS filegroup_or_partition_scheme  
    ,ds.name AS filegroup_or_partition_scheme_name  
   ,is_primary_key  
  FROM sys.indexes AS i  
INNER JOIN sys.data_spaces AS ds ON i.data_space_id = ds.data_space_id  
WHERE is_hypothetical = 0 AND i.index_id <> 0   
AND i.object_id = OBJECT_ID('BillingInfo');  
GO  

Before running the update statistics, I turn off automatic statistics updates for demo purposes. I would recommend you not to do this production or critical Database without known subsequences.

Use DBBlogger
Go
ALTER DATABASE DBbLOGGER SET AUTO_UPDATE_STATISTICS OFF
Go
UPDATE STATISTICS dbo.BillingInfo WITH FULLSCAN, ALL
Go

While the update statistics command is in executing state, check the existing locks using the following query.

USE master
GO
SELECT resource_type ,
       resource_subtype ,
      resource_description ,
       request_mode ,
       request_type ,
       request_status ,
      request_session_id  FROM sys.dm_tran_locks
WHERE request_session_id = 61

Let’s analyze the following query results.

In the locking results, we get the following rows.

• Row 1: Shared lock on the Database

• Row 2-6: Schema Stability (SCH-S) lock on the object, index, and stats for the METADATA resource type.

• Row 7: Exclusive lock (X) on the Resource subtype UPDSTATS. Microsoft documentation says that These resource subtypes do not conflict with each other.

Can you execute UPDATE STATISTICS WITH FULL SCAN while alter table statement is running?

You cannot run the SQL Server update statistics while the table is getting altered. As stated earlier, update stats required schema stability lock on the underlying object. Therefore, during the table alter operation, SQL Server cannot get the Sch-S lock, and it will cause blocking.

Let’s test this scenario.

For the demonstration, I started a transaction to alter the table in the new query window(Session id 60). I did not commit the transaction to create the scenario.

Note: Do not leave open transactions for your Database to avoid a blocking scenario.

BEGIN TRAN
Alter table BillingInfo 
ADD testColumn1 int

In another query window(SP ID 57), perform the update statistics on the [BillingInfo] table.

Check the existing locks using sys.dm_tran_locks, and we can see session-id 57 is waiting for the SCH-S lock on the object.

If you run sp_who2, session-id 57 (update statistics) is blocked by SP ID 60(ALTER TABLE).

SQL Server statistics are an essential part of the database maintenance process. They enable the query optimizer to choose the most optimal execution plan for SQL Server queries. We hope you found this blog post to be helpful. If you have any questions about SQL Server statistics, please contact us anytime at rajendra.gupta16@gmail.com. Thank you for reading.