Azure SQL Serverless DB

Question

Azure SQL Serverless DB

Dylan Miles 5

Hello -

I have migrated two SQL on-prem server to Azure SQL (Serverless) set to 1hr auto-pause primarily for cost saving. However after reviewing one of my DB are not pausing.

I have a DB A and a DB B on the same SQL Server

DB - A (This is production)

DB -B (This is development)

DB A in this case has not paused once, however DB B is pausing as expected, I have disconnected the application to prove this is not causing it from not auto-pausing and is looking very likely to be relating to the backups; however the policy is set the same for both.

This shows a backup almost every hour, if not more often - would this be preventing it from auto-pause; looking at the queries, I would say not?

SELECT *FROM sys.dm_database_backups ORDER BY backup_finish_date DESC;

I have run the following command to check what may be preventing it from auto-pausing and there is a EC2 connecting to it in a sleeping state that I am almost certain is AWS Services for the backup; this wouldn't stop auto-pause as it is in a sleeping state

SELECT session_id,

Upon Checking "Performance Overview" in the specific DB, I can see there is multiple task that is re-occurring queries every hour however these are using minimal to no CPU - could this be causing the issue, pink is also a long running query - could these be preventing auto-pause (long running being in our case 250ms to 2 seconds) - the backup retention policy is set to take a diff backup every 24hrs

User's image

Example 1: (Red)

(@backupTypeEquals nvarchar(1),@backupPathLike nvarchar(36))SELECT TOP 1  [backup_metadata_uuid],[database_guid],[physical_database_name],[time_zone],[first_lsn],[last_lsn],[checkpoint_lsn],[database_backup_lsn],[backup_start_date],[backup_finish_date],[backup_type],[backup_storage_redundancy],[database_version],[backup_size],[compressed_backup_size],[server_name],[is_damaged],[last_recovery_fork_guid],[differential_base_lsn],[differential_base_guid],[backup_path],[last_valid_restore_time],[compression_algorithm],[allocated_db_size_bytes],[allocated_data_size_bytes]  FROM [HIDDEN].[sys].[backup_metadata_store] WHERE (backup_type = @backupTypeEquals) AND (backup_path LIKE '%' + @backupPathLike + '%') ORDER BY backup_start_date DESC

Example 2: (Pink)

SELECT  [backup_metadata_uuid],[database_guid],[physical_database_name],[time_zone],[first_lsn],[last_lsn],[checkpoint_lsn],[database_backup_lsn],[backup_start_date],[backup_finish_date],[backup_type],[backup_storage_redundancy],[database_version],[backup_size],[compressed_backup_size],[server_name],[is_damaged],[last_recovery_fork_guid],[differential_base_lsn],[differential_base_guid],[backup_path],[last_valid_restore_time],[compression_algorithm],[allocated_db_size_bytes],[allocated_data_size_bytes]  FROM [HIDDEN].[sys].[backup_metadata_store]

Example 3: (Dark Blue)

(@backupTypeEquals nvarchar(1),@backupPathLike nvarchar(36),@backupFinishDateGreaterThan nvarchar(22))SELECT  [backup_metadata_uuid],[database_guid],[physical_database_name],[time_zone],[first_lsn],[last_lsn],[checkpoint_lsn],[database_backup_lsn],[backup_start_date],[backup_finish_date],[backup_type],[backup_storage_redundancy],[database_version],[backup_size],[compressed_backup_size],[server_name],[is_damaged],[last_recovery_fork_guid],[differential_base_lsn],[differential_base_guid],[backup_path],[last_valid_restore_time],[compression_algorithm],[allocated_db_size_bytes],[allocated_data_size_bytes]  FROM [HIDDEN].[sys].[backup_metadata_store] WHERE (backup_type = @backupTypeEquals) AND (backup_path LIKE '%' + @backupPathLike + '%') AND (backup_finish_date > @backupFinishDateGreaterThan)

Dylan Miles 5 Reputation points

2023-10-31T13:57:33.5966667+00:00

Query being run to check active activity as not showing above correctly:

SELECT session_id,
host_name,
program_name,
client_interface_name,
login_name,
status,
login_time,
last_request_start_time,
last_request_end_time
FROM sys.dm_exec_sessions AS s
INNER JOIN sys.dm_resource_governor_workload_groups AS wg
ON s.group_id = wg.group_id
WHERE s.session_id <> @@SPID
AND
(
(
wg.name like 'UserPrimaryGroup.DB%'
AND
TRY_CAST(RIGHT(wg.name, LEN(wg.name) - LEN('UserPrimaryGroup.DB') - 2) AS int) = DB_ID()
)
OR
wg.name = 'DACGroup'
);

Reference: https://learn.microsoft.com/en-us/answers/questions/867440/how-did-my-azure-bill-get-this-high

1 answer

Your answer

Dylan Miles 5 Reputation points

2023-10-31T13:57:33.5966667+00:00

Query being run to check active activity as not showing above correctly:

SELECT session_id,
host_name,
program_name,
client_interface_name,
login_name,
status,
login_time,
last_request_start_time,
last_request_end_time
FROM sys.dm_exec_sessions AS s
INNER JOIN sys.dm_resource_governor_workload_groups AS wg
ON s.group_id = wg.group_id
WHERE s.session_id <> @@SPID
AND
(
(
wg.name like 'UserPrimaryGroup.DB%'
AND
TRY_CAST(RIGHT(wg.name, LEN(wg.name) - LEN('UserPrimaryGroup.DB') - 2) AS int) = DB_ID()
)
OR
wg.name = 'DACGroup'
);

Reference: https://learn.microsoft.com/en-us/answers/questions/867440/how-did-my-azure-bill-get-this-high

Answer 1

Auto-pausing is triggered if all of the following conditions are true for the duration of the auto-pause delay:

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

The following features do not support auto-pausing, but do support auto-scaling. If any of the following features are used, then auto-pausing must be disabled and the database will remain online regardless of the duration of database inactivity:

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 auto-pausing.
DNS alias created for the logical server containing a serverless database.
Elastic Jobs (preview), Auto-pause enabled serverless database is not supported as a Job Database. Serverless databases targeted by elastic jobs do support auto-pausing, and will be resumed by job connections.

Auto-pausing is temporarily prevented during the deployment of some service updates which require the database be online. In such cases, auto-pausing becomes allowed again once the service update completes.

If auto-pausing is enabled, but a database does not auto-pause after the delay period, and the features listed above are not used, the application or user sessions may be preventing auto-pausing. To see if there are any application or user sessions currently connected to the database, connect to the database using any client tool, and execute the following query:

SELECT session_id,
       host_name,
       program_name,
       client_interface_name,
       login_name,
       status,
       login_time,
       last_request_start_time,
       last_request_end_time
FROM sys.dm_exec_sessions AS s
INNER JOIN sys.dm_resource_governor_workload_groups AS wg
ON s.group_id = wg.group_id
WHERE s.session_id <> @@SPID
      AND
      (
      (
      wg.name like 'UserPrimaryGroup.DB%'
      AND
      TRY_CAST(RIGHT(wg.name, LEN(wg.name) - LEN('UserPrimaryGroup.DB') - 2) AS int) = DB_ID()
      )
      OR
      wg.name = 'DACGroup'
      );

After running the query, make sure to disconnect from the database. Otherwise, the open session used by the query will prevent auto-pausing.

If the result set is non-empty, it indicates that there are sessions currently preventing auto-pausing.

If the result set is empty, it is still possible that sessions were open, possibly for a short time, at some point earlier during the auto-pause delay period. To see if such activity has occurred during the delay period, you can use Azure SQL Auditing and examine audit data for the relevant period.

Reference: https://learn.microsoft.com/en-us/azure/azure-sql/database/serverless-tier-overview?view=azuresql&tabs=general-purpose

GeethaThatipatri-MSFT 29,542 Reputation points Microsoft Employee Moderator

2023-11-01T14:32:00.72+00:00

@Dylan Miles Greetings, Looks like you have done a thorough investigation of the issue, for further investigation I would suggest raising a support case.

Regards

Geetha
Andy Felton 0 Reputation points

2023-11-01T18:11:37.7766667+00:00

@Dylan Miles I'm seeing exactly the issue with one of my DBs created yesterday - if you get anywhere would be great to know what the issue was.

Thanks

Andy
GK87 191 Reputation points

2024-01-25T10:57:13.2+00:00

@Dylan Miles I was doing the exact same investigation as you since I'm seeing the same issue with a serverless database utilizing some SQL Instance CPU and the same type of queries every hour during some hours of the day. This prevents pausing from happening it seems. That's how I ended up here. Did you find a solution/cause to this?
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2024-01-25T22:00:50.58+00:00

What about checking Query Store for the intervals where you expect the server to be stopped? That can show you which queries that has been executed in that interval.

Share via

Azure SQL Serverless DB

1 answer

Your answer