Erin Stellato looks at using Trace Flag 2389 with the new cardinality estimator in SQL Server 2014:
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).
I highly recommend reading this article.Comments closed