5

So I have a bit of an issue with setting up a new Azure SQL serverless database. enter image description here

I keep getting high CPU billed usage which is costing a fortune, I have resorted to even creating a new DB with a new name in a new region, with a new username and password as well as locking down the firewall. Backups are disabled as well as any auto features. But as soon as the deployment finishes, I start getting billed.

enter image description here

I have run sql profiler and nothing is even touching the DB.

Anyone had this issue before, or know where to start looking?

Altus Baard
  • 151
  • 1
  • 11
  • What is the auto-pause value set to? The minimum is 1 hour. – wBob Dec 21 '20 at 09:13
  • That is set to 1 hour, but it never pauses as there is some type of usage going on that I do not know where it originates from. – Altus Baard Dec 21 '20 at 09:24
  • I’ve experienced similar. You can run sp_who2 or install sp_whoIsActive which will give you more information on any active connections, kill any you don’t need / recognise as long as you know what you’re doing and understand the implications of the kill command. One unpleasant workaround is to change the tier of the db temporarily to Basic or Standard then change it back. This seemed to work but obviously does not get to the root cause. What we really need is the ability to manually pause the db so there is feature-parity with Synapse and tools to properly diagnose what keeps db awake. – wBob Dec 21 '20 at 09:49
  • You mention Profiler - are you running this via Azure Data Studio? This starts Extended Event sessions, maybe there is a connection. I had a feeling it was Azure DevOps with mine but never got to the bottom of it. – wBob Dec 21 '20 at 09:51
  • I just changed it to a basic db, will try convert to serverless and report back. I did try sp_who2 but it did not show any relevant connections, at least that I could figure out – Altus Baard Dec 21 '20 at 09:53
  • I went through this exercise, make a change, wait just over an hour, check back, make a change, just ended up deleting it. I can redeploy dev environment from Azure DevOps and tear it down. – wBob Dec 21 '20 at 10:00
  • Tried the conversion from Basic, no success, still started billing as soon as it was done converting – Altus Baard Dec 21 '20 at 11:44
  • So that’s not how it works. It will start billing as soon as it’s converted. It’s supposed to stop after your auto-pause period ie 1 hour in your case as long as there are no active connections. You have to wait. – wBob Dec 21 '20 at 11:52
  • Sorry, that's what I meant, I waited till it was supposed to pause, but it never pauses. – Altus Baard Dec 21 '20 at 13:29
  • I am experiencing the same issue, did anyone find a valid solution? – Andreas Wendl Mar 26 '21 at 09:31

5 Answers5

5

The documentation is fairly clear on the conditions required for autopause to start:

Autopausing is triggered if all of the following conditions are true for the duration of the autopause delay:

Number sessions = 0 CPU = 0 for user workload running in the user pool

It also lists some conditions which might prevent autopause - you should double-check you do not have any of the following enabled:

  • Geo-replication (active geo-replication and auto-failover groups).
  • Long-term backup retention (LTR).
  • The sync database used in SQL data sync. Unlike sync databases, hub and member databases support autopausing.
  • DNS aliasing
  • The job database used in Elastic Jobs (preview).

If you find your database is still not pausing, there are a number of ways to track down active connections:

  1. The Azure portal - the portal allows you to drill down into activity just by clicking on the Intelligent Performance hub and Query Performance Insight blade:

Query Performance Insight

Click on query ids (in the red box in my picture) to focus on individual queries.

  1. Connect to the database and run either sp_who2 or sp_whoIsActive (which is now hosted on github) to list active sessions. If you find active sessions you can either disconnect them (eg active SQL Server Management Studio (SSMS) connections, or Azure Data Studio (ADS)) or kill them, as long as you know what you are doing and understand the implications of the kill command.
  2. One slightly unpleasant workaround I found (here) was switching the tier to Standard and switching it back again to serverless. I expect this just has the effect of killing all the active connections in order to switch the tier and does not get to the root cause bit it did work for me.

I would suggest you attempt to diagnose active connections using method 1 or 2 above and report back.

wBob
  • 13,710
  • 3
  • 20
  • 37
  • So after spinning a bunch of serverless DB's up and down, it just started working right, no idea what, how or why, but it is working as expected now. Really infuriating. – Altus Baard Dec 23 '20 at 13:01
2

Note that the "App CPU billed" metric is not quoted in US dollars. It's quoted in vCore seconds.

So in your case, if you're being billed roughly 40 units per minute, at $0.000145 USD per COMPUTE COST/VCORE/SECOND (current rate that I see on the Compute + Storage screen in the portal), then you're being charged $0.0058 per minute, or about $250/mo.

enter image description here

Not sure what your budget is or if you had any confusion around this metric, but it gave me a mild heart attack when I first saw my graph earlier today and mistakenly thought it was quoted in USD.

Jon
  • 569
  • 2
  • 11
1

I'm not sure if the question was about auto-pausing or just high billing from the get-go.

I have the same issue that I am charged constantly for almost maximum vCores even when nothing is running (seemingly, according to sp_whoisactive) ...

One useful pointer I have found as to the reason is this: https://learn.microsoft.com/en-us/azure/azure-sql/database/serverless-tier-overview#billing wherein

The amount of compute billed is exposed by the following metric:

  • Metric: app_cpu_billed (vCore seconds)
  • Definition: max (min vCores, vCores used, min memory GB * 1/3, memory GB used * 1/3)
  • Reporting frequency: Per minute

This quantity is calculated each second and aggregated over 1 minute.

The killer seems to be "memory GB used". If you are scaled to say 8 vCores and 24GB max memory then it seems like SQL Server grabs all the memory as soon as you do anything and never lets it go ... so even if your CPU is running 10% you have hold of all the memory and get charged for all the "vCores" all the time.

I'm not a DBA at all, but I hear this is normal behaviour for SQL Server so either I missed something obvious or this feels like a rip-off.

Jason Welch
  • 896
  • 5
  • 9
0

We cannot stop/start the SQL Azure servers. A SQL Azure Database resides on a shared host. there are other tenants on that server. You have access to the database, but not to the corresponding hosting server. There really is no way to pause / stop billing for your Azure SQL Database.

Alternatively:

  1. We can reduce the size of the Azure SQL database, which will reduce the cost of the next service hour.
  2. If you really don't want to pay for the database, you can backup the DB to blob storage, delete the database, and then restore it when needed. You can reference this documentation.
Joseph Xu
  • 5,607
  • 2
  • 5
  • 15
0

Just realised some people still come read this for an answer, the answer is/was, I just kept deleting and recreating the database and the problem fixed itself.

So the short answer, I don't know what fixed it, but it is now fixed

Altus Baard
  • 151
  • 1
  • 11