Azure SQL Database
An Azure relational database service.
6,176 questions
Azure Hyperscale Premium Tier getting disconnected and queries moving to suspended state.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(265.6, 36%, 35%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EM%3C/text%3E%3C/svg%3E)
Minkita
0
Reputation points
We are currently using Azure SQL Database – Hyperscale tier, which holds around 5 TB of data and growing. This data was migrated from SAP S/4HANA and includes several large tables used by business users for analytics and reporting.
However, we're facing serious issues:
Business users write unoptimized queries, often resulting in Cartesian products.
This leads to query suspensions, and in some cases, even database disconnections.
We have no control over the query logic or the ability to enforce best practices on how queries are written.
Given that Azure SQL Hyperscale is a premium and scalable solution, we had expected it to handle heavy workloads gracefully, including badly written queries to some extent. But that hasn’t been the case in our experience.
- Why is Azure SQL Hyperscale not able to handle such scenarios without crashing or suspending queries
- Are there any native features in Hyperscale (like query limits, guards, or throttles) that can protect the database from expensive or harmful queries?
- What are the recommended practices in such environments where query logic cannot be directly controlled?
{count} votes
-
Amira Bedhiafi 31,391 Reputation points2025-04-16T11:50:16.84+00:00 Hello Minkita !
Thank you for posting on Microsoft Learn.
You're raising a very real and common concern when it comes to shared or uncontrolled analytics access on a large Azure SQL Hyperscale database. Even though Hyperscale is designed for high-volume workloads, it is not immune to performance degradation or query suspension when badly written or expensive queries run uncontrolled especially in a multi-user, ad-hoc query environment.
Even though Azure SQL Hyperscale provides:
- Independent compute and storage layers
- Scale-out read replicas
- Fast log processing and high IOPS
it still has resource governance limits per database, especially in terms of:
- CPU
- Memory
- Worker threads
- TempDB usage
When users write cartesian joins, forget filters, or request millions of rows in ad-hoc fashion, Hyperscale will attempt to run the query until it hits a soft or hard resource threshold, at which point:
- The query may be suspended
- The session might be killed
- Other sessions might be impacted
- In extreme cases, the node might need a failover/restart
Unfortunately, Resource Governor is not available in Azure SQL Database (only SQL Server). But Hyperscale does offer:
Automatic resource throttling
CPU/memory caps per session
Session limits on worker threads or tempdb usage
These are not customizable but are part of built-in workload isolation.
You can use Query Store + Intelligent Insights to capture query performance metrics over time and you can identify frequent bad queries, regressions...
You can also use Query Store hints (recently added) to force better plans or limit behavior
Automatic tuning can force last-known good plans (helps in some bad cases) but it doesn’t solve badly designed logic (like missing joins)
-
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(35.2, 49%, 13%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EVK%3C/text%3E%3C/svg%3E)
Vijayalaxmi Kattimani 2,225 Reputation points Microsoft External Staff2025-04-16T13:04:51.3+00:00 Hi Minkita,
Greetings!
In addition to the response provided by @Amira Bedhiafi, I would like to add the following information.
We would like to inform you that, Azure SQL Database Hyperscale is designed to handle large-scale databases with high performance and scalability. However, unoptimized queries, especially those resulting in Cartesian products, can significantly impact performance and lead to issues such as query suspensions and database disconnections.
Here are some insights and recommendations based on your concerns:
Why Azure SQL Hyperscale May Struggle with Unoptimized Queries:
Azure SQL Hyperscale provides rapid scalability and high performance, but it is not immune to the effects of poorly written queries. Unoptimized queries can lead to excessive resource consumption, causing the database to throttle or suspend operations to maintain stability. This is particularly true for queries that result in Cartesian products, which can generate a large number of rows and consume significant CPU and memory resources.
Native Features in Hyperscale to Protect Against Harmful Queries:
Azure SQL Hyperscale includes several features that can help manage and mitigate the impact of expensive or harmful queries:
- Log Rate Governance: Hyperscale manages log generation rate via log rate governance, which helps maintain recoverability SLAs by limiting the log generation rate.
- Resource Governance: Hyperscale uses resource governance mechanisms to manage CPU and memory usage, which can help prevent excessive resource consumption by unoptimized queries.
- Dynamic Management Views (DMVs): DMVs such as sys.dm_exec_requests can be used to monitor the number of outstanding requests and identify queries that are consuming excessive resources.
Recommended Practices for Environments with Uncontrolled Query Logic:
In environments where you cannot directly control query logic, consider the following best practices to mitigate the impact of unoptimized queries:
- Query Optimization: Encourage business users to optimize their queries by providing training and resources on best practices for SQL query writing. Tools like SQL Server Management Studio (SSMS) can help analyse and optimize queries.
- Indexing: Ensure that your database is properly indexed to improve query performance. Regularly review and update indexes based on query patterns.
- Resource Limits: Implement resource limits and throttling to prevent individual queries from consuming excessive resources. This can be done using resource governance features in Azure SQL Hyperscale.
- Monitoring and Alerts: Set up monitoring and alerts to identify and respond to performance issues quickly. Use DMVs and other monitoring tools to track query performance and resource usage.
- Partitioning: Consider partitioning large tables to improve query performance and reduce the impact of unoptimized queries.
We would request you to refer the below mentioned links for more information.
https://stackoverflow.com/questions/11436892/detect-if-sql-azure-is-throttling
I hope this information helps. Please do let us know if you have any further queries.
-
Vijayalaxmi Kattimani 2,225 Reputation points Microsoft External Staff
2025-04-17T08:41:03.5166667+00:00 Hi Minkita,
We haven’t heard from you on the last response and wanted to follow up to check if your issue has been resolved.
If you have found a solution, we would appreciate it if you could share it with the community, as it may be helpful to others. Otherwise, please provide more details, and we will do our best to assist you further.
-
Minkita 0 Reputation points
2025-04-17T09:47:19.95+00:00 Hi Vijayalaxami,
Can you tell me more about Resource Limit?
-
Vijayalaxmi Kattimani 2,225 Reputation points Microsoft External Staff
2025-04-17T11:04:06.1266667+00:00 Hi Minkita,
Sure, I will provide a clear explanation regarding the resource limits.
Implement resource limits to prevent individual queries from consuming excessive resources. This can be done using resource governance features in Azure SQL Hyperscale.
We would like to inform you that, Resource governance enforces limits on various resources to ensure that no single query or workload can monopolize the system. Resource governance in Azure SQL Database, including the Hyperscale tier, is a set of mechanisms designed to manage and allocate resources effectively to ensure balanced and predictable performance.
When you choose a specific Azure SQL Database service tier, you are selecting a pre-defined set of allocated resources across several dimensions such as CPU, storage type, storage limit, memory, and more. Each service tier comes with inherent resource usage boundaries or limits
For example, there are limits on log rate, memory usage, and the number of concurrent workers.
Azure SQL Database uses a resource governance implementation based on SQL Server Resource Governor, modified and extended to run in the cloud. This helps enforce resource limits and manage resource allocation effectively. Resource governance mechanisms include admission control, which manages query execution and resource allocation, and log rate governance, which limits the rate at which transaction logs are generated.
We request you to refer the below mentioned links for more information.
https://azure.microsoft.com/en-us/blog/resource-governance-in-azure-sql-database/
I hope this information helps. Please do let us know if you have any further queries.
-
Minkita 0 Reputation points
2025-04-17T11:29:13.9066667+00:00 I have tried using Resource Governor, but it seems it is not supported for Azure SQL hyperscale
-
Minkita 0 Reputation points
2025-04-17T11:29:56.3966667+00:00 Same has been mentioned by Amira Bedhiafi
-
Vijayalaxmi Kattimani 2,225 Reputation points Microsoft External Staff
2025-04-17T11:50:06.51+00:00 Hi Minkita,
As per the response provided by @Amira Bedhiafi,
Unfortunately, Resource Governor is not available in Azure SQL Database (only SQL Server). But Hyperscale does offer:
The Resource Governor feature is available in Azure SQL Database Hyperscale; however, it is not supported in SQL Server.
The Hyperscale service tier in Azure SQL Database provides the following additional capabilities:
- Rapid scale up - you can, in constant time, scale up your compute resources to accommodate heavy workloads when needed, and then scale the compute resources back down when not needed.
- Rapid scale out - you can provision one or more read-only replicas for offloading your read workload and for use as hot-standbys.
- Automatic scale-up, scale-down, and billing for compute based on usage with serverless compute.
- Optimized price/performance for a group of Hyperscale databases with varying resource demands with elastic pools.
- Autoscaling storage with support for up to 128 TB of database or 100 TB elastic pool size.
- Higher overall performance due to higher transaction log throughput and faster transaction commit times regardless of data volumes.
- Fast database backups (based on file snapshots) regardless of size with no I/O impact on compute resources.
- Fast database restores or copies (based on file snapshots) in minutes rather than hours or days.
I would request you to refer the provided links for more information.
-
Vijayalaxmi Kattimani 2,225 Reputation points Microsoft External Staff
2025-04-18T09:16:06.9433333+00:00 Hi Minkita,
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Sign in to comment
Sign in to answer