sql server trace flag 2390

Hi Friends,

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

1. From my previous blog on Trace flag 2388, it is clear that by default, SQL Server tracks the status of statistics leading column that can be Ascending, Stationary or Unknown. By default, no action will be taken by SQL Server on the basis of this status information.
2. From my previous blog on Trace flag 2389, it is clear that we can change the default behavior of cardinality estimator in the case where leading statistics column marked as ascending.
3. If you want to change the behavior of cardinality estimator in the case where leading statistics column marked as ascending or unknown then you can use trace flag 2390.

Let me show you this practically. Below TSQL code will create a database, a table and insert 11000 rows.

use master
go
--Enable the trace flag to check the default behavior
DBCC TRACEON(2388,-1)
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 identity(1,1),
name varchar(25)
)
go
--create non clustered index on id column
--this will generate statistics on this index with the same name as of index
create nonclustered index IX_xtstatsdemo_id on xtstatsdemo(id,name)
go 
------ First Block to insert data and update the stats --------
insert into xtstatsdemo values('SQLServerGeeks1');
go 10000
update statistics dbo.xtstatsdemo with fullscan;
go 
------ Second Block to insert data and update the stats --------
insert into xtstatsdemo values('SQLServerGeeks2');
go 750
update statistics dbo.xtstatsdemo with fullscan;
go 
------ Third Block to insert data and update the stats --------
insert into xtstatsdemo values('SQLServerGeeks3');
go 250
update statistics dbo.xtstatsdemo with fullscan;
go 
DBCC SHOW_STATISTICS('xtstatsdemo','IX_xtstatsdemo_id')
Go

From the above output it is clear that leading column is marked as Unknown. No let me insert 1288 more rows.

insert into xtstatsdemo values('SQLServerGeeks5');
go 1288 
DBCC SHOW_STATISTICS('xtstatsdemo','IX_xtstatsdemo_id')
go
DBCC TRACEOFF(2388,-1)
go

From above output, SQL Server has no statistics for newly inserted 1288 rows and leading column still under Unknown status. Now let show you that Trace Flag 2389 will not work here.

DBCC TRACEON(2389,-1)
Go
--OPTION(RECOMPILE) used to create a new plan
Select id,name from xtstatsdemo  where id>11000 OPTION(RECOMPILE)
Go
DBCC TRACEOFF(2389,-1)
Go

From above plan, you can see that estimated number of rows is 1.That means trace flag 2389 will not work if leading column is not marked as ascending. Now let me show you the same with trace flag 2390.

DBCC TRACEON(2390,-1)
Go
--OPTION(RECOMPILE) used to create a new plan
Select id,name from xtstatsdemo  where id>11000 OPTION(RECOMPILE)
Go
DBCC TRACEOFF(2390,-1)
Go

From above execution plan, you can see that estimated numbers of row are 1288. That means trace flag 2390 will work here for leading column, which is marked as Unknown. Keep in mind that by default SQL Server 2014 will not consider estimated number of rows as 1 in this case because of new cardinality estimator.

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