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.
Prince Kumar Rastogi