This is one of the blog post in a series of posts about optimizing SQL Server queries. A list of blog posts about query tuning can be found here – https://jbswiki.com/2021/04/15/sql-server-query-optimization-introduction/

-> Execute the below script to create the required database, objects for the demo.

create database JB_Update_Stats
go

use JB_Update_Stats
go

create table Table1(
sno int primary key identity (1,1),
sname1 char(800),
sname2 char(800),
sname3 char(800),
sname4 char(800),
sname5 char(800),
sname6 char(800))
CREATE NONCLUSTERED INDEX [Ix_Table1] ON [dbo].[Table1]([sname1] ASC)
CREATE NONCLUSTERED INDEX [Ix_Table2] ON [dbo].[Table1]([sname2] ASC)
CREATE NONCLUSTERED INDEX [Ix_Table3] ON [dbo].[Table1]([sname3] ASC)
CREATE NONCLUSTERED INDEX [Ix_Table4] ON [dbo].[Table1]([sname4] ASC)
CREATE NONCLUSTERED INDEX [Ix_Table5] ON [dbo].[Table1]([sname5] ASC)
CREATE NONCLUSTERED INDEX [Ix_Table6] ON [dbo].[Table1]([sname6] ASC)
GO

set nocount on
declare @i int = 1
while (@i<300000)
begin
insert into Table1 values (‘a’,’b’,’c’,’d’,’e’,’f’)
set @i = @i + 1
end

-> The query to populate “Table1” took close to 45 mins in my lab machine.

-> I am using SQL server 2014 with Service pack 2 in my lab machine.

-> I dont have any trace flags enabled on the SQL server instance.

-> Open a new query window, consider this as session1 and note down the SPID for this session.Execute the below query,

update statistics table1 ([Ix_Table1]) with fullscan

-> Open another query window, consider this as session2 and note down the SPID for this session. Execute the below query,

update statistics table1 ([Ix_Table2]) with fullscan

-> Open another query window and execute the below,

select * from master..sysprocesses where blocked>0
go
sp_lock
go
sp_lock
go

-> The output above reveals that SPID 57 is blocked by SPID 54 and the wait type indicates an Exclusive lock.

-> The sp_lock output provides more details. SPID 54 which is executing update statistics on “table1 ([Ix_Table1])” holds an exclusive lock on the table level on object id 245575913, which is “Table1”.

-> SPID 57 in the sp_lock output shows that it is waiting for an exclusive lock on the table level on object id 245575913, which is “Table1”.

-> The above behavior prevents concurrent updates of multiple statistics against the same table.

Effects of Trace flag 7471

-> I am turning trace flag 7471 now,

-> Open a new query window, consider this as session3 and note down the SPID for this session.Execute the below query,

update statistics table1 ([Ix_Table1]) with fullscan

-> Open another query window, consider this as session4 and note down the SPID for this session. Execute the below query,

update statistics table1 ([Ix_Table2]) with fullscan

-> Open another query window and execute the below,

select * from master..sysprocesses where blocked>0
go
sp_lock
go
sp_lock
go

-> After enabling trace flag 7471 we dont see any blockings. Concurrent updates of multiple statistics against the same table seems possible.

-> Trace flag 7471 can be added to the startup parameters to make sure it is active even after SQL server restarts.

-> Check https://support.microsoft.com/en-us/help/3156157/running-multiple-update-statistics-for-different-statistics-on-a-single-table-concurrently-is-available  for more details on trace flag 7471. As per the article this trace flag is first introduced in SQL server 2014 SP1 CU6 and SQL server 2016 CU6.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.