We're running SqlServer 2019 on google cloud linux VM (2vcpu, 4gb), using mcr.microsoft.com/mssql/server:latest image. Might be of interest; it's enterprise edition. I understand that 2vcpu & 4gb is not really sufficient for any kind of SqlServer workload, but its currently just a poc/test environment, but shouldn't be relevant as no queries are executed anyway. (maybe?)
Instance hosts about 10 databases, each smaller than the other (few megs each), but databases aren't queried at all. Only external queries executed are SELECT 1 healthchecks (from dotnet core apps).
After few weeks of slow gcloud dashboard movement (cpu ~ 1% at all times), few days ago cpu started to behave oddly. It sticks to 60% most of the time (+/- few %). Pattern is visible on the image below; it sticks to ~60% for 30 min, then drops for few mins, and goes back up to 60%. It once went higher, but most of the time its steady 60%:
When i ssh to the instance, top looks like this:
Additional information;
- MAXDOP is set to 2
- Memory is not limited (mistake on my part, should be ~3gb, considering 4gb vm?)
- Other config: default
I've run query shown here: techcommunity.microsoft.com and got results that look like old 2008 issue: constant resource monitor values 32-33.
How can i find out whats eating the cpu?
I'm just curious in which direction to look... Is it resource monitor-related? Linux? Docker? Bad configuration? Too weak VM?
Edit: reason why i haven't increased VM resources or set ram limits so far is because it took few weeks for this issue to occur, I dont want to make it disappear by restarting the VM. We have 2 more VMs with the same setup, still behaving as expected (~1% cpu).
Edit 2: I added monitoring snapshots of last 48h for this and 2 other VMs running the similar workload - below.
- Column 1: VM1 - topic of the discussion
- Column 2: VM2 - behaves as expected
- Column 3: VM3 - behaves as expected
Memory footprint of all instances is the same:
- VIRT = 12.5g
- RES = 2.5-3.0g
- %MEM = 65%