Has performance suffered when migrating from SQL2008 to SQL2016?
As covered in my last post, with Microsoft no longer supporting SQL Server 2008, a lot of businesses are being forced to consider migrating their databases to SQL2016. This is not a trivial task, but there is an extraordinary amount of information on how to achieve this online, so I won’t go into best practice at this point. What I can do is help you avoid a potential ‘gotcha’ that we discovered recently.
First, a bit of background. A customer had two servers that they wanted to bring together into a new virtual machine running the latest operating system and SQL Server. We used Microsoft’s own Database Migration Assistant to do the migration, as one of the things it does is highlight any compatibility issues.
Having successfully moved everything across, we set about reconnecting all the varied applications to the new databases. It all went without a hitch, with no apparent issues.
Except one.
Issues with server speed identified
An SSRS report suite refused to run and refresh at anything like the speed we were accustomed to on the old server. As the VM specification wasn’t quite on a par with at least one of the old servers, we upped the spec and tried again. Still the same lacklustre performance. None of the other migrated systems showed a similar propensity for slowness so we set about investigating what could be wrong with this particular database.
Need to reset compatibility
After much head scratching, we discovered that setting the database compatibility back to 100 (for SQL2008) restored the performance we were expecting. It turns out that SQL2016 and SQL2017 have new query optimizer algorithms, and they can occasionally provide worse plans than the old system. The long term answer is to use the new Query Store to monitor the database. The information discovered can be used to understand which queries are running slowly, and what might be done to speed them up. Apparently, this was a well-known problem when SQL2014 was introduced, and Microsoft publicised the issue. However, since then, Microsoft seemed to have assumed most people are now migrating from SQL2014 to SQL2017 and have not flagged the problem as before.
So now you know. Unexpected performance hit after upgrading? Try resetting the compatibility level on your database.
If you have any further queries about this or another SQL issue, call me on 01635 889223 or email nick.barnes@changepp.co.uk
