We recently upgraded from SQL Server 2008R2 to SQL Server 2014 SP1 + CU4.
After a couple of weeks, there were problems with execution plans not properly estimating row counts. The problem got so bad at one point that the decision was made to revert back to the old cardinality estimator by enabling the 9481 traceflag and UPDATEing statistics again. When I say "got so bad" I'm referring to the execution times for queries increasing by a magnitude of 10 in some cases.
Using traceflag 9481 has resolved the problem but this can't be the solution can it?
Searching Google has shown some taking the old cardinality estimator route and others using a combination of 2312 and 4199 to use the new estimator.
So after upgrading from 2008R2 to 2014, what combination of traceflags (if any) and other steps should we be taking?
Thanks, Craig
Update April 26th 9am
The 4199 traceflag does not turn on the new Cardinality Estimator. I had to use the 2312 traceflag instead.
With the 4199 traceflag, the version was still on 70. The answer from Chris Wood reminded me of an article from Brent Ozar I'd also read at some point. Still waiting to see if the execution times improve.
110
, i.e for SQL Server 2012. Doing this will force SQL Server to use old optimizer. Regarding execution plan as you said you run one query withquerytraceon
hint for old optimizer and other without it and compare the execution plans.