sql server trace flag 2371

Hi Friends,

This is my 29th blog on SQL Server Trace Flag 2371 in the series of One Trace Flag a Day. You can go to all blogs under that series by clicking here.

There are lots of blogs about SQL Server statistics by many SQL Server professionals. We all know that auto update of statistics triggered based on standard threshold values. These are mention below:

• For temp tables auto update will be trigger for every six modifications
• For a table, having cardinality greater than 6 and less than or equals to 500 then auto update will be trigger for every 500 modifications.
• For a table, having cardinality greater than 500, auto update will be triggered for (500 + 20% of the table) modifications.

If everything is running good then no issue i.e. no performance issue then no need to worry. Now just think about these thresholds in the situation when your table cardinality is too high like 50000 rows or 1000000. In such cases, for high cardinality tables these thresholds may not be good enough. Let say, I have a table with cardinality 50000. We have inserted 20% (10000) new rows. As per the above standard threshold, these 20% newly inserted rows are not sufficient to trigger the auto update for statistics. This may be one of the reasons of performance issue in your case. If you are facing the same kind of issue then no need to worry because Microsoft provide us a trace flag 2371. By using this trace flag; SQL server will decide dynamic threshold value for auto update of statistics on the tables with more than 25000 rows. Higher the number of rows (cardinality) will use lower the threshold value for auto update of statistics.

Let me show you this with TSQL practically. I am going to create a table with 50000 rows.

use master
go
--Drop this database if alreay exist
if DB_ID('StatsDemo2014')>0
begin  
Alter database StatsDemo2014 set single_user 
Drop database StatsDemo2014
end
go
--Create a database for Demo at default files location
Create database StatsDemo2014
go
Use StatsDemo2014
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 master
go
DBCC TRACEON(2371,-1)
go
use StatsDemo2014
go
Select * from xtstatsdemo where id>8000
Go

Now check the auto created statistic on id column from SSMS.

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 trace flag 2371 here to change this threshold. 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
DBCC TRACEOFF(2371,-1)
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 this trace flag 2371 then threshold value to auto update stats will be decreases 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.

Keep in mind that this trace flag introduced from SQL Server 2008 R2 Service Pack 1 onwards. If you ever used this trace flag in your environment, then we will be more than happy to read your comments.

PS: Do not use trace flags in production environment without testing it on non production environments and without consulting because everything comes at a cost.

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