SQL 2014 upgrade experience

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.

 

Advertisements

One thought on “SQL 2014 upgrade experience

  1. raghav

    Really I appreciate.Thanks for providing such useful information. The given information is really very helpful to me. Hope, I will get more information regarding the same in future.

    Like

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s