One of the SQL Server trace flags that’s been around for a while is 2389. It’s often discussed with 2390, but I just want to focus on 2389 for this post. The trace flag was introduced in SQL Server 2005 SP1, which was released on April 18, 2006 (according to http://sqlserverbuilds.blogspot.co.uk/), so it’s been around for over 10 years. Trace flags change the behavior of the engine, and 2389 allows the optimizer to identify statistics which are ascending and brand them as such (often called "the ascending key problem"). When this occurs, the statistics will be updated automatically at query compile time, which means that the optimizer has information about the highest value in the table (compared to when the trace flag is not used).
I had a discussion recently with a client about using this trace flag, and it came up because of this type of scenario:
- You have a large table that has an INT as the primary key, and it’s clustered.
- You have a nonclustered index that leads on a DATETIME column.
- The table has about 20 million rows in it, and anywhere from 5,000 to 100,000 rows are added each day.
- Statistics are updated nightly as part of your maintenance task.
- Auto-update statistics is enabled for the database, but even if 100,000 rows are added to the table, that’s way less than the 4 million rows (20%) needed to invoke an automatic update.
- When users query the table using the date in the predicate, query performance can be great, or it can be awful.
That last bullet almost makes it sounds like a parameter sensitivity issue, but it’s not. In this case, it’s a statistics issue. My suggestion to the client was using TF 2389, or updating statistics more frequently throughout the day (e.g. via an Agent Job). Then I thought I’d do some testing, since the client was running SQL Server 2014. This is where things got interesting.
The Setup
We’re going to create the aforementioned table for testing in the RTM build of SQL Server 2016, within the WideWorldImporters database, and I’m going to set the compatibility mode to 110 initially:
USE [master];
GO
RESTORE DATABASE [WideWorldImporters]
FROM DISK = N'C:\Backups\WideWorldImporters-Full.bak'
WITH FILE = 1,
MOVE N'WWI_Primary' TO N'C:\Databases\WideWorldImporters\WideWorldImporters.mdf',
MOVE N'WWI_UserData' TO N'C:\Databases\WideWorldImporters\WideWorldImporters_UserData.ndf',
MOVE N'WWI_Log' TO N'C:\Databases\WideWorldImporters\WideWorldImporters.ldf',
MOVE N'WWI_InMemory_Data_1' TO N'C:\Databases\WideWorldImporters\WideWorldImporters_InMemory_Data_1',
NOUNLOAD, REPLACE, STATS = 5;
GO
ALTER DATABASE [WideWorldImporters] SET COMPATIBILITY_LEVEL = 110;
GO
USE [WideWorldImporters];
GO
CREATE TABLE [Sales].[BigOrders](
[OrderID] [int] NOT NULL,
[CustomerID] [int] NOT NULL,
[SalespersonPersonID] [int] NOT NULL,
[PickedByPersonID] [int] NULL,
[ContactPersonID] [int] NOT NULL,
[BackorderOrderID] [int] NULL,
[OrderDate] [date] NOT NULL,
[ExpectedDeliveryDate] [date] NOT NULL,
[CustomerPurchaseOrderNumber] [nvarchar](20) NULL,
[IsUndersupplyBackordered] [bit] NOT NULL,
[Comments] [nvarchar](max) NULL,
[DeliveryInstructions] [nvarchar](max) NULL,
[InternalComments] [nvarchar](max) NULL,
[PickingCompletedWhen] [datetime2](7) NULL,
[LastEditedBy] [int] NOT NULL,
[LastEditedWhen] [datetime2](7) NOT NULL,
CONSTRAINT [PK_Sales_BigOrders] PRIMARY KEY CLUSTERED
(
[OrderID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [USERDATA]
) ON [USERDATA] TEXTIMAGE_ON [USERDATA];
Next we’re going to load about 24 million rows into BigOrders, and create a nonclustered index on OrderDate.
SET NOCOUNT ON;
DECLARE @Loops SMALLINT = 0, @IDIncrement INT = 75000;
WHILE @Loops < 325 -- adjust this to increase or decrease the number of rows added
BEGIN
INSERT [Sales].[BigOrders]
( [OrderID],
[CustomerID],
[SalespersonPersonID],
[PickedByPersonID],
[ContactPersonID],
[BackorderOrderID],
[OrderDate],
[ExpectedDeliveryDate],
[CustomerPurchaseOrderNumber],
[IsUndersupplyBackordered],
[Comments],
[DeliveryInstructions],
[InternalComments],
[PickingCompletedWhen],
[LastEditedBy],
[LastEditedWhen]
)
SELECT
[OrderID] + @IDIncrement,
[CustomerID],
[SalespersonPersonID],
[PickedByPersonID],
[ContactPersonID],
[BackorderOrderID],
[OrderDate],
[ExpectedDeliveryDate],
[CustomerPurchaseOrderNumber],
[IsUndersupplyBackordered],
[Comments],
[DeliveryInstructions],
[InternalComments],
[PickingCompletedWhen],
[LastEditedBy],
[LastEditedWhen]
FROM [Sales].[Orders];
CHECKPOINT;
SET @Loops = @Loops + 1;
SET @IDIncrement = @IDIncrement + 75000;
END
CREATE NONCLUSTERED INDEX [NCI_BigOrders_OrderDate]
ON [Sales].[BigOrders] ([OrderDate], CustomerID);
If we check the histogram for the nonclustered index, we see the highest date is 2016-05-31:
DBCC SHOW_STATISTICS ('Sales.BigOrders',[NCI_BigOrders_OrderDate]);
Statistics for the NCI on OrderDate
If we query for any date beyond that, note the estimated number of rows:
SELECT CustomerID, OrderID, SalespersonPersonID
FROM [Sales].[BigOrders]
WHERE [OrderDate] = '2016-06-01';
Plan when querying for a date beyond what's in the histogram
It’s 1, because the value is outside the histogram. And in this case, that’s ok, because there are no rows in the table beyond May 31, 2016. But let’s add some and then re-run the same query:
INSERT [Sales].[BigOrders]
( [OrderID],
[CustomerID],
[SalespersonPersonID],
[PickedByPersonID],
[ContactPersonID],
[BackorderOrderID],
[OrderDate],
[ExpectedDeliveryDate],
[CustomerPurchaseOrderNumber],
[IsUndersupplyBackordered],
[Comments],
[DeliveryInstructions],
[InternalComments],
[PickingCompletedWhen],
[LastEditedBy],
[LastEditedWhen]
)
SELECT
[OrderID] + 25000000,
[CustomerID],
[SalespersonPersonID],
[PickedByPersonID],
[ContactPersonID],
[BackorderOrderID],
'2016-06-01',
[ExpectedDeliveryDate],
[CustomerPurchaseOrderNumber],
[IsUndersupplyBackordered],
[Comments],
[DeliveryInstructions],
[InternalComments],
[PickingCompletedWhen],
[LastEditedBy],
[LastEditedWhen]
FROM [Sales].[Orders];
GO
SELECT CustomerID, OrderID, SalespersonPersonID
FROM [Sales].[BigOrders]
WHERE [OrderDate] = '2016-06-01';
Plan after adding rows past May 31
The estimated number of rows is still 1. But this is where things get interesting. Let’s change the compatibility mode to 130 so that we use the new Cardinality Estimator and see what happens.
USE [master];
GO
ALTER DATABASE [WideWorldImporters] SET COMPATIBILITY_LEVEL = 130
GO
USE [WideWorldImporters];
GO
SELECT CustomerID, OrderID, SalespersonPersonID
FROM [Sales].[BigOrders]
WHERE [OrderDate] = '2016-06-01';
Plan after adding rows for June 1, using the new CE
Our plan shape is the same, but now our estimate is 4,898 rows. The new CE treats values outside of the history differently than the old CE. So…do we even need trace flag 2389?
The Test – Part I
For the first test, we’re going to stay in compatibility mode 110 and run through what we would see with 2389. When using this trace flag you can either enable it as a startup parameter in the SQL Server service, or you can use DBCC TRACEON to enable it instance-wide. Understand that in your production environment, if you use DBCC TRACEON to enable the trace flag, when the instance restarts the trace flag won’t be in effect.
With the trace flag enabled, a statistic has to be updated three (3) times before the optimizer will brand it as ascending. We’ll force four updates for good measure and add more rows in between each update.
USE [master];
GO
ALTER DATABASE [WideWorldImporters] SET COMPATIBILITY_LEVEL = 110;
GO
DBCC TRACEON (2389, -1);
GO
USE [WideWorldImporters];
GO
UPDATE STATISTICS [Sales].[BigOrders] [NCI_BigOrders_OrderDate];
GO
INSERT [Sales].[BigOrders]
( [OrderID],
[CustomerID],
[SalespersonPersonID],
[PickedByPersonID],
[ContactPersonID],
[BackorderOrderID],
[OrderDate],
[ExpectedDeliveryDate],
[CustomerPurchaseOrderNumber],
[IsUndersupplyBackordered],
[Comments],
[DeliveryInstructions],
[InternalComments],
[PickingCompletedWhen],
[LastEditedBy],
[LastEditedWhen]
)
SELECT
[OrderID] + 25100000,
[CustomerID],
[SalespersonPersonID],
[PickedByPersonID],
[ContactPersonID],
[BackorderOrderID],
'2016-06-02',
[ExpectedDeliveryDate],
[CustomerPurchaseOrderNumber],
[IsUndersupplyBackordered],
[Comments],
[DeliveryInstructions],
[InternalComments],
[PickingCompletedWhen],
[LastEditedBy],
[LastEditedWhen]
FROM [Sales].[Orders];
GO
UPDATE STATISTICS [Sales].[BigOrders] [NCI_BigOrders_OrderDate];
GO
INSERT [Sales].[BigOrders]
( [OrderID],
[CustomerID],
[SalespersonPersonID],
[PickedByPersonID],
[ContactPersonID],
[BackorderOrderID],
[OrderDate],
[ExpectedDeliveryDate],
[CustomerPurchaseOrderNumber],
[IsUndersupplyBackordered],
[Comments],
[DeliveryInstructions],
[InternalComments],
[PickingCompletedWhen],
[LastEditedBy]
[LastEditedWhen]
)
SELECT
[OrderID] + 25200000,
[CustomerID],
[SalespersonPersonID],
[PickedByPersonID],
[ContactPersonID],
[BackorderOrderID],
'2016-06-03',
[ExpectedDeliveryDate],
[CustomerPurchaseOrderNumber],
[IsUndersupplyBackordered],
[Comments],
[DeliveryInstructions],
[InternalComments],
[PickingCompletedWhen],
[LastEditedBy],
[LastEditedWhen]
FROM [Sales].[Orders];
GO
UPDATE STATISTICS [Sales].[BigOrders] [NCI_BigOrders_OrderDate];
GO
INSERT [Sales].[BigOrders]
( [OrderID],
[CustomerID],
[SalespersonPersonID],
[PickedByPersonID],
[ContactPersonID],
[BackorderOrderID],
[OrderDate],
[ExpectedDeliveryDate],
[CustomerPurchaseOrderNumber],
[IsUndersupplyBackordered],
[Comments],
[DeliveryInstructions],
[InternalComments],
[PickingCompletedWhen],
[LastEditedBy],
[LastEditedWhen]
)
SELECT
[OrderID] + 25300000,
[CustomerID],
[SalespersonPersonID],
[PickedByPersonID],
[ContactPersonID],
[BackorderOrderID],
'2016-06-04',
[ExpectedDeliveryDate],
[CustomerPurchaseOrderNumber],
[IsUndersupplyBackordered],
[Comments],
[DeliveryInstructions],
[InternalComments],
[PickingCompletedWhen],
[LastEditedBy],
[LastEditedWhen]
FROM [Sales].[Orders];
GO
UPDATE STATISTICS [Sales].[BigOrders] [NCI_BigOrders_OrderDate];
If we check statistics again, and use the trace flag 2388 to display additional information, we see that the statistic is now marked as Ascending:
DBCC TRACEON (2388);
GO
DBCC SHOW_STATISTICS ('Sales.BigOrders',[NCI_BigOrders_OrderDate]);
NCI on OrderDate marked as ASC
If we query for a future date, when statistics are fully up-to-date, we see that it still estimates 1 row:
SELECT CustomerID, OrderID, SalespersonPersonID
FROM [Sales].[BigOrders]
WHERE [OrderDate] = '2016-06-05';
Plan after TF 2389 enabled, but no rows beyond histogram
Now we’ll add rows for June 5th and run the same query again:
INSERT [Sales].[BigOrders]
( [OrderID],
[CustomerID],
[SalespersonPersonID],
[PickedByPersonID],
[ContactPersonID],
[BackorderOrderID],
[OrderDate],
[ExpectedDeliveryDate],
[CustomerPurchaseOrderNumber],
[IsUndersupplyBackordered],
[Comments],
[DeliveryInstructions],
[InternalComments],
[PickingCompletedWhen],
[LastEditedBy],
[LastEditedWhen]
)
SELECT
[OrderID] + 25400000,
[CustomerID],
[SalespersonPersonID],
[PickedByPersonID],
[ContactPersonID],
[BackorderOrderID],
'2016-06-05',
[ExpectedDeliveryDate],
[CustomerPurchaseOrderNumber],
[IsUndersupplyBackordered],
[Comments],
[DeliveryInstructions],
[InternalComments],
[PickingCompletedWhen],
[LastEditedBy],
[LastEditedWhen]
FROM [Sales].[Orders];
GO
SELECT CustomerID, OrderID, SalespersonPersonID
FROM [Sales].[BigOrders]
WHERE [OrderDate] = '2016-06-05';
Plan after TF 2389 enabled, 70K+ rows added beyond histogram
Our estimate is no longer 1, it’s 22,595. Now, just for fun, let’s disable the trace flag and see what the estimate is (I’m going to clear procedure cache, as disabling the trace flag won’t affect what’s currently in cache).
DBCC TRACEOFF (2389, -1);
GO
DBCC FREEPROCCACHE;
GO
SELECT CustomerID, OrderID, SalespersonPersonID
FROM [Sales].[BigOrders]
WHERE [OrderDate] = '2016-06-05';
Plan after TF 2389 is *disabled*, 70K+ rows added beyond histogram
This time around I get an estimate of 1 row again. Even though the statistic is branded as ascending, if trace flag 2389 is not enabled, it only estimates 1 row when you query for a value outside the histogram.
We’ve demonstrated that trace flag 2389 does what we expect – what it always has done – when using the old Cardinality Estimator. Now let’s see what happens with the new one.
The Test – Part II
To be thorough, I’m going to reset everything. I will create the database again, set the compatibility mode to 130, load the data initially, then turn on trace flag 2389 and load three sets of data with stats updates in between.
USE [master];
GO
RESTORE DATABASE [WideWorldImporters]
FROM DISK = N'C:\Backups\WideWorldImporters-Full.bak'
WITH FILE = 1,
MOVE N'WWI_Primary' TO N'C:\Databases\WideWorldImporters\WideWorldImporters.mdf',
MOVE N'WWI_UserData' TO N'C:\Databases\WideWorldImporters\WideWorldImporters_UserData.ndf',
MOVE N'WWI_Log' TO N'C:\Databases\WideWorldImporters\WideWorldImporters.ldf',
MOVE N'WWI_InMemory_Data_1' TO N'C:\Databases\WideWorldImporters\WideWorldImporters_InMemory_Data_1',
NOUNLOAD, REPLACE, STATS = 5;
GO
USE [master];
GO
ALTER DATABASE [WideWorldImporters] SET COMPATIBILITY_LEVEL = 130;
GO
USE [WideWorldImporters];
GO
CREATE TABLE [Sales].[BigOrders](
[OrderID] [int] NOT NULL,
[CustomerID] [int] NOT NULL,
[SalespersonPersonID] [int] NOT NULL,
[PickedByPersonID] [int] NULL,
[ContactPersonID] [int] NOT NULL,
[BackorderOrderID] [int] NULL,
[OrderDate] [date] NOT NULL,
[ExpectedDeliveryDate] [date] NOT NULL,
[CustomerPurchaseOrderNumber] [nvarchar](20) NULL,
[IsUndersupplyBackordered] [bit] NOT NULL,
[Comments] [nvarchar](max) NULL,
[DeliveryInstructions] [nvarchar](max) NULL,
[InternalComments] [nvarchar](max) NULL,
[PickingCompletedWhen] [datetime2](7) NULL,
[LastEditedBy] [int] NOT NULL,
[LastEditedWhen] [datetime2](7) NOT NULL,
CONSTRAINT [PK_Sales_BigOrders] PRIMARY KEY CLUSTERED
(
[OrderID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON) ON [USERDATA]
) ON [USERDATA] TEXTIMAGE_ON [USERDATA];
GO
SET NOCOUNT ON;
DECLARE @Loops SMALLINT = 0;
DECLARE @IDIncrement INT = 75000;
WHILE @Loops < 325 -- adjust this to increase or decrease the number of rows added
BEGIN
INSERT [Sales].[BigOrders]
( [OrderID],
[CustomerID],
[SalespersonPersonID],
[PickedByPersonID],
[ContactPersonID],
[BackorderOrderID],
[OrderDate],
[ExpectedDeliveryDate],
[CustomerPurchaseOrderNumber],
[IsUndersupplyBackordered],
[Comments],
[DeliveryInstructions],
[InternalComments],
[PickingCompletedWhen],
[LastEditedBy],
[LastEditedWhen]
)
SELECT
[OrderID] + @IDIncrement,
[CustomerID],
[SalespersonPersonID],
[PickedByPersonID],
[ContactPersonID],
[BackorderOrderID],
[OrderDate],
[ExpectedDeliveryDate],
[CustomerPurchaseOrderNumber],
[IsUndersupplyBackordered],
[Comments],
[DeliveryInstructions],
[InternalComments],
[PickingCompletedWhen],
[LastEditedBy],
[LastEditedWhen]
FROM [Sales].[Orders];
CHECKPOINT;
SET @Loops = @Loops + 1;
SET @IDIncrement = @IDIncrement + 75000;
END
CREATE NONCLUSTERED INDEX [NCI_BigOrders_OrderDate]
ON [Sales].[BigOrders] ([OrderDate], CustomerID);
GO
INSERT [Sales].[BigOrders]
( [OrderID],
[CustomerID],
[SalespersonPersonID],
[PickedByPersonID],
[ContactPersonID],
[BackorderOrderID],
[OrderDate],
[ExpectedDeliveryDate],
[CustomerPurchaseOrderNumber],
[IsUndersupplyBackordered],
[Comments],
[DeliveryInstructions],
[InternalComments],
[PickingCompletedWhen],
[LastEditedBy],
[LastEditedWhen]
)
SELECT
[OrderID] + 25000000,
[CustomerID],
[SalespersonPersonID],
[PickedByPersonID],
[ContactPersonID],
[BackorderOrderID],
'2016-06-01',
[ExpectedDeliveryDate],
[CustomerPurchaseOrderNumber],
[IsUndersupplyBackordered],
[Comments],
[DeliveryInstructions],
[InternalComments],
[PickingCompletedWhen],
[LastEditedBy],
[LastEditedWhen]
FROM [Sales].[Orders];
GO
DBCC TRACEON (2389, -1);
GO
UPDATE STATISTICS [Sales].[BigOrders] [NCI_BigOrders_OrderDate];
GO
INSERT [Sales].[BigOrders]
( [OrderID],
[CustomerID],
[SalespersonPersonID],
[PickedByPersonID],
[ContactPersonID],
[BackorderOrderID],
[OrderDate],
[ExpectedDeliveryDate],
[CustomerPurchaseOrderNumber],
[IsUndersupplyBackordered],
[Comments],
[DeliveryInstructions],
[InternalComments],
[PickingCompletedWhen],
[LastEditedBy],
[LastEditedWhen]
)
SELECT
[OrderID] + 25100000,
[CustomerID],
[SalespersonPersonID],
[PickedByPersonID],
[ContactPersonID],
[BackorderOrderID],
'2016-06-02',
[ExpectedDeliveryDate],
[CustomerPurchaseOrderNumber],
[IsUndersupplyBackordered],
[Comments],
[DeliveryInstructions],
[InternalComments],
[PickingCompletedWhen],
[LastEditedBy],
[LastEditedWhen]
FROM [Sales].[Orders];
GO
UPDATE STATISTICS [Sales].[BigOrders] [NCI_BigOrders_OrderDate];
GO
INSERT [Sales].[BigOrders]
( [OrderID],
[CustomerID],
[SalespersonPersonID],
[PickedByPersonID],
[ContactPersonID],
[BackorderOrderID],
[OrderDate],
[ExpectedDeliveryDate],
[CustomerPurchaseOrderNumber],
[IsUndersupplyBackordered],
[Comments],
[DeliveryInstructions],
[InternalComments],
[PickingCompletedWhen],
[LastEditedBy],
[LastEditedWhen]
)
SELECT
[OrderID] + 25200000,
[CustomerID],
[SalespersonPersonID],
[PickedByPersonID],
[ContactPersonID],
[BackorderOrderID],
'2016-06-03',
[ExpectedDeliveryDate],
[CustomerPurchaseOrderNumber],
[IsUndersupplyBackordered],
[Comments],
[DeliveryInstructions],
[InternalComments],
[PickingCompletedWhen],
[LastEditedBy],
[LastEditedWhen]
FROM [Sales].[Orders];
GO
UPDATE STATISTICS [Sales].[BigOrders] [NCI_BigOrders_OrderDate];
GO
INSERT [Sales].[BigOrders]
( [OrderID],
[CustomerID],
[SalespersonPersonID],
[PickedByPersonID],
[ContactPersonID],
[BackorderOrderID],
[OrderDate],
[ExpectedDeliveryDate],
[CustomerPurchaseOrderNumber],
[IsUndersupplyBackordered],
[Comments],
[DeliveryInstructions],
[InternalComments],
[PickingCompletedWhen],
[LastEditedBy],
[LastEditedWhen]
)
SELECT
[OrderID] + 25300000,
[CustomerID],
[SalespersonPersonID],
[PickedByPersonID],
[ContactPersonID],
[BackorderOrderID],
'2016-06-04',
[ExpectedDeliveryDate],
[CustomerPurchaseOrderNumber],
[IsUndersupplyBackordered],
[Comments],
[DeliveryInstructions],
[InternalComments],
[PickingCompletedWhen],
[LastEditedBy],
[LastEditedWhen]
FROM [Sales].[Orders];
GO
UPDATE STATISTICS [Sales].[BigOrders] [NCI_BigOrders_OrderDate];
Ok, so our data is completely loaded. If we check statistics again, and use the trace flag 2388 to display additional information, we see that the statistic is again marked as Ascending:
DBCC TRACEON (2388);
GO
DBCC SHOW_STATISTICS ('Sales.BigOrders',[NCI_BigOrders_OrderDate]);
NCI OrderDate statistic marked as ASC with TF 2389 and compatibility mode 130
Ok, so let’s query for June 5th again:
SELECT CustomerID, OrderID, SalespersonPersonID
FROM [Sales].[BigOrders]
WHERE [OrderDate] = '2016-06-05';
Plan with new CE, no rows beyond what's in histogram
Our estimate is 4,922. Not quite what it was in our first test, but definitely not 1. Now we’ll add some rows for June 5th and re-query:
INSERT [Sales].[BigOrders]
( [OrderID],
[CustomerID],
[SalespersonPersonID],
[PickedByPersonID],
[ContactPersonID],
[BackorderOrderID],
[OrderDate],
[ExpectedDeliveryDate],
[CustomerPurchaseOrderNumber],
[IsUndersupplyBackordered],
[Comments],
[DeliveryInstructions],
[InternalComments],
[PickingCompletedWhen],
[LastEditedBy],
[LastEditedWhen]
)
SELECT
[OrderID] + 25400000,
[CustomerID],
[SalespersonPersonID],
[PickedByPersonID],
[ContactPersonID],
[BackorderOrderID],
'2016-06-05',
[ExpectedDeliveryDate],
[CustomerPurchaseOrderNumber],
[IsUndersupplyBackordered],
[Comments],
[DeliveryInstructions],
[InternalComments],
[PickingCompletedWhen],
[LastEditedBy],
[LastEditedWhen]
FROM [Sales].[Orders];
GO
SELECT CustomerID, OrderID, SalespersonPersonID
FROM [Sales].[BigOrders]
WHERE [OrderDate] = '2016-06-05';
Plan with new CE, with 70K+ rows beyond what's in histogram
The estimate is the same. So now, what if we turn off trace flag 2389?
DBCC TRACEOFF (2389, -1);
GO
DBCC FREEPROCCACHE;
GO
SELECT CustomerID, OrderID, SalespersonPersonID
FROM [Sales].[BigOrders]
WHERE [OrderDate] = '2016-06-05';
Plan with new CE but TF 2389 is NOT enabled, with 70K+ rows beyond what's in histogram
The estimate changed slightly, to 4,930, but it changed. This tells me that trace flag 2389 has some effect on the estimate, but how much is unknown.
The Test – Part III
I ran one final test, where I restored the database, set the compatibility mode to 130, loaded all the data again, updated statistics multiple times, but did NOT enable trace flag 2389. The code is the same as Part II, except for using DBCC TRACEON to enable 2389. When I queried for June 5, both before and after adding the data, the estimated number of rows was 4,920.
What does it mean?
To summarize, when using compatibility mode 110 or below, trace flag 2389 works like it always has. But when using compatibility mode 120 or higher, and thus the new CE, the estimates are not the same compared to the old CE, and in this specific case, are not that different whether using the trace flag or not.
So what should you do? Test, as always. I haven’t found anything documented in MSDN that states that trace flag 2389 is not supported with compatibility mode 120 and higher, nor have I found anything that documents a change in behavior. I do find it very interesting that the estimates are different (in this case much lower) with the new CE. That could potentially be an issue, but there are multiple factors in play when it comes to estimates, and this was a very simple query (one table, one predicate). In this case, the estimate is way off (4920 rows versus the 22,595 rows for the June 5 date).
If I re-run the query for a date that has the same number of rows that is within the histogram, I get a similar plan, but it runs in parallel:
SELECT CustomerID, OrderID, SalespersonPersonID
FROM [Sales].[BigOrders]
WHERE [OrderDate] = '2016-06-02';
Plan for a query that uses a date within the histogram (new CE, no TF)
The estimate is also more accurate (68,318). The plan doesn’t change significantly in this case, but the cost is obviously higher. At some point, depending on the number of rows that would be returned, this could tip to a table scan.
The best guidance at this time if you’re running 2014 or higher and compatibility mode 120 or higher, and you have leading columns in statistics that are ascending, is to test. If you find that the new Cardinality Estimator does not provide as good of an estimate as the old CE, then I would recommend filing a Connect item so the product team is aware of it. There are always one-off and unique cases, but if many customers (read: YOU) consistently find the same behavior – and it’s not ideal – then it’s important to let the development team know about it.
This is another important item to consider when upgrading to 2014 or 2016 – and a reminder to not neglect your testing (and oh by the way, Query Store would be extremely useful here with 2016). Get to it friends.
Would the Query Optimizer Fixes database setting/TF 4199 change any of this behavior?
Great question, I didn't test that permutation, so definitely something to enable and test when using compat mode 120 or lower!
Absolutely fantastic research. Thanks a ot.
This is really a great information. It is very rare to see this kind of internal information about SQL Server. I'm looking forward to read the research paper mentioned in the article too.
HI Erin,
Great info indeed.
MS released the update after your research (I believe) But they also suggested to enable TF 4139
https://support.microsoft.com/en-us/help/2952101/fix-poor-cardinality-estimation-when-the-ascending-key-column-is-branded-as-stationary-in-sql-server
Could you pls let's know will this affect your results.
Regards
Hi Manish-
I expect that could absolutely affect the results; it would need to be tested to determine that definitively. If you're curious about that for something in your environment, I highly recommend that you test it.
Erin