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.

SQLServer2016-TraceFlag2371

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

SQLServer2016-TraceFlag2371-2

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

SQLServer2016-TraceFlag2371-3

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

Avatar

About Prince Rastogi

Prince Rastogi is working as Database Administrator at Elephant Insurance, Richmond. He is having more than 8 years of experience and worked in ERP Domain, Wealth Management Domain. Currently he is working in Insurance domain. In the starting of his career he was working on SQL Server, Internet Information Server and Visual Source Safe. He is post graduate in Computer Science. Prince is ITIL certified professional. Prince likes to explore technical things for Database World and Writing Blogs. He is Technical Editor and Blogger at SQLServerGeeks.com. He is a regular speaker at DataPlatformDay events in Delhi NCR. He has also presented some in depth sessions about SQL Server in SQL Server Conferences in Bangalore.

View all posts by Prince Rastogi →

2 Comments on “SQL Server 2016 Trace Flag 2371 Change”

  1. Thanks Prince! for providing some very insightful info. Please provide some insight into how the threshold varies based on cardinality of the table
    For e.g. for 50000 rows what would be the threshold for triggering stats update?

    Thanks and Regards
    Veera

Leave a Reply

Your email address will not be published. Required fields are marked *