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
--Drop this database if alreay exist
if DB_ID('WITHOUT2371')>0
Alter database WITHOUT2371 set single_user 
Drop database WITHOUT2371
--Create a database for Demo at default files location
Create database WITHOUT2371
--Create table
create table xtstatsdemo
id int not null,
balance int not null,
name varchar(25)
-- 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)
    if (@counter%2=0)
        insert into xtstatsdemo values(@counter,@balance,'data1')
        insert into xtstatsdemo values(@counter,@balance,'data2')
    set @counter = @counter + 1;
    set @balance = @balance + 1;
--You can check number of rows inside the table
Select count(*) from xtstatsdemo

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

Select * from xtstatsdemo where id>8000

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
--Statement to update the stats automatically
Select name from xtstatsdemo where id>8000
--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


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 →

8 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

  2. The article is a valuable resource for SQL Server administrators and developers looking to optimize database performance. Overall, it is a well-written and informative piece that showcases the author’s expertise and commitment to sharing knowledge with the SQL Server community.

  3. The article stands out by breaking down complex concepts into easily understandable language, making it accessible to both beginners and experienced professionals. It goes beyond technical explanations, discussing considerations and best practices for using the trace flag effectively. Overall, this article is a valuable resource for optimizing query performance in SQL Server 2016, combining technical knowledge with practical guidance in a positive and unique way.

  4. The author’s ability to explain the significance of trace flag 2371 and its impact on query performance is commendable. Furthermore, the article stands out by providing practical examples and step-by-step instructions, empowering readers to implement the suggested changes effectively. Overall, this article is a great resource for anyone seeking to enhance their SQL Server performance and maximize their database’s efficiency. Kudos to the author for delivering such a positive and enlightening piece!

  5. The author’s expertise shines through as they explain how Trace Flag 2371 can enhance query optimization by addressing cardinality estimation issues. The inclusion of practical examples and step-by-step instructions ensures that readers can easily implement this flag in their own environments. Overall, this article showcases a unique blend of technical depth, clarity, and practicality, making it a must-read for anyone seeking to optimize their SQL Server performance.

  6. The author’s expertise shines through, as they explain complex technical details in a user-friendly way, making it an invaluable resource for those seeking to optimize SQL Server performance and enhance their database management skills.

  7. The real-world scenarios and step-by-step guidance provided in this piece make it a valuable resource for database administrators and enthusiasts. Thank you, SQLServerGeeks, for sharing this insightful information and helping us navigate the complexities of SQL Server management!

Leave a Reply

Your email address will not be published.