I upgraded our servers to SQL 2014 and found it a pretty smooth experience. Thomas LaRock has a nice set of advice here – thanks Tom – with explanations of why and links to further reading.
We started from a mix of mostly SQL 2012 but a few stragglers on SQL 2008R2 that, for various reasons, I hadn’t got round to. I kept the compatibility levels as they were for a while to bed things in. There were no problems at all.
So then I changed the compatibility levels to 120 (SQL 2014) which invokes the new cardinality estimator. As far as I can see, performance is at least as good as before but two query plans did go bad. One needed a little refactoring but the other was very stubborn. After running out of other ideas I used the query hint OPTION (QUERYTRACEON 9481) to force the old cardinality model and that fixed it. I don’t like query hints but in this case I’ll make an exception.
I also notice a distinct improvement in the missing index recommendations in the Performance Dashboard report. For several years it’s been recommending two or three indexes that we already have. Those are gone now and that’s a good sign in my book.