Hi Friends,

This is my 31st blog on SQL Server Trace Flag 2389 in the series of One Trace Flag a Day. You can go to all blogs under that series by click here.

Trace flag 2389, provided by Microsoft to change the behavior of SQL Server optimizer. This trace flag was introduced in SQL Server 2005 SP1. Till now, we all know below things about SQL Server:

  1. By default, SQL Server triggers auto update on statistics based on threshold values. If you want to know about these threshold values then click here.
  2. By default, SQL Server also 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. If you want to know more about this then click here.
  3. By default, if you will run a select query to get the column(s) values from all the rows which have been inserted after the last statistics update then SQL Server estimates them as only 1. This happens because SQL Server doesn’t have information about those inserted rows in the statistics. I will show you this behavior in this discussion later.

If you want to change the default behavior described in 3rd point then you can use trace flag 2389. This trace flag should only work if below condition satisfied:

  1. Leading column of your statistics (Which is going to be used by optimizer for your query plan) should be marked as Ascending by SQL Server.
  2. A covering index should exist with the ascending column as leading key.

Now let me show you the above behavior without trace flag 2389.


Till now, we have a table with 11000 rows and one statistic object due to non clustered index where ‘id’ is a leading column. Statistics also has been updated and leading column in statistic has been marked as ascending (which is pre required condition for working of trace flag 2389) by SQL Server. Now we can say that as per statistics, SQL server has information about all 11000 rows. Now let me try to insert 1288 more rows i.e. below the auto update threshold value.

So, no auto update statistics will trigger i.e. SQL Server don’t know about these newly inserted rows. Now let see what happen in execution plan, if I will select columns from only these newly inserted rows. You can enable Include Actual Execution Plan from management studio or by pressing CTRL+M.


From the above execution plan, you can see that SQL Server estimated number of rows as 1 because it does not have any information about those 1288 new inserted rows in the statistic object. While actual number of rows is 1288. So it’s a big difference in estimated and actual number of rows. This can create a performance issue for large tables because resource allocation for query execution depends on this estimated number of rows. Keep in mind that this default behaviour is not applicable on SQL Server 2014 because of new cardinality estimator. Now let me show you the query plan for same query after enabling the trace flag 2389.


Now from above execution plan it is clear that Actual and Estimated number are same here. This is good for performance perspective.

Do not forget to turn off the trace flags.

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

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook

Follow Prince Rastogi on Twitter | Follow Prince Rastogi on FaceBook