--- title: "Stop Scaling Azure SQL: Find Real Performance Issues" description: "Use Azure's built-in diagnostics to find real performance bottlenecks in your Azure SQL Database instead of scaling up immediately." canonical: "https://adamtheautomator.com/azure-sql-performance-tuning/" --- # Stop Scaling Azure SQL: Find Real Performance Issues > Use Azure's built-in diagnostics to find real performance bottlenecks in your Azure SQL Database instead of scaling up immediately. Source: https://adamtheautomator.com/azure-sql-performance-tuning/ --- ATA Learning Tap to hide [ ATA Learning ](/) * [Home](/) * [Tutorials](/tutorials/) * [Instructors](/author/) * [Advertising](/advertising/) * [Recommended Resources](/resources/) * [About Adam](/about-adam/) Search for: * [](https://twitter.com/adbertram) * [](https://github.com/Adam-the-Automator) * [](https://www.linkedin.com/company/adam-the-automator-llc) * [](/feed/) ![Stop Scaling Azure SQL: Find Real Performance Issues](https://adamtheautomator.com/wp-content/uploads/publisher/3075d9c85b2b811696d7c3fb28215d5b/2632cbcaed949b31f4a9b4c4ea73d850076a5034cf55e309a5815dd451ab0388.webp) # Stop Scaling Azure SQL: Find Real Performance Issues [![](https://secure.gravatar.com/avatar/d0b9d42e21e5622713f8b693aa5c0f9244d5f7dd200ed29b8398f52dee5de337?s=192&d=mm&r=g)Adam Bertram](https://adamtheautomator.com/author/adam-bertram/)3 September 202610 min. read Categories: [Cloud](/category/cloud/) Tags:[Azure SQL Database](/tag/azure-sql-database/)[Databases](/tag/databases/)[DevOps](/tag/devops/)[Azure](/tag/azure/) Table of Contents * [What’s Throttling Your Azure SQL Database](#whats-throttling-your-azure-sql-database) * [Finding the Expensive Queries with Query Performance Insight](#finding-the-expensive-queries-with-query-performance-insight) * [Reading the Top Queries Chart](#reading-the-top-queries-chart) * [Why Plans Regress, and How Automatic Correction Fixes It](#why-plans-regress-and-how-automatic-correction-fixes-it) * [Finding and Fixing Missing Indexes Without Guessing](#finding-and-fixing-missing-indexes-without-guessing) * [The DMV Query That Surfaces Missing Indexes](#the-dmv-query-that-surfaces-missing-indexes) * [Letting Azure Tune Itself: Automatic Index Management and Its Safety Rails](#letting-azure-tune-itself-automatic-index-management-and-its-safety-rails) * [Safety Rails for Automatic Changes](#safety-rails-for-automatic-changes) * [SQL Performance Tuning by Symptom: CPU, IO, Blocking, and Chatty Connections](#sql-performance-tuning-by-symptom-cpu-io-blocking-and-chatty-connections) * [Query Anti-Patterns That Waste the Optimizer’s Work](#query-anti-patterns-that-waste-the-optimizers-work) * [Three Checks Ahead of a Resize](#three-checks-ahead-of-a-resize) * [Watching an Entire Estate with Database Watcher](#watching-an-entire-estate-with-database-watcher) * [The Four-Step Setup Flow](#the-four-step-setup-flow) * [Tune the Query Before You Tune the Invoice](#tune-the-query-before-you-tune-the-invoice) Don’t reach for a bigger compute tier the next time a query crawls. Raising the DTUs or adding vCores is the reflex, skipping the SQL performance tuning step that fixes the query itself. The invoice grows and the query is still slow. Scaling up rarely fixes a slow Azure SQL Database. Most slow Azure SQL workloads are stalled on a stale execution plan, a missing index nobody built, or a query that hits the server five hundred times when it should hit it once. Scaling up hides that waste instead of fixing it. You keep paying for the cover-up every month afterward. Every query that costs the database meaningful work leaves a record in Query Store, and the default capture mode skips only what’s infrequent or trivial. Which tool you open first decides how fast you find where the waste is hiding. ## What’s Throttling Your Azure SQL Database Azure SQL Database hides the machine from you on purpose: the platform owns the hardware, so there’s no server to sign into and no trace flag to flip. [Traditional instance-level tuning](https://learn.microsoft.com/azure/azure-sql/database/performance-guidance?view=azuresql) a SQL Server DBA takes for granted simply isn’t an option here. That gap shows up fastest for teams that [migrated a SQL Server database to Azure SQL](https://adamtheautomator.com/migrate-sql-server-azure-sql-database/), since the tuning muscle memory from the old server no longer applies. Tuning moves up a layer, from server settings to query shape, index design, and the tools Microsoft built to replace those settings. Azure measures performance against one of two [purchasing models](https://learn.microsoft.com/azure/azure-sql/database/purchasing-models?view=azuresql): the DTU model, a blended score of CPU, memory, and I/O, or the vCore model, which scales compute and storage independently. When a database sits at its resource limit, every query on it slows down together. Query Store is the foundation: every other tool here just reads what it captured. That single record feeds Query Performance Insight and Automatic Tuning, each drawing on it for a different kind of diagnosis. ![Query Store fan-out](https://adamtheautomator.com/wp-content/uploads/2026/09/query-store-pipeline-scaled.jpg) ## Finding the Expensive Queries with Query Performance Insight [Query Performance Insight](https://learn.microsoft.com/azure/azure-sql/database/query-performance-insight-use?view=azuresql) is the fastest way into that telemetry: [Query Store](https://learn.microsoft.com/sql/relational-databases/performance/monitoring-performance-by-using-the-query-store) runs underneath it by default, capturing query text, execution plans, and runtime statistics on every new database. Confirm it’s collecting with a two-line check run from whatever tool you use to [connect to Azure SQL Database](https://adamtheautomator.com/connect-to-azure-sql-database/): ```sql SELECT actual_state_desc, desired_state_desc, current_storage_size_mb, max_storage_size_mb FROM sys.database_query_store_options; ``` If `actual_state_desc` doesn’t come back `READ_WRITE`, nothing downstream has data to work with. Query Performance Insight also needs a couple of hours of active capture before its charts render anything useful. Watch for one specific mismatch: `desired_state_desc` reading `READ_WRITE` while `actual_state_desc` reads `READ_ONLY`. That gap means Query Store filled its storage allocation and demoted itself, so `current_storage_size_mb` will already be sitting at `max_storage_size_mb`. Capture has silently stopped. Raise the storage cap or shorten the retention window before you trust any chart built on that data. ### Reading the Top Queries Chart Once that’s confirmed, Query Performance Insight (under **Intelligent Performance** in the portal, or the same telemetry through T-SQL) opens on the top five CPU consumers by default. The chart overlays a DTU percentage line against bars for the queries you select, and a **Custom** tab re-slices the same data by duration or execution count. Duration surfaces queries most likely to be locking resources and blocking other sessions. Execution count surfaces something easy to miss: ten milliseconds looks harmless until a query fires five thousand times an hour, racking up more cumulative cost than one slow query ever will. Microsoft’s own guidance calls this [“chatty” query behavior](https://learn.microsoft.com/azure/azure-sql/database/query-performance-insight-use?view=azuresql#review-top-queries-per-execution-count). Work the top three to five resource consumers first, then re-open the chart a day later and check whether the DTU line moved. Inefficient queries rarely distribute their damage evenly, so those few queries are usually the whole complaint. ## Why Plans Regress, and How Automatic Correction Fixes It Query Performance Insight names the expensive queries; the execution plan the optimizer chose explains them. The optimizer compiles a plan based on the parameter values it sees on first run, then caches that plan for reuse, a shortcut called parameter sniffing. It works until a later execution passes very different parameter values, the cached plan turns out to be a poor fit, and the optimizer keeps using it anyway because nothing has told it to reconsider. The same mechanics govern [performance tuning in SQL Server](https://adamtheautomator.com/performance-tuning-in-sql-server/) running on-premises, where parameter sniffing was documented long before Azure SQL Database existed. Most sudden CPU or I/O spikes with no obvious cause trace back to a plan that stopped matching the query it was built for. [Automatic Plan Correction](https://learn.microsoft.com/azure/azure-sql/database/automatic-tuning-overview?view=azuresql), the `FORCE_LAST_GOOD_PLAN` option, compares a regressed plan’s performance against the history Query Store retained and forces the last plan that worked. Databases created after March 2020 ship with it enabled; older databases need it turned on explicitly: ```sql ALTER DATABASE CURRENT SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON); ``` Even with the option off, the engine keeps logging every regression into `sys.dm_db_tuning_recommendations`: the regressed and recommended plan IDs, the estimated CPU gain, and the exact `sp_query_store_force_plan` call to force it by hand. Query that view on the same schedule you check the DTU chart. ## Finding and Fixing Missing Indexes Without Guessing Every disk-based nonclustered index pays off on reads and costs you on writes: more storage, and more work on every `INSERT`, `UPDATE`, and `DELETE` that touches the indexed columns. That tradeoff is why [index design](https://learn.microsoft.com/sql/relational-databases/sql-server-index-design-guide?view=sql-server-ver17) still matters even though Azure SQL Database can build indexes for you. The optimizer gets more cardinality information from a unique index than from a non-unique one on the same columns. Indexing a column with millions of rows but only a handful of distinct values (a status flag, a boolean) rarely earns back the write overhead it costs. Modifying an existing index with an `INCLUDE` column usually beats creating a near-duplicate one. ### The DMV Query That Surfaces Missing Indexes During compilation, the optimizer logs any index it estimates would have significantly cut the cost of the query it just compiled, but only when that query earns a full optimization pass. Simple single-table lookups usually resolve with a trivial plan instead and skip that logging step entirely. Only one thing follows from a clean result here: nothing you’ve run yet was expensive enough to trigger full optimization. It says nothing about whether the workload actually has index gaps. That log lives in a set of dynamic management views, queryable from [SQL Server Management Studio](https://adamtheautomator.com/sql-server-management-studio/) or another T-SQL client. Joining them produces a ranked list of what’s missing: ```sql SELECT migs.avg_user_impact, mid.statement, mid.equality_columns, mid.inequality_columns, mid.included_columns FROM sys.dm_db_missing_index_details AS mid INNER JOIN sys.dm_db_missing_index_groups AS mig ON mid.index_handle = mig.index_handle INNER JOIN sys.dm_db_missing_index_group_stats AS migs ON mig.index_group_handle = migs.group_handle WHERE migs.avg_user_impact > 50 ORDER BY migs.avg_user_impact DESC; ``` (Tested on Azure SQL Database Basic tier, compatibility level 170, engine version 12.0.2000.8.) `avg_user_impact` estimates the percentage cost reduction the index would deliver. Sort by that column first. Microsoft Learn’s [_Tune nonclustered indexes with missing index suggestions_](https://learn.microsoft.com/sql/relational-databases/indexes/tune-nonclustered-missing-index-suggestions?view=sql-server-ver17) is explicit that these aren’t mandates: “Missing index suggestions aren’t prescriptions to create indexes exactly as suggested.” The DMVs also reset on every restart. Creating every suggestion on a write-heavy table can trade one performance problem for a worse one. Stale statistics compound the same risk from the other direction: an outdated row-count estimate misleads the optimizer as badly as no index at all, so a new index judged against stale numbers gets blamed for the estimate’s mistake, not its own. * * * _**Warning: A missing-index recommendation is a hypothesis, not a mandate. Check _**`avg_user_impact`**_ against your actual write volume on that table before you build anything, and re-check it after the next restart clears the DMVs.**_ * * * ## Letting Azure Tune Itself: Automatic Index Management and Its Safety Rails Automatic tuning runs the same missing-index analysis continuously across every database in an estate and acts on what it finds, adding two more advisor recommendations to `FORCE_LAST_GOOD_PLAN`: `CREATE_INDEX`, which builds a flagged index and verifies the workload got faster, and `DROP_INDEX`, which removes duplicates and anything [unused for 90 days or longer](https://learn.microsoft.com/azure/azure-sql/database/automatic-tuning-overview?view=azuresql). ```sql ALTER DATABASE CURRENT SET AUTOMATIC_TUNING (CREATE_INDEX = ON, DROP_INDEX = ON); ``` ### Safety Rails for Automatic Changes By default only `FORCE_LAST_GOOD_PLAN` is on; index creation and removal stay off until you opt in, a sensible default for a change that touches physical schema. Three safety rails govern what happens once you do opt in: * The platform drops an index that doesn’t measurably help and recreates it if the workload proves otherwise. * If CPU, data I/O, or log I/O crossed 80% in the previous 30 minutes, the platform postpones the change rather than adding load to a database already under pressure. * Validating whether an index helped takes 30 minutes to 72 hours, depending on how often the affected queries run. Automatic tuning can’t tell a critical customer-facing call from an overnight batch job, and optimizing for average latency is the wrong tradeoff when that call is bound by a hard SLA. A release pipeline running schema deployments can silently drop an automatically created index when its migration script doesn’t know the index exists. Review recent tuning history without T-SQL through [`Get-AzSqlDatabaseRecommendedAction`](https://learn.microsoft.com/en-us/powershell/module/az.sql/get-azsqldatabaserecommendedaction) in PowerShell. The cmdlet takes a mandatory `-AdvisorName` alongside the resource group, server, and database. It returns nothing at all until the platform has enough workload history to make a recommendation. The advisors worth checking are `CreateIndex`, `DropIndex`, and `ForceLastGoodPlan`. ## SQL Performance Tuning by Symptom: CPU, IO, Blocking, and Chatty Connections Not every symptom needs the same tool. Reaching for Query Performance Insight when the real problem is blocking wastes time during an incident. High CPU points toward [missing indexes and plan regressions](https://learn.microsoft.com/azure/azure-sql/database/high-cpu-diagnose-troubleshoot?view=azuresql). High I/O with normal CPU often means a scan where a seek should happen, visible in the execution plan itself. And [blocking or deadlocks](https://learn.microsoft.com/azure/azure-sql/database/understand-resolve-blocking?view=azuresql) show up as queries that look fine alone but pile up waiting on a lock another session holds, a symptom none of the other tools surface on their own, because the query causing the wait isn’t the one that’s slow. | Tool | Best for | Scope | | --- | --- | --- | | Query Performance Insight | Top CPU, duration, or execution-count offenders, fast to open | Single or pooled database | | Query Store | Plan history, regression detection, forcing plans | Single database | | DMVs (`sys.dm_db_missing_index_*`, `sys.dm_os_wait_stats`) | Ad hoc, scriptable, point-in-time diagnosis | Single database, resets on restart | | [Database watcher](https://learn.microsoft.com/azure/azure-sql/database-watcher-overview?view=azuresql) | Low-latency, estate-wide view | Entire Azure SQL estate | Azure SQL Insights, the estate-monitoring tool this workflow used before, was retired at the end of December 2024; database watcher is Microsoft’s current recommendation for the same job, and it skips the collection-agent VM you’d otherwise have to provision and patch yourself. ### Query Anti-Patterns That Waste the Optimizer’s Work Four query shapes show up in almost every slow-query list: * `SELECT *` against a wide table forces the engine to read columns the application never uses, and blocks the optimizer from using a narrow covering index even when one exists. * N+1 patterns, one query per row instead of one query for the whole set, multiply round trips and network latency by however many rows the outer loop touches. * Non-parameterized queries generate a fresh execution plan on every call instead of reusing a cached one, burning compile time a parameterized version would skip. * Row-by-row inserts and updates instead of [batched operations](https://learn.microsoft.com/azure/azure-sql/performance-improve-use-batching?view=azuresql) turn one possible round trip into hundreds. [Connection pooling](https://learn.microsoft.com/azure/azure-sql/database/performance-guidance?view=azuresql#optimize-connectivity-and-connection-pooling) addresses a related but separate cost: opening a fresh physical connection for every request. Most data providers pool connections by default, but it’s worth confirming. Opening a fresh connection per call adds that setup cost to every request, on top of the query itself. ## Three Checks Ahead of a Resize Business Critical and Hyperscale exist because some workloads need more vCores, memory, or faster local storage than SQL performance tuning alone can deliver. Microsoft’s [high-CPU troubleshooting guidance](https://learn.microsoft.com/azure/azure-sql/database/high-cpu-diagnose-troubleshoot?view=azuresql#when-to-add-cpu-resources) points the same direction: add CPU resources once your queries and indexes are already properly tuned, not before. Check the same three things every time: * Whether CPU, memory, IO, or storage is consistently near its limit, not just spiking during one bad afternoon. The DTU line in Query Performance Insight over a week tells you more than a single alert does. * Whether the top resource consumers from earlier are still running the same execution plan they were a month ago, or whether a regression crept in uncorrected. * Whether the missing-index DMVs are still showing high-impact recommendations nobody’s acted on. * * * _**Reality Check: a bigger compute tier bills the same waste at a higher rate. Scale only when the bottleneck survives query and index work.**_ * * * Once all three come back clean, a higher service tier or a move from the DTU model to vCore is the right purchase. ## Watching an Entire Estate with Database Watcher Checking thirty databases one at a time across Azure SQL Database and Managed Instance is a full-time job on its own (the on-premises boxes nobody’s decommissioned stay a separate chore - database watcher doesn’t reach them). [Database watcher](https://learn.microsoft.com/azure/azure-sql/database-watcher-overview?view=azuresql) is Microsoft’s managed answer for that Azure-side estate view, and unlike its VM-based predecessor, nothing in the path runs on a VM you provision: ### The Four-Step Setup Flow 1. Create a watcher resource and point it at SQL targets, the databases, elastic pools, or managed instances you want monitored, up to 100 per watcher. 2. The watcher connects to each target directly and collects from more than 70 catalog views and DMVs. No collector agent, no VM to patch. 3. Collected data lands in a central store you choose: an Azure Data Explorer cluster or a Real-Time Analytics database in Microsoft Fabric. 4. Azure Workbooks-based dashboards sit on top: an estate dashboard with heatmaps across every monitored resource, plus a resource dashboard with the query, index, and wait-statistics detail Query Performance Insight gives one database at a time. That four-step path collapses the ownership split Azure SQL Insights required. Azure runs the watcher and the dashboards; you only choose and size the data store that holds what they collect. ![Database watcher pipeline](https://adamtheautomator.com/wp-content/uploads/2026/09/database-watcher-architecture-scaled.jpg) * * * _**Pro Tip: Grant the watcher’s identity only the server roles Microsoft documents for Azure SQL Database, membership in _**`##MS_ServerPerformanceStateReader##`**_, _**`##MS_DefinitionReader##`**_, and _**`##MS_DatabaseConnector##`**_, nothing broader. Database watcher checks its own permissions on connect and disconnects outright if it holds anything extra, so an overly generous grant doesn’t just violate least privilege. It stops data collection.**_ * * * Once telemetry lands in the data store, [Kusto Query Language](https://learn.microsoft.com/azure/data-explorer/kusto/query/) (or T-SQL, since Azure Data Explorer answers both) queries across the entire monitored estate, and Azure Monitor alert templates notify you when a database’s conditions need attention. Retention runs per database or per table, well past what a single database’s Query Store was ever built to hold. Database watcher itself is still in preview: the watcher resource and its dashboards are free, but the Azure Data Explorer cluster or Fabric capacity backing the data store carries its own cost. ## Tune the Query Before You Tune the Invoice None of this replaces judgment. Treat a [database advisor recommendation](https://learn.microsoft.com/azure/azure-sql/database/database-advisor-implement-performance-recommendations?view=azuresql) as the opening argument in SQL performance tuning; you still make the call. The invoice is the last thing you should be tuning. Share this article [Share on X](https://twitter.com/intent/tweet?url=https%3A%2F%2Fadamtheautomator.com%2Fazure-sql-performance-tuning%2F&text=Stop%20Scaling%20Azure%20SQL%3A%20Find%20Real%20Performance%20Issues)[Share on Facebook](https://www.facebook.com/sharer/sharer.php?u=https%3A%2F%2Fadamtheautomator.com%2Fazure-sql-performance-tuning%2F)[Share on LinkedIn](https://www.linkedin.com/sharing/share-offsite/?url=https%3A%2F%2Fadamtheautomator.com%2Fazure-sql-performance-tuning%2F) ## Related Posts ![](https://adamtheautomator.com/wp-content/uploads/2026/02/featured_image-20.webp) ### [How to Migrate SQL Server to Azure SQL Database](/migrate-sql-server-azure-sql-database/) Learn how to migrate SQL Server to Azure SQL Database using Azure DMS and SqlPackage, covering assessment with Azure Arc, schema migration, data movement, and post-migration validation. ![](https://adamtheautomator.com/wp-content/uploads/2026/09/featured_image-1.webp) ### [Bicep: Never Hand-Write Azure ARM JSON Again](/azure-bicep-vs-arm-templates/) Learn how Bicep simplifies Azure infrastructure deployment with domain-specific language, dependency inference, and reusable modules for cleaner IaC. ![](https://adamtheautomator.com/wp-content/uploads/2026/08/featured_image-4.webp) ### [Application Insights: Catch Failures Before Customers Do](/application-insights-catch-failures/) Azure Application Insights closes the gap between an uptime check and real application health, using telemetry, KQL, and alerts. ## Categories * [IT Ops](/category/it-ops/) * [Cloud](/category/cloud/) * [DevOps](/category/devops/) * [Home Ops](/category/home-ops/) * [Information Security](/category/infosec/) * [Software Development](/category/software-development/) ## Site * [Home](/) * [Tutorials](/tutorials/) * [Instructors](/author/) * [Advertising](/advertising/) * [Recommended Resources](/resources/) * [About Adam](/about-adam/) Copyright 2026© ATA Learning | [Privacy Policy](/privacy/)