When More vCores Aren’t the Fix: Diagnosing Azure SQL CPU and Compilation Storms
Before changing MAXDOP or adding compute, answer one question: Is the database executing work, compiling work, or waiting for work? When an Azure SQL Database approaches its CPU or worker limits, the natural reaction is often straightforward: Add more vCores. Move to a higher service objective. Change MAXDOP. Find the most expensive query. Add an index. Sometimes one of those actions is exactly what the workload needs. But not always. A database experiencing very high CPU does not necessarily have an execution CPU problem. It might instead be spending substantial resources: Repeatedly compiling queries. Generating plans that aren't efficiently reused. Processing excessive application concurrency. Holding workers while requests wait. Handling large numbers of inexpensive but extremely frequent queries. Recompiling statements because workload characteristics keep changing. Adding compute can make these symptoms less visible without making the workload healthier. The more useful question is: Where is the resource pressure actually coming from? In this article, we'll build a practical troubleshooting workflow to distinguish: Execution CPU. Compilation and recompilation pressure. Worker saturation. Blocking and concurrency. Plan instability. Parameterization problems. I/O and transaction-log pressure. Genuine capacity constraints. The goal isn't to avoid scaling. The goal is to understand the workload before deciding that scaling is the solution. 1. Start With the Symptom, Not the Conclusion Imagine an incident where you observe: CPU approaching the database's resource limit. Worker utilization rising rapidly. Application latency increasing. More active sessions than usual. Intermittent application timeouts. No obvious increase in legitimate business volume. It's tempting to conclude: "We don't have enough CPU or workers." That conclusion is premature. High worker utilization tells us that many workers are being consumed. It doesn't explain why. The underlying cause might include: Expensive queries actively consuming CPU. Large numbers of concurrent requests. Parallel query execution. Blocking. Long-running requests. Query compilation or recompilation. Excessive application calls. Several of these conditions happening simultaneously. The first step is therefore to correlate Azure SQL's primary database resource measurements. 2. Establish the Resource Timeline For recent diagnosis, sys.dm_db_resource_stats provides resource utilization samples for the current database. Run this inside the Azure SQL Database you're investigating: SELECT TOP (240) end_time, avg_cpu_percent, avg_data_io_percent, avg_log_write_percent, avg_memory_usage_percent, max_worker_percent, max_session_percent FROM sys.dm_db_resource_stats ORDER BY end_time DESC; This gives us several important signals: avg_cpu_percent CPU utilization relative to the database resource limit. avg_data_io_percent Data I/O utilization. avg_log_write_percent Transaction log write utilization. avg_memory_usage_percent Memory usage. max_worker_percent Worker utilization. max_session_percent Session utilization. sys.dm_db_resource_stats returns approximately one hour of resource data at roughly 15-second intervals for Azure SQL Database. A useful summary is: SELECT MIN(end_time) AS sample_start_utc, MAX(end_time) AS sample_end_utc, CAST(AVG(avg_cpu_percent) AS decimal(10,2)) AS avg_cpu_percent, CAST(MAX(avg_cpu_percent) AS decimal(10,2)) AS max_cpu_percent, CAST(MAX(max_worker_percent) AS decimal(10,2)) AS max_worker_percent, CAST(MAX(max_session_percent) AS decimal(10,2)) AS max_session_percent, CAST(MAX(avg_data_io_percent) AS decimal(10,2)) AS max_data_io_percent, CAST(MAX(avg_log_write_percent) AS decimal(10,2)) AS max_log_write_percent, CAST(MAX(avg_memory_usage_percent) AS decimal(10,2)) AS max_memory_usage_percent FROM sys.dm_db_resource_stats; Look for relationships between: CPU and workers. Workers and sessions. CPU and data I/O. CPU and log writes. Memory and concurrency. When the spike begins. How long it persists. How quickly the database returns to normal. Important: sys.dm_db_resource_stats provides recent, high-resolution resource statistics. For longer historical analysis, Azure SQL also exposes sys.resource_stats from the logical server's master database, with five-minute aggregation and longer retention. 3. First Classification: Executing, Compiling, or Waiting? Before tuning anything, classify the workload. Scenario A: The database is executing expensive work Typical evidence includes: High CPU. A small set of queries dominates total CPU. High logical reads. Large joins or aggregations. Expensive scans. Parallel query execution. Execution plans that explain the consumption. In this case, compilation may be perfectly normal. The database is simply performing expensive execution work. Scenario B: The workload may have excessive compilation Typical evidence might include: Many similar query shapes. Large numbers of distinct query texts. High compile counts. Frequent recompilation. Poor parameterization. Very frequent short-running queries. Temporary-object-heavy procedures. CPU usage not fully explained by the top executing queries. None of these signals alone proves that a compilation storm exists. The key is correlation. Scenario C: Workers are waiting rather than computing Typical evidence includes: High worker utilization. CPU that isn't proportionally high. Significant blocking. Lock waits. Long-running transactions. Many concurrent requests. Requests holding workers while waiting for resources. The remedy here can be completely different from an execution CPU problem. 4. Build a Minimum Evidence Packet Before making configuration changes, capture enough evidence to explain the incident. At minimum, collect: Exact incident window in UTC. CPU utilization. Worker utilization. Session utilization. Data I/O. Log-write utilization. Memory utilization. Top queries by CPU. Top queries by duration. Execution counts. Query Store wait categories. Number of relevant Query Store plans. Compile counts. Application deployment timestamps. Scheduled workload timestamps. Blocking information, if the incident is currently happening. Query Store is particularly useful because it provides historical information about: Query text. Execution plans. Runtime statistics. Execution counts. Query performance over time. Query Store wait categories. 5. Find the Queries Consuming the Most CPU Don't rely only on whatever happens to be executing at the moment you connect. Query Store can help determine which queries consumed CPU during the period under investigation. The following example examines approximately the last hour. DECLARE @StartTime datetimeoffset = DATEADD(HOUR, -1, SYSUTCDATETIME()); WITH RuntimeStats AS ( SELECT rs.plan_id, rs.runtime_stats_interval_id, rs.execution_type, SUM(rs.count_executions) AS execution_count, SUM ( CONVERT(decimal(38,4), rs.avg_cpu_time) * rs.count_executions ) AS total_cpu_us FROM sys.query_store_runtime_stats AS rs GROUP BY rs.plan_id, rs.runtime_stats_interval_id, rs.execution_type ) SELECT TOP (20) q.query_id, qt.query_sql_text, SUM(r.execution_count) AS executions, CAST ( SUM(r.total_cpu_us) / NULLIF(SUM(r.execution_count), 0) AS decimal(20,2) ) AS weighted_avg_cpu_us, CAST ( SUM(r.total_cpu_us) AS decimal(38,2) ) AS total_cpu_us FROM RuntimeStats AS r JOIN sys.query_store_runtime_stats_interval AS rsi ON r.runtime_stats_interval_id = rsi.runtime_stats_interval_id JOIN sys.query_store_plan AS p ON r.plan_id = p.plan_id JOIN sys.query_store_query AS q ON p.query_id = q.query_id JOIN sys.query_store_query_text AS qt ON q.query_text_id = qt.query_text_id WHERE r.execution_type = 0 AND rsi.end_time > @StartTime GROUP BY q.query_id, qt.query_sql_text ORDER BY total_cpu_us DESC; A few important details: Query Store CPU statistics are reported in microseconds. The query pre-aggregates Query Store runtime rows. execution_type = 0 restricts the analysis to normally completed executions. The average CPU calculation is weighted by execution count. Now ask the critical question: Do the highest-CPU queries explain the database-level CPU spike? If they do, investigate: Execution plans. Logical reads. Indexing. Cardinality estimates. Join strategies. Parallelism. Parameter sensitivity. If they don't, widen the investigation. Compilation, concurrency, waits, application request volume, or another engine activity may deserve more attention. 6. Don't Ignore Execution Frequency A query doesn't need to be individually expensive to create substantial pressure. Imagine: Query A consumes 500 ms of CPU and runs 20 times. Query B consumes 2 ms of CPU but runs 200,000 times. Query B may represent far more total work. Use Query Store to identify high-frequency queries: DECLARE @StartTime datetimeoffset = DATEADD(HOUR, -1, SYSUTCDATETIME()); WITH RuntimeStats AS ( SELECT rs.plan_id, rs.runtime_stats_interval_id, rs.execution_type, SUM(rs.count_executions) AS execution_count, SUM ( CONVERT(decimal(38,4), rs.avg_cpu_time) * rs.count_executions ) AS total_cpu_us FROM sys.query_store_runtime_stats AS rs GROUP BY rs.plan_id, rs.runtime_stats_interval_id, rs.execution_type ) SELECT TOP (20) q.query_id, qt.query_sql_text, SUM(r.execution_count) AS executions, CAST ( SUM(r.total_cpu_us) AS decimal(38,2) ) AS total_cpu_us FROM RuntimeStats AS r JOIN sys.query_store_runtime_stats_interval AS rsi ON r.runtime_stats_interval_id = rsi.runtime_stats_interval_id JOIN sys.query_store_plan AS p ON r.plan_id = p.plan_id JOIN sys.query_store_query AS q ON p.query_id = q.query_id JOIN sys.query_store_query_text AS qt ON q.query_text_id = qt.query_text_id WHERE r.execution_type = 0 AND rsi.end_time > @StartTime GROUP BY q.query_id, qt.query_sql_text ORDER BY executions DESC; This becomes particularly important when diagnosing compilation problems because a high-frequency workload can amplify a relatively small inefficiency thousands of times per second. 7. What Does a Compilation Storm Look Like? A compilation storm usually isn't one expensive compilation. It's a workload pattern. Signals worth investigating include: Large numbers of nearly identical queries. Literal values embedded into otherwise identical SQL statements. Poor plan reuse. Elevated compile counts. Rapid plan generation. Temporary-object-heavy execution. Dynamically generated SQL where query text changes unnecessarily. Application parameter types that don't align with SQL types. For example, an application might continuously submit: SELECT OrderID, OrderDate FROM dbo.Orders WHERE CustomerID = 1001; Then: SELECT OrderID, OrderDate FROM dbo.Orders WHERE CustomerID = 1002; Then: SELECT OrderID, OrderDate FROM dbo.Orders WHERE CustomerID = 1003; Compare that with an appropriately parameterized application call: DECLARE @CustomerID int = 1001; EXEC sys.sp_executesql N' SELECT OrderID, OrderDate FROM dbo.Orders WHERE CustomerID = @CustomerID; ', N'@CustomerID int', @CustomerID = @CustomerID; Parameterized SQL can make plan reuse possible when the workload characteristics make reuse appropriate. But there's an important caveat: Parameterization itself is not universally better. Highly skewed data distributions can produce different optimal plans for different parameter values. The target isn't: "Reuse every plan." It's: Avoid unnecessary compilation while preserving appropriate plan choices. 8. Look for Plan Churn and Compilation Activity Query Store can help identify queries associated with multiple retained plans and elevated compile activity. SELECT TOP (50) q.query_id, COUNT(DISTINCT p.plan_id) AS plan_count, COUNT(DISTINCT p.query_plan_hash) AS distinct_plan_shapes, q.count_compiles, q.initial_compile_start_time, q.last_compile_start_time, CAST ( q.avg_compile_duration AS decimal(20,2) ) AS avg_compile_duration_us, q.last_compile_duration AS last_compile_duration_us, CAST ( q.avg_optimize_cpu_time AS decimal(20,2) ) AS avg_optimize_cpu_time_us FROM sys.query_store_query AS q JOIN sys.query_store_plan AS p ON q.query_id = p.query_id GROUP BY q.query_id, q.count_compiles, q.initial_compile_start_time, q.last_compile_start_time, q.avg_compile_duration, q.last_compile_duration, q.avg_optimize_cpu_time HAVING COUNT(DISTINCT p.plan_id) > 1 ORDER BY plan_count DESC, q.count_compiles DESC; This distinction is important: Multiple Query Store plans do not automatically mean harmful plan churn. Query Store intentionally maintains query and plan history. Multiple plans may be perfectly legitimate because of: Plan changes over time. Statistics changes. Index changes. Parameter-sensitive behavior. Database changes. Query Store history. Intentional plan management. The signal becomes more interesting when multiple plans correlate with: High compile counts. Compilation during the incident. Rising CPU. Large latency variation. Performance regression. Also compare: COUNT(DISTINCT p.plan_id) with: COUNT(DISTINCT p.query_plan_hash) Several retained plan records don't necessarily mean several materially different plan shapes. 9. Look for Queries With Large Duration Variation Average latency can hide an important problem. A query averaging 30 ms might look harmless until some executions take several seconds. Use Query Store to identify significant variation: DECLARE @StartTime datetimeoffset = DATEADD(HOUR, -1, SYSUTCDATETIME()); WITH RuntimeStats AS ( SELECT rs.plan_id, rs.runtime_stats_interval_id, rs.execution_type, SUM(rs.count_executions) AS execution_count, SUM ( CONVERT(decimal(38,4), rs.avg_duration) * rs.count_executions ) AS total_duration_us, MAX(rs.max_duration) AS max_duration_us FROM sys.query_store_runtime_stats AS rs GROUP BY rs.plan_id, rs.runtime_stats_interval_id, rs.execution_type ) SELECT TOP (30) q.query_id, COUNT(DISTINCT p.plan_id) AS plan_count, SUM(r.execution_count) AS execution_count, CAST ( SUM(r.total_duration_us) / NULLIF(SUM(r.execution_count), 0) AS decimal(20,2) ) AS weighted_avg_duration_us, MAX(r.max_duration_us) AS max_duration_us, CAST ( MAX(r.max_duration_us) / NULLIF ( SUM(r.total_duration_us) / NULLIF(SUM(r.execution_count), 0), 0 ) AS decimal(20,2) ) AS max_to_average_ratio FROM RuntimeStats AS r JOIN sys.query_store_runtime_stats_interval AS rsi ON r.runtime_stats_interval_id = rsi.runtime_stats_interval_id JOIN sys.query_store_plan AS p ON r.plan_id = p.plan_id JOIN sys.query_store_query AS q ON p.query_id = q.query_id WHERE r.execution_type = 0 AND rsi.end_time > @StartTime GROUP BY q.query_id ORDER BY max_to_average_ratio DESC; Large variation can justify investigating: Parameter-sensitive behavior. Blocking. Different execution plans. Data skew. Changing memory requirements. Concurrent workload effects. It doesn't, by itself, tell us which one is responsible. 10. Use Query Store Wait Categories Correctly Wait information provides another dimension. Query Store can associate wait categories with query plans and runtime intervals. That's an important distinction. Query Store isn't intended to provide a historical record of every individual SQL Server wait type. The following query summarizes Query Store waits for approximately the last hour: DECLARE @StartTime datetimeoffset = DATEADD(HOUR, -1, SYSUTCDATETIME()); WITH WaitStats AS ( SELECT ws.plan_id, ws.runtime_stats_interval_id, ws.execution_type, ws.wait_category, ws.wait_category_desc, SUM(ws.total_query_wait_time_ms) AS total_wait_ms FROM sys.query_store_wait_stats AS ws GROUP BY ws.plan_id, ws.runtime_stats_interval_id, ws.execution_type, ws.wait_category, ws.wait_category_desc ) SELECT TOP (30) w.wait_category_desc, SUM(w.total_wait_ms) AS total_wait_ms FROM WaitStats AS w JOIN sys.query_store_runtime_stats_interval AS rsi ON w.runtime_stats_interval_id = rsi.runtime_stats_interval_id WHERE w.execution_type = 0 AND rsi.end_time > @StartTime GROUP BY w.wait_category_desc ORDER BY total_wait_ms DESC; Look for categories associated with: Locking. CPU. Parallelism. Buffer I/O. Transaction log activity. Memory. Other resource pressure. Use these categories to determine where the next investigation should go, not as standalone proof of root cause. 11. Capture Exact Wait Types During an Active Incident When the issue is occurring now, live DMVs can provide more specific evidence. For example: SELECT wt.session_id, wt.exec_context_id, wt.wait_type, wt.wait_duration_ms, wt.blocking_session_id, r.status, r.command, r.cpu_time, r.total_elapsed_time, r.logical_reads, r.reads, r.writes, wt.resource_description FROM sys.dm_os_waiting_tasks AS wt LEFT JOIN sys.dm_exec_requests AS r ON wt.session_id = r.session_id WHERE wt.session_id <> @@SPID ORDER BY wt.wait_duration_ms DESC; This creates an important analytical distinction. Query Store Use it for: Historical query behavior. Historical plan behavior. Runtime statistics. Historical wait categories. Live DMVs Use them for: Specific waits occurring now. Current requests. Current blocking. Current resource use. For example, individual waits such as: RESOURCE_SEMAPHORE_QUERY_COMPILE or: SOS_SCHEDULER_YIELD need to be interpreted alongside the rest of the evidence. A compile-related wait doesn't automatically prove that compilation CPU caused the incident. Likewise, a scheduler-related wait doesn't automatically prove that MAXDOP is wrong. 12. Don't Ignore Data-Type Mismatches Another frequently overlooked issue is inconsistent data types between the application and database. Imagine a column defined as: CustomerCode varchar(30) while the application sends: @CustomerCode nvarchar(30) Or perhaps the column is: CustomerID int while the application uses: bigint Depending on the particular expression and conversion direction, an implicit conversion may affect: Index access methods. Cardinality estimation. Plan selection. Reads. CPU consumption. As a general principle: Application parameter definitions should align with the corresponding database column types wherever practical. Compare: Data type. Length. Precision. Scale. Unicode versus non-Unicode. Collation when relevant. Don't reach for a hint when the underlying problem is an application/database contract mismatch. 13. Temporary Tables Require Evidence, Not Assumptions Temporary tables are useful and common SQL Server constructs. They're not inherently a performance problem. However, some workloads repeatedly: Create temporary tables. Populate them. Modify them. Create or drop temporary indexes. Execute queries heavily dependent on temporary-table cardinality. Those patterns can affect optimization and recompilation behavior. The important troubleshooting principle is: Don't rewrite temporary-table logic just because compilation appears high. First demonstrate the relationship. Correlate: Compilation timing. Procedure executions. Query Store history. CPU peaks. Plan variation. Application workload cycles. Then decide whether temporary-table behavior actually requires modification. 14. OPTION (RECOMPILE) Is Not a Universal Compilation Fix This hint deserves particular care: SELECT ... FROM ... WHERE ... OPTION (RECOMPILE); OPTION (RECOMPILE) instructs SQL Server to compile a new temporary plan for that execution rather than reuse the normal cached plan for the statement. That can be very useful for certain parameter-sensitive workloads. For example: A query behaves very differently for different parameter populations. Reusing one cached plan consistently produces poor performance. Compilation cost is small compared with savings in execution cost. But consider the problem we're investigating. If the workload already suffers from excessive compilation, broadly introducing OPTION (RECOMPILE) may increase compilation work. Treat it as: A targeted tool. A workload-specific optimization. A controlled experiment. A change requiring before-and-after measurement. Not as: A universal response to high CPU. A default fix for plan instability. A general cure for compilation storms. 15. MAXDOP Is Not a Universal CPU Fix Either Suppose CPU and worker utilization are both elevated. Should we immediately lower MAXDOP? Not necessarily. MAXDOP controls the maximum degree of parallelism available to a parallel query plan. It doesn't directly fix: Excessive compilation. Poor parameterization. Blocking. Inefficient serial queries. Excessive application concurrency. Data-type mismatches. Poor indexes. Check the database-scoped setting: SELECT name, value, value_for_secondary FROM sys.database_scoped_configurations WHERE name = N'MAXDOP'; Then determine whether parallelism is genuinely implicated. Ask: Are the CPU-dominant queries running parallel plans? Are parallel requests consuming a meaningful share of workers? Does the workload benefit from parallelism? Would reducing parallelism decrease worker pressure but increase latency? Is concurrency the real problem instead? The correct reasoning is not: High workers = reduce MAXDOP. It is: Determine whether intra-query parallelism is materially contributing to the observed resource pressure. Also remember that database-scoped configuration changes can result in plan invalidation and subsequent recompilation. Configuration experiments should therefore be controlled and monitored. 16. Be Careful With Forced Plans Query Store allows you to force a selected plan. That's an extremely useful capability. It's also easy to misuse. A plan that was historically fast isn't automatically safe for: Every parameter value. Every data distribution. Every concurrency level. Every future workload. Before forcing a plan, evaluate: Parameter sensitivity. Data distribution. Query execution frequency. CPU. Duration. Logical reads. Memory use. Concurrency. Performance across representative parameter values. If the evidence supports a controlled test: EXEC sys.sp_query_store_force_plan @query_id = 123, plan_id = 456; Monitor forcing status: SELECT plan_id, query_id, is_forced_plan, force_failure_count, last_force_failure_reason, last_force_failure_reason_desc FROM sys.query_store_plan WHERE query_id = 123; And have a rollback path: EXEC sys.sp_query_store_unforce_plan @query_id = 123, plan_id = 456; Replace the sample IDs with the Query Store identifiers from your own database. Most importantly: Plan forcing attempts to reproduce the selected plan. It shouldn't be treated as a guarantee that future execution will always behave exactly like the historical execution you're trying to reproduce. Treat plan forcing as a controlled intervention rather than an automatic response to plan variation. 17. A Practical Remediation Ladder Once you've identified the likely source of resource consumption, move from structural corrections toward infrastructure scaling. Step 1: Correct data-type mismatches Align: Application parameters. Database columns. Stored procedure parameters. Dynamic SQL parameter definitions. Step 2: Reduce unnecessary query-shape variation Use appropriate parameterization where the workload benefits from plan reuse. Avoid generating thousands of logically identical query strings simply because literal values change. Step 3: Investigate high-frequency workload patterns Determine whether the problem is a single expensive query or thousands of inexpensive queries executed continuously. Step 4: Investigate temporary-table behavior Only redesign temporary-table logic when evidence links it to: Recompilation. Poor cardinality estimates. Plan instability. Excessive CPU. Step 5: Control burst concurrency If large numbers of requests arrive simultaneously, consider: Application-side throttling. Batching. Queuing. Staggering jobs. Reducing redundant requests. Controlling parallel application calls. Step 6: Optimize genuinely expensive execution Investigate: Index design. Logical reads. Access methods. Cardinality estimates. Join strategies. Memory grants. Parameter-sensitive behavior. Parallel execution. Step 7: Use targeted plan interventions Where supported by evidence, consider: Query Store plan forcing. Query Store hints. Carefully tested query hints. Always define: What behavior you're trying to change. What success looks like. How long you'll observe it. What triggers rollback. Step 8: Scale when the workload genuinely requires it Scaling is completely reasonable when: Workload optimization has been assessed. Business growth has increased concurrency. CPU demand represents useful execution work. Latency requirements justify additional compute. Scaling is the lowest-risk mitigation for an active business impact. The optimized workload legitimately requires more capacity. The important distinction is: Scaling should solve a capacity problem, not simply hide an unexplained workload problem. 18. Scale or Fix? Use the following matrix as a starting point. Observation Likely investigation First response High CPU + a few queries dominate CPU Execution pressure Investigate plans, reads, indexes, and query design High CPU + many similar query texts Parameterization/compilation Investigate query generation and plan reuse High workers + moderate CPU + blocking Waiting/concurrency Investigate blockers and transaction duration High CPU + high Data I/O Data-access pressure Investigate scans, logical/physical reads, and indexes High CPU + large plan variation Plan instability Correlate Query Store plans with performance periods Sudden spike after application release Workload change Compare deployment timing with query behavior Predictable scheduled spike Burst workload Tune, stagger, batch, or provide appropriate capacity Optimized workload + sustained legitimate CPU demand Capacity Consider scaling Scaling helps temporarily but the pattern repeatedly returns Workload or capacity issue still unexplained Revisit root-cause evidence This matrix is diagnostic, not an Azure SQL service guarantee. There is no universal CPU percentage or worker percentage at which every workload should automatically scale. 19. Validate the Fix A performance investigation isn't complete when CPU drops once. Validate the change against an equivalent workload cycle. Compare: CPU utilization. Worker utilization. Session utilization. Execution counts. Query CPU. Query duration. Query Store wait categories. Relevant plan counts. Application latency. Application timeouts. Start again with: SELECT end_time, avg_cpu_percent, max_worker_percent, max_session_percent, avg_data_io_percent, avg_log_write_percent, avg_memory_usage_percent FROM sys.dm_db_resource_stats ORDER BY end_time DESC; Whenever possible, compare equivalent periods: Same business workload. Same scheduled job. Similar request volume. Similar concurrency. Similar parameter distribution. Don't declare success simply because the workload disappeared. 20. Define Regression Criteria Before Deployment Before applying a tuning change, decide what constitutes failure. Possible rollback signals include: CPU becomes materially worse. P95 or P99 application latency regresses. Worker utilization rises. Timeouts increase. Logical reads rise materially. Another parameter population becomes slower. Query Store reveals a plan regression. These aren't universal thresholds. Define them according to: Your workload baseline. Application requirements. Business SLAs or SLOs. Normal workload variation. 21. Build an Escalation-Ready Evidence Package If further investigation or escalation is required, a clear evidence package dramatically reduces troubleshooting time. Include: Incident start and end in UTC. CPU timeline. Worker timeline. Session timeline. Memory timeline. Data I/O timeline. Log-write timeline. Sanitized Query Store identifiers. Relevant execution plans. Query Store wait categories. Live wait types captured during the incident. Compile counts. Query execution counts. Application deployment timeline. Scheduled workload information. Reproduction steps, when available. Separate your conclusions into two categories. Facts Examples: CPU increased during the incident window. Worker utilization increased at the same time. Query Store contained multiple plans. Compile counts were elevated. Execution count rose. Blocking was observed. Latency increased following a deployment. Hypotheses Examples: Compilation contributed materially to CPU utilization. Application-generated query variation reduced plan reuse. Temporary-table behavior contributed to recompilation. Parameter sensitivity contributed to latency variation. Increased concurrency amplified an existing query-performance problem. This distinction matters. Correlation is evidence. It isn't automatically root cause. 22. The Complete Diagnostic Decision Tree When Azure SQL CPU or worker utilization suddenly climbs, use this sequence. Question 1: Is CPU high? No Investigate: Workers. Blocking. Waits. Sessions. Long-running transactions. Concurrency. Yes Continue. Question 2: Do a small number of queries explain most execution CPU? Yes Investigate: Execution plans. Logical reads. Indexing. Cardinality estimates. Parallelism. Parameter sensitivity. No Broaden the investigation toward: Compilation. Query frequency. Concurrency. Workload shape. Question 3: Is there significant query-text or plan variation? Yes Investigate: Application parameterization. Dynamic SQL generation. Compile counts. Plan history. Parameter sensitivity. No Continue. Question 4: Are workers primarily waiting? Yes Investigate: Blocking. Locks. Long-running transactions. Current waits. Application concurrency. No Continue. Question 5: Are data I/O or log-write resources saturated? Yes Investigate those as potential independent bottlenecks. No Continue CPU-focused analysis. Question 6: Does compilation activity correlate with the CPU window? Yes Investigate: Query-shape variation. Parameterization. Recompilation. Application request patterns. Temporary-object behavior. No Return to: Execution CPU. Concurrency. Parallelism. Other engine work. Question 7: Does the workload still need additional compute after optimizations are assessed? Yes Scaling is appropriate. No Fix the workload behavior first. Final Takeaway The most important lesson is simple: High CPU describes a symptom. It doesn't identify the cause. The same applies to workers. High worker utilization doesn't necessarily mean Azure SQL Database needs more workers or more vCores. Before scaling or changing MAXDOP, determine whether the database is primarily: Executing expensive work. Executing inexpensive work far too frequently. Compiling excessive work. Waiting on other work. Handling excessive concurrency. Moving too much data. Generating unnecessary query variations or plans. The strongest diagnosis comes from correlating multiple sources of evidence: sys.dm_db_resource_stats. Query Store execution statistics. Query Store plan history. Query Store compilation information. Query Store wait categories. Live wait information. Execution plans. Application and deployment timelines. Sometimes the correct answer really is more compute. But if the underlying problem is unnecessary compilation, poor parameterization, blocking, uncontrolled concurrency, or inefficient queries, additional compute might simply give the problem more room to grow. Diagnose first. Optimize where the evidence points. Scale when the workload actually needs it. Further Reading For more information on the concepts and diagnostic techniques discussed in this article, refer to the following official Microsoft documentation: https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-resource-stats-azure-sql-database sys.resource_stats (Azure SQL Database) Monitor performance by using the Query Store Best practices for monitoring workloads with Query Store sys.query_store_query (Transact-SQL) sys.query_store_plan (Transact-SQL) sys.query_store_runtime_stats (Transact-SQL) sys.query_store_wait_stats (Transact-SQL) https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-os-waiting-tasks-transact-sql sp_query_store_force_plan (Transact-SQL) Configure the maximum degree of parallelism (MAXDOP) in Azure SQL Database https://learn.microsoft.com/en-us/sql/relational-databases/performance/query-store-hints Query Store hints best practicesLesson Learned #487: Identifying Parallel and High-Volume Queries in Azure SQL Database
Today, I worked on a service request that our customer needs to monitor and optimize queries that can significantly impact performance. This includes both queries running in parallel and those that, although executed with a single thread (MAXDOP 1), have a high volume of executions. In this article, we will explain a query that helps us identify these two types of queries in an Azure SQL Database.Lesson Learned #481: Query Performance Analysis Tips
When working with databases, high resource usage or a query reporting a timeout could indicate that the statistics of the tables involved in the query are not up to date, that we might be missing indexes, or that there are excessive blocking as the most common elements and possible causes of performance loss. For this reason, I would like to add in this article elements that can help us determine what might be happening with our query.
