We habe a managed instance with a 12 or so dbs supporting the same application across 2x customers. This has been running find on a 4-core sql managed instance, but at almost exactly midnight local time about 2wks ago, performance dropped substantially. This was seen as excessive CPU (quickly rising to 100% once user work started at 6am that day) and the instance was migrated to 8 cores around midday.
On 8 cores, the Azure metrics for avg and peak CPU then stabilised at the same levels as per the 4 core instance & have remained similar (so the graph is the same shape following user demand on both pre- & post- migration).
This suggests that the service is now 1/2 as efficient as it was prior to its 100% cpu spike.
Analysis shows that the dominant wait by far is SOS_scheduler_yield. At the same time, we noticed that almost every query we checked in the query store registers a huge uplift in Logical Writes at the midnight point where we see the CPU starting to spike.
I did wonder whether something from Paul Randall about VM overcommittment might be relevant https://www.sqlskills.com/blogs/paul/increased-sos_scheduler_yield-waits-on-virtual-machines/ however I don't think that fits the signature for Logical Write increase.
So that might be from 10k writes to 1M+ ie, 2 orders of magnitude.
We are not seeing long running queries, no blocking - the system services lots of short ones..
Corresponding with the high number of logical writes, the PLE has dropped through the floor - now measures in minutes if we're lucky, seconds if not.
the OLTP workload (user demand) is unchanged from before, as are the data volumes.
It looks a bit like stale plans, but on a huge scale - or as if the sqlserver decided to drop once of the optimiser paths from the query implementation.
What's odd is how some plans for the same query show logical writes unchanged before/after but other plans for this query show this huge spike, even though the plan id is the same. And that's not "one problem query" - this pattern shows for almost all the queries we look at. Either they have a single plan & it spiked, or they have a few plans (parameterisation) and some spiked & others didn't. We haven't found a pattern to those yet.
An external DBA review hasn't really turned up anything beyond slightly stale stats (yet when we query for stale stats the ones with high change rate are all refreshed in the past few days & I would be a bit surprised if this were he underlying cause across such a wide spread of queries.
It has been escalated to MS, but I thought I'd post in case ayone else had either experienced anything similar (sudden big uplift in logical writes), particularly on Azure. The way the CPU graph reads, we're using the same % of avg/max CPU before & after the core increase, it's almost exactly double the CPU consumption. Which makes me think it's a factor affecting sqlserver, but not directly from in the db.
Thanks for reading this far & all useful suggestions!
