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
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
Veera
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.
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.
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!
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.
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.
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!