Learn more about SQL Server tools

 

Tutorials          DBA          Dev          BI          Career          Categories          Events          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Events

DBA    Dev    BI    Categories

 

Does updating SQL Server statistics cause blocking


By:   |   Read Comments (1)   |   Related Tips: More > Locking and 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.

Screen Capture of sys.dm_tran_locks Results.

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.

Column name

Data type

Description

resource_type

nvarchar(60)

Represents the resource type. The value can be one of the following: DATABASE, FILE, OBJECT, PAGE, KEY, EXTENT, RID, APPLICATION, METADATA, HOBT, or ALLOCATION_UNIT.

resource_subtype

nvarchar(60)

Represents a subtype of resource_type. Acquiring a subtype lock without holding a nonsubtyped lock of the parent type is technically valid. Different subtypes do not conflict with each other or with the nonsubtyped parent type. Not all resource types have subtypes.

resource_description

nvarchar(256)

Description of the resource that contains only information that is not available from other resource columns.

resource_associated_entity_id

bigint

ID of the entity in a database with which a resource is associated. This can be an object ID, Hobt ID, or an Allocation Unit ID, depending on the resource type.

request_mode

nvarchar(60)

Mode of the request. For granted requests, this is the granted mode; for waiting requests, this is the mode being requested.

request_type

nvarchar(60)

Request type. The value is LOCK.

request_status

nvarchar(60)

Current status of this request. Possible values are GRANTED, CONVERT, WAIT, LOW_PRIORITY_CONVERT, LOW_PRIORITY_WAIT, or ABORT_BLOCKERS. For more information about low priority waits and abort blockers, see the low_priority_lock_wait section of ALTER INDEX (Transact-SQL).

request_session_id

int

Session ID that currently owns this request. The owning session ID can change for distributed and bound transactions. A value of -2 indicates that the request belongs to an orphaned distributed transaction. A value of -3 indicates that the request belongs to a deferred recovery transaction, such as, a transaction for which a rollback has been deferred at recovery because the rollback could not be completed successfully.

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


Last Update:






About the author
MSSQLTips author Daniel Farina Daniel Farina was born in Buenos Aires, Argentina. Self-educated, since childhood he showed a passion for learning.

View all my tips
Related Resources


 









Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



Friday, December 16, 2016 - 8:35:26 PM - Bill Back To Top

Schema stability locks are incompatible with schema modification locks, so blocking can arise. Updating statistics cause recompilations, which can be bottlenecked in a memory resource semaphore for query compilation within memorystatus' medium or high memory gateways. Before SQL Server 2005 bottleneck was called a comipile blocking chain.

Both causes are rare for most systems, but I have identified and suggested resolutions for them, in all versions of SQL Server since 6.


Learn more about SQL Server tools