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
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.
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
2 Comments on “SQL Server 2016 Trace Flag 2371 Change”
Note: this will only work if your database compatibility level is 130. Keep this in mind when restoring a database from an older version
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