Does upgrading the Sql compatibility level cause downtime in Azure Sql?
Summary: Not from what we measured, but it did cause a couple of seconds of slowness and a short CPU spike.
We have long been thinking about upgrading from compatibility level 120 to 140 on our main database but we never been able to find the answer to if it causes downtime our not so we have been holding off. But now we simply decided to test it one of our staging database first and if there was no obvious issues just run it production when there was a lower load.
On staging we got this result. A small spike coupled with some perceived slowness when clicking around in the application
While staging contains less than 10 gb of data our production database has order of magnitudes more data. Would that make a difference?
The spike did seem higher even if we run that database on a much higher performance layer. So maybe the database size does affect it. In terms of responsiveness the same pattern was observed. There was some slowness for like 20 seconds but everything was up and running.
Hopefully this can help someone make an educated choice on when to upgrade your compatibility level.