Does updating SQL Server statistics cause blocking

Problem

You are experiencing high query execution times. You as the SQL Server DBA decide that updating database statistics is one possible solution to the problem, but your boss says that it will cause blocking and advises not to update statistics while the database is being used. In this tip I show that doing a statistics update does not cause blocking.

Solution

This is a hot topic in the SQL Server DBA community. Some people say that a statistics update blocks transactional activity. They say that the performance of the application running on top of SQL Server runs slower when the statistics are being updated, but there may be other reasons that may degrade performance like the intense disk activity produced when updating statistics.

In this tip, we will cover a scenario where we update statistics while the database is being used to see if blocking does occur during the statistics update.

Setup Example Code

In order to analyze the behavior of updating statistics, first we need to create a sample database with the option AUTO_UPDATE_STATISTICS set to OFF.

USE [master]
GO
CREATE DATABASE [TestDB]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'TestDB_file1', 
   FILENAME = N'E:\MSSQL\TestDB_1.mdf',
   SIZE = 128MB , 
   MAXSIZE = UNLIMITED, 
   FILEGROWTH = 64MB) 
 LOG ON 
( NAME = N'TestDB_log_file1',
    FILENAME = N'E:\MSSQL\TestDB_1.ldf',
    SIZE = 8MB,
    MAXSIZE = 2048GB,
    FILEGROWTH = 8MB)
GO
ALTER DATABASE TestDB SET RECOVERY SIMPLE
GO
ALTER DATABASE TestDB SET AUTO_UPDATE_STATISTICS OFF

Now we create a test table with an index.

USE TestDB
GO
IF OBJECT_ID('dbo.Customers','U') IS NOT NULL
    DROP TABLE dbo.Customers
GO
CREATE TABLE dbo.Customers(
  CustomerId        INT NOT NULL IDENTITY(1,1),
  CustomerCode      NVARCHAR(10) NOT NULL,
  CustomerName      NVARCHAR(50) NOT NULL,
  CustomerAddress   NVARCHAR(50) NOT NULL,
  LastModified  DATETIME NOT NULL DEFAULT  GETDATE()
  PRIMARY KEY CLUSTERED (CustomerId),
  INDEX IX_CustomerCode NONCLUSTERED (CustomerCode)
)
GO

You can use Tibor Nagy’s tip Populating a SQL Server Test Database with Random Data to fill the previous table with data. Please consider that you should add enough data that gives you time when running the statistics update to run Dynamic Management View queries in another session to see what is occurring.

After populating the table with data it’s time to run the statistics update.

USE TestDB
GO
UPDATE STATISTICS dbo.Customers WITH FULLSCAN, ALL

While the statistics update is running, open a new session and execute the following query against sys.dm_tran_locks Dynamic Management View in order to analyze the locked resources by the statistics update. Notice that your session_id most likely will be different than the one in the following query.

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

The following image is a screen capture of the output of the previous query in my test environment.

In order to analyze the previous query results, I included a short version of the table available from MSDN with the description of the sys.dm_tran_locks columns.

Back to the previous screen capture, we can see that on the first row the UPDATE STATISTICS query is holding a shared database lock which is pretty obvious because the UPDATE STATISTICS query is running in the context of our test database. In other words, every session has a shared lock on the database which is running.

Rows two through six tell us that we have locks on the table metadata. You can see on resource_description column the object_id as well as index_id (when resource_subtype is INDEXSTATS) or stats_id (when resource_subtype is STATS). If we look at the request_mode column we can see that the locks are schema stability locks (Sch-S) which is compatible with all lock modes except the schema modification (Sch-M) lock mode.

Rows seven and eight show two schema stability locks on the table.

The last row is the only row that shows an Exclusive lock in the table, but it is of subtype UPDSTATS. If you look back at the table with the column descriptions, you can see that it says different subtypes do not conflict with each other. In other words, the only thing that’s being locked exclusively is the statistics.

So based on this analysis, we can see that the update statistics does not cause blocking issues.

Next Steps

• This tip covers the consequences of having outdated statistics: Issues Caused by Outdated Statistics in SQL Server.
• If you don’t know how locks work, then this is the tip for you: Understanding SQL Server Locking.
• Also check out the SQL Server Locking and Blocking Tips Category to go deeper.
• Stay tuned to the SQL Server Performance Tuning Tips Category for more cool tips.