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.

traceflag_2390_1

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

traceflag_2390_2

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.

traceflag_2390_3

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.

traceflag_2390_4

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