SQL Server 2016 Trace Flag 2371 Change

Hello Friends,

In SQL Server 2016, we have seen that various trace flag related changes. For example, changes related to trace flag 1117 and 1118. This blog post focused on SQL Server 2016 – Trace Flag 2371 Change. Today, I’ll show you the changes related to trace flag 2371.

Those who don’t know about trace flag 2371 this trace flag dynamically decrease the statistics update threshold value if your table have more than 25000 rows. You can  also click here for more details.

Now we will see the SQL Server 2016 behavior without using (explicitly) trace flag 2371:

use master
go
--Drop this database if alreay exist
if DB_ID('WITHOUT2371')>0
begin  
Alter database WITHOUT2371 set single_user 
Drop database WITHOUT2371
end
go
--Create a database for Demo at default files location
Create database WITHOUT2371
go
Use WITHOUT2371
go
--Create table
create table xtstatsdemo
(
id int not null,
balance int not null,
name varchar(25)
)
go
-- Now I am inserting 50000 rows in the table.
-- Here id and balance both are having same values for demo purpose
set nocount on
declare @counter int =1
declare @balance int =1
while (@counter<=50000)
begin
    if (@counter%2=0)
        begin
        insert into xtstatsdemo values(@counter,@balance,'data1')
        end
    else
        begin
        insert into xtstatsdemo values(@counter,@balance,'data2')
        end
    set @counter = @counter + 1;
    set @balance = @balance + 1;
end
go
--You can check number of rows inside the table
Select count(*) from xtstatsdemo
Go

Now run the below code, here select statement will create a statistic on id column.

USE WITHOUT2371
go
Select * from xtstatsdemo where id>8000
Go

Now check the auto created statistic on id column.

Run the below command to check the details of this newly created statistic.

--change the name of stats as per your environment
DBCC Show_Statistics('xtstatsdemo','_WA_Sys_00000001_21B6055D') with stat_header

As per the standard threshold for update stats, stats will be updated only after (20% of 50000 + 500) 10500 modifications. We are using SQL Server 2016 here to check the inbuilt behavior of trace flag 2371. Now try to delete 8000 rows from the table and then check the stats.

-- Now I am going to delete 8000 rows
delete from xtstatsdemo where id<8001
go
--Statement to update the stats automatically
Select name from xtstatsdemo where id>8000
go
--Now check the stats details and change the name of stats as per your environment
DBCC Show_Statistics('xtstatsdemo','_WA_Sys_00000001_21B6055D') with stat_header

Excellent!

From the above output you can see that statistics have been updated for below standard threshold value. If you are using SQL Server 2016 then the threshold value to auto update stats will be decreased as the cardinality of that table grows. Here, I have shown to you only a single situation on a table with 50000 rows. You can try this with many other situations.

HAPPY LEARNING!

Regards:
Prince Kumar Rastogi

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook

Follow Prince Rastogi on Twitter | Follow Prince Rastogi on FaceBook