Updating sql server

USE [master] GO CREATE DATABASE [Test DB] CONTAINMENT = NONE ON PRIMARY ( NAME = N'Test DB_file1', FILENAME = N'E:\MSSQL\Test DB_1.mdf', SIZE = 128MB , MAXSIZE = UNLIMITED, FILEGROWTH = 64MB) LOG ON ( NAME = N'Test DB_log_file1', FILENAME = N'E:\MSSQL\Test DB_1.ldf', SIZE = 8MB, MAXSIZE = 2048GB, FILEGROWTH = 8MB) GO ALTER DATABASE Test DB SET RECOVERY SIMPLE GO ALTER DATABASE Test DB SET AUTO_UPDATE_STATISTICS OFF USE Test DB GO IF OBJECT_ID('dbo. 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 its time to run the statistics update.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.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.

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

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.

This can be done easily by doing a bulk-insert in a new table and then rename the table to original one.

The required indexes and constraint can be created on a new table as required.

In this tip I show that doing a statistics update does not cause blocking.

You must have an account to comment. Please register or login here!