0

In the near future the current the current database infrastructure is to be migrated to SQL Server 2017 from 2008R2.

Once all of the databases have been migrated to the new server I would like to upgrade the compatibility level to 140. This is to take advantage of the new features in 2017, such as the Adaptive Query Engine.

However many have warned that this will have an implications on cardinality estimators and other features.

How can I test the implications of changing the compatibility level to ensure that there has been little to no impact as a result of the change?

templatetypedef
  • 362,284
  • 104
  • 897
  • 1,065
Thomas Foster
  • 1
  • Test everything on the test environment – Stanislav Kundii Jul 16 '18 at 14:24
  • Capture a profiler trace and replay it. Or at the very least identify your most important/expensive queries and replay those. There is, unfortunately, no substitute for testing. One option worth mentioning is that you can selectively [disable the new estimator per query](https://blogs.msdn.microsoft.com/sql_server_team/developers-choice-use-hint-query-hints/), so one option is to assume "all will be well" initially, then apply the hint if it turns out all is not well. Clearly, though, duplicating your existing workload on the new server *first* is a safer approach. – Jeroen Mostert Jul 16 '18 at 14:24

0 Answers0