Database Hub in Fabric: Now in Public Preview -Turn Database Signals into Guided Action
As applications grow, so does the database estate behind them. What starts with a handful of databases can become hundreds or thousands of resources spread across services, subscriptions, and environments. For the teams responsible for keeping them healthy, the challenge is no longer just collecting signals. It is knowing what needs attention, why it matters, and what to do next. Now in public preview, Database Hub in Microsoft Fabric brings inventory, health, performance, security, and optimization insights into one connected experience. It helps administrators and developers see the bigger picture, identify affected resources, and move from estate-wide visibility to focused investigation, without losing the context that brought them there. The preview brings together supported resources across Azure SQL Database, SQL Server enabled by Azure Arc, Azure Database for PostgreSQL, Azure Cosmos DB, and SQL database in Fabric. Available capabilities and signals vary by service and preview configuration. Database Hub Overview brings together estate-wide findings and cross-engine performance trends to help you prioritize what needs attention. Start with what needs your attention When you manage a large database estate, another dashboard is only useful if it helps you decide where to spend your time. The Overview page provides that starting point, bringing together findings across security, performance, and optimization. It distinguishes Issues, which require timely attention, from Suggestions, which highlight opportunities to improve your estate over time. You might begin with a security configuration that needs review or a performance signal that warrants investigation. Selecting a finding opens Estate with the relevant filters already applied, so you can see the affected resources rather than search for them again. The result is a more direct path from “something needs attention” to “these are the resources I should investigate.” Overview highlights issues across Security, Performance, and Optimization, with affected-resource counts and direct links to investigate further. Bring your estate into one view, and make it relevant to you The Estate experience brings supported resources into a unified inventory, scoped to your existing permissions. Search and filters help you narrow that inventory to the service, subscription, or resource group you manage. This is especially useful when your responsibilities span database engines. You can begin with a broader view, then focus on Azure SQL databases, Arc-enabled SQL Server resources, or Azure Database for PostgreSQL flexible servers without rebuilding the resource list in separate portals. Each resource brings its associated Issues and Suggestions into context. Open a finding to review the affected resource, supporting information, and recommended next step. The inventory also respects the resource model of each service. For example, the PostgreSQL view represents flexible server instances, rather than every database hosted within them. A unified view does not mean moving your databases. Your Azure resources remain in their existing subscriptions, and on-premises SQL Server instances remain on-premises, connected through Azure Arc. Database Hub brings operational context together; it does not require relocating or mirroring your application data into Fabric. Estate brings resource inventory, Issues, and Suggestions into one view, with quick actions to continue investigation in the Azure portal, VS Code, or SSMS. Understand security posture and performance in context Database Hub helps you identify configurations worth reviewing and resources that need a closer look. For security, supported signals can include Microsoft Entra authentication, customer-managed keys, and auditing coverage, depending on the database service. These findings provide a starting point for investigation, not a blanket judgment that every flagged resource is insecure or noncompliant. For example, a suggestion to review customer-managed keys should be evaluated against your organization’s requirements. A resource using service-managed keys is still encrypted at rest. For performance, built-in dashboards bring supported utilization and activity signals into view, including CPU, memory, storage, I/O, and connections. Review trends across your estate, then narrow the scope and time range to investigate individual resources. Metric availability varies by engine and monitoring configuration; missing data should not be interpreted as good health. For supported SQL monitoring scenarios, you can also create a custom monitoring dashboard from a template and tailor its charts and layout to the resources your team follows regularly. Together, these experiences help answer two practical questions: Where is pressure building, and where is there an opportunity to improve? Performance summaries highlight recent capacity signals and trends across Microsoft SQL, PostgreSQL, and Cosmos DB. The Security dashboard summarizes Microsoft Entra authentication, audit logging, and customer-managed key coverage, helping you identify configurations that warrant review across your estate. The Performance monitoring dashboard combines memory-pressure trends, resource-level details, and interpretation guidance to help focus your investigation. Move from a finding to an informed next step Estate-wide visibility is the beginning of an investigation, not a replacement for database expertise. When a finding needs deeper analysis, Database Hub helps you review the evidence and continue in the appropriate native experience, such as the Azure portal or SQL Server Management Studio (SSMS). Engine-specific diagnostics and configuration changes remain in the tools designed for that work. Consider a performance issue that occurred before an administrator could inspect it. In supported agent-assisted scenarios, captured incident evidence can help establish what happened, when it happened, and which queries or sessions were involved. That context gives the administrator a more informed starting point for investigation. The operator remains in control: review the evidence, evaluate the recommendation, and apply authorized changes through the appropriate service tools. The preview investigation workflow does not automatically remediate issues, and existing permissions and approval requirements continue to apply. Bring database expertise into agent-assisted workflows We are also making database operational intelligence available through agent skills: reusable capabilities that agents can call to support observability, monitoring, diagnostics, optimization, and other database operations. Database Hub provides estate context; skills provide a way to bring database expertise into an agent-assisted investigation. This creates a foundation for helping teams move from identifying an affected resource to understanding its signals and evaluating the next step. We’re building toward making this expertise available in the AI companions and development tools teams already use, with recommendations grounded in evidence, the specific database, and your organization’s operational controls. If we take a step back, our broader vision brings together a free, extensible Database Hub experience, agentic observability, and OneLake integration, all within Microsoft Fabric. It’s a foundation for connecting database operations with analytics and AI and helping teams turn signals into informed action. Get started with Database Hub Start with the Database Hub documentation to review preview availability, supported services, and setup requirements. Ask your Fabric administrator to enable the preview where required, confirm that you have the appropriate resource permissions, and complete any service-specific monitoring prerequisites. Then open Microsoft Fabric, select Databases in the left navigation, and begin with Overview. Choose a finding, explore the affected resources in Estate, and follow the evidence into your next investigation. For more details on supported services and getting set up, the Database Hub documentation is also available on Microsoft Learn. Learn more What is Database Hub in Fabric? Enable performance monitoring for Microsoft SQL Performance Monitoring for Azure SQL in Database Hub
Azure SQL Managed Instance Next-gen: Severe Slowdowns During Backups
We recently upgraded our Azure SQL Managed Instance from legacy General Purpose to Next-gen General Purpose. Afterward, automatic full backups caused severe application slowdowns. I’m sharing our experience so others can evaluate this before upgrading. We had used legacy General Purpose for more than three years without SQL performance problems. We never paid attention to when the automatic backups ran because they had no noticeable impact. After upgrading, we experienced three consecutive Thursday afternoon slowdowns during full backups, severe enough that multiple customers asked whether we were having an outage. The important difference is how storage performance is allocated. On legacy General Purpose, each database file receives IOPS and throughput based on its allocated size. On Next-gen, IOPS are configured at the instance level, and the allocated IOPS also determine throughput. Our configuration is 4 vCores with 832 GB of reserved storage, primarily supporting one large production database. Its data file is approximately 703 GiB. Under legacy General Purpose, that file falls in the published tier providing 5,000 IOPS and 210 MB/s. After upgrading, the default Next-gen allocation was 2,496 IOPS, calculated as three IOPS per GB of reserved storage. Using Microsoft’s throughput formula of IOPS divided by 30, that provides approximately 83.2 MB/s shared across the instance. In other words, we went from an allowance of approximately 210 MB/s for our main data file alone to approximately 83 MB/s for the instance. Our backups typically run near 100 MB/s, so the reduced allowance left no room for application traffic and caused severe slowdowns. Reverting to legacy restored normal application performance. This will not necessarily affect every workload the same way. Instances with smaller databases may benefit from the different allocation model. Next-gen also allows additional IOPS to be purchased. However, Microsoft’s positioning of the upgrade as having no additional cost needs an important qualification: the upgrade itself may leave the bill unchanged, but maintaining comparable storage performance can require additional spending. Moreover, purchasing additional Next-gen IOPS alone would not restore our previous throughput allowance. Our 4-vCore Next-gen configuration has a maximum throughput of 145 MB/s, regardless of how many additional IOPS we purchase, compared with approximately 210 MB/s for our main data file on legacy. Reaching comparable throughput would therefore require increasing the vCore count as well. In our case, reverting restored acceptable performance, so we plan to remain on legacy for now. Before upgrading, compare your existing per-file IOPS and throughput allowances with the proposed Next-gen instance-wide allocation. Include automatic full backups in that assessment, even if ordinary application traffic is light and CPU utilization is low. The IOPS allocation rules are documented, but we did not find a clear warning that upgrading with the default settings could substantially reduce available throughput for an existing workload and turn previously unobtrusive backups into a major performance event. If anyone from Microsoft is reading, please make this tradeoff explicit in the upgrade guidance. An upgrade assessment that compares existing file-level allowances with the proposed instance-level limits would help customers avoid discovering this in production.Ask your database estate a question: Copilot in Database Hub is now in public preview
Most database teams already collect a lot of monitoring data. What they don't have enough of is time to work out what it means. When something looks wrong, the first job is usually to open several portals, find the affected resources, and work out what changed. Database Hub in Microsoft Fabric already brings your Azure, Fabric, on-premises and multicloud databases into one view. Today we're making that view easier to use: Copilot in Database Hub is now in public preview. You can ask questions about your estate in plain language and get answers grounded in what Database Hub already knows about your databases. What you can do with Copilot in Database Hub Area What Copilot helps with Try asking Prioritize Rank the issues across your estate and show how many resources each one affects "What should I fix first?" Performance Find resources near storage, memory or compute capacity, and explain what changed "Are there any performance issues I should know about?" Troubleshooting Investigate slowdowns and blocking that affect your applications "Are there any blocked queries in my database causing delays in app response?" Security Find risky configurations, such as public network access or endpoints without private access "What are the biggest security risks in my environment?" Migration readiness Bring SQL Server assessment results into one answer "Analyze migration readiness across my SQL Server estate" Ask Copilot from wherever you're working Copilot is on every main page in Database Hub. Select Ask Copilot in the top right of the page you're on: Overview: you're looking at the issue counts and want to know what to fix first. Estate: you're going through resources and their issues and want to know which of this matter. Performance monitoring: you're watching metrics for Microsoft SQL, PostgreSQL or Cosmos DB and want to know why something changed. The Copilot pane opens beside the page, so you keep your place while you ask. Database Hub Overview page showing security, performance and optimization issues, with the Ask Copilot selected Ask Copilot button on the Estate and Performance monitoring pages in Database Hub. Get started with suggested questions Copilot opens with suggested questions grouped by the three things teams ask about most: Check performance: How are my databases performing? or Are there any performance issues I should know about? Review security: How secure are my databases?, What should I fix first to improve security? or What are the biggest security risks in my environment? Assess migration readiness: Show my existing SQL Server migration assessment results, or Analyze migration readiness across my SQL Server estate. Ask, follow up, then go deeper Here's a typical investigation: Ask a broad question. "What should I fix first to improve security?" Read the ranked summary. Copilot lists the top findings across your estate, such as endpoints without private access or public network access left on. It shows how many resources each one affects, so the biggest risks come first. Narrow it down. "Which of those are production databases?" or "Show me the resources with public network access enabled." Open the evidence. Go from Copilot's answer to the affected resources in Estate. Each resource lists its issues, such as Public network access is enabled or Memory is near capacity. Then act with the tools and processes you already use, including opening the resource in the Azure portal, SSMS or VS Code. Troubleshoot what's slowing your apps down When users report that an app is slow, the hard part is finding which database is responsible and why. Open Copilot from Performance monitoring and start with the symptom: "Are there any blocked queries in my database causing delays in app response?" "Why did CPU spike on this database in the last hour?" "Which databases are close to their storage, memory or compute limits?" "What changed on this database since yesterday?" Copilot summarizes what it finds and points you to the affected resource. Switch to the Performance monitoring charts to see the trend for yourself before you change anything. Plan your SQL Server migrations For SQL Server teams planning a move, "Analyze migration readiness across my SQL Server estate" collects assessment results into one answer, instead of you checking one server at a time. Follow up with "Which servers have blockers?" to see where to start. How Copilot works Copilot in Database Hub answers from the information Database Hub already has about your estate: your resource inventory, the security, performance and optimization issues it detects, and the performance telemetry it collects. It uses that context to rank, summarize and explain, then links you back to the resource or chart so you can check the evidence. Copilot sees only the resources your own Azure and Fabric permissions allow. It doesn't give you access to anything you couldn't already see. Tips for better answers Start broad, then narrow. Ask "What should I fix first?" and then filter by environment, service or issue type. Name what you care about. A resource name, a time window or a symptom gives you a more specific answer than a general question does. Ask from the right page. Ask about trends from Performance monitoring and about resources and issues from Estate. Check before you act. Use the linked resources and charts to confirm what Copilot found. Built with clear boundaries Grounded in Database Hub. Copilot answers from the information available in Database Hub. Your permissions, nothing more. Copilot works within your existing Azure and Fabric permissions. You stay in control. Copilot helps you understand and prioritize. It doesn't change your databases. Responsible AI. Copilot is powered by AI, so mistakes are possible. Review its answers before you act on them in production. Get started Opt in: Turn on Copilot for your organization. Copilot in Database Hub is an opt-in experience. Nothing is turned on for your users until a Fabric administrator chooses to enable it. In the Fabric admin portal, under Tenant settings > Copilot and AI, enable these settings, and make sure the enabled scope includes the users who need Copilot: Users can use Copilot, AI Agents and other AI experiences powered by Azure OpenAI Users can use Copilot, AI Agents and other AI experiences powered by OpenAI as a Microsoft Subprocessor Data sent to OpenAI as a Microsoft Subprocessor can be processed outside your capacity's geographic region, compliance boundary, or national cloud instance You can turn each setting on for the whole organization or only for specific security groups, so you can start with a pilot group. Important: Review the OpenAI as a Microsoft Subprocessor settings with your security and compliance team before you enable them. See OpenAI as a subprocessor in Microsoft Fabric for data processing commitments and current limitations. Open Database Hub in Microsoft Fabric. Select Ask Copilot on Overview, Estate or Performance monitoring, and start with a suggested question. Frequently asked questions Is Copilot on by default? No. Copilot in Database Hub is opt-in. A Fabric administrator decides whether to enable it and for whom. Which databases does Copilot cover? The same resources Database Hub shows you: Azure SQL, Fabric, on-premises and multicloud databases connected to your hub, within your permissions. Does Copilot need a specific capacity, and how is it billed? Copilot in Fabric needs a paid Fabric capacity (F2 or higher) or a Power BI Premium capacity (P1 or higher). It doesn't need a separate per-user license. Copilot usage is measured in tokens and consumes your Fabric capacity units (CUs). You can track it in the Fabric Capacity Metrics app under the operation name Copilot in Fabric. For rates and examples, see Copilot consumption, usage, and billing in Fabric. To bill all Copilot usage for a group of users to one capacity, see Fabric Copilot capacity. Does Copilot change my databases? No. Copilot helps you understand and prioritize. You make changes with your existing tools and processes. Is Copilot available in government or sovereign clouds? Not currently. OpenAI-operated models aren't available in government clouds (GCC, GCC High, DoD) or sovereign clouds. Why don't I see Ask Copilot? Check with your Fabric administrator that the tenant settings above are enabled and that their scope includes you. Learn more Database Hub overview Overview of Copilot in Fabric Enable and configure Copilot in Microsoft Fabric Copilot and Agent tenant settings OpenAI as a subprocessor in Microsoft Fabric Copilot consumption, usage, and billing in Fabric Fabric Copilot capacity What's next This preview is the start. We'll keep growing what Copilot can answer, based on what you ask it. Tell us what you ask first, and what you wish it could answer: select Feedback in Database Hub.Closing a monitoring blind spot: version store read metrics
When we asked two popular LLMs to "rank top RDBMS on the breadth and depth of the engine monitoring data they provide", the SQL Server database engine was ranked first by one and second by the other. With hundreds of DMVs and catalog views, thousands of extended events, query plan XML, and Query Store, you or your agent can diagnose and fix nearly any performance problem. Yet sometimes we find a blind spot. A mysterious CPU increase Imagine a busy OLTP database happily humming at 30 percent CPU as it does on any given day. Then, CPU starts climbing up without any increase in the workload. At 60 percent, it sets off an alarm. You start looking for the most common culprit - a query plan regression - but find none. Query plans are unchanged, but some queries just consume more and more CPU and run slower and slower. At this point you might capture an XPerf trace and look at the top CPU consuming call stacks. This is a powerful technique that finds the culprit in this case, but it's nowhere near as quick and simple as querying a DMV. You need access to the OS to capture the trace, you need to install the Windows Performance Toolkit, and you need to download symbols. Often that's just not an option. The culprit in this case: the version store, or rather row versions that had to be kept around for much longer than usual. Row versions are needed only while some transaction might still read them. After that, a background process cleans them up. Several things can hold that cleanup back, most commonly a long-running transaction, often an idle session that started but never committed a transaction. While cleanup is blocked, things get steadily worse: Version chains get longer. Every update to a frequently modified row adds another version, and queries that need an older version of the row must walk back through more and more of them. Until now, there was no practical way to detect this without an XPerf trace or a debugger. Versions move off-row. A row can hold only one in-row version. While cleanup is blocked, further updates to a row must store their versions off-row instead, which is more expensive both to write and to read. For frequently updated rows, nearly all new versions end up off-row. Until now, you couldn't correlate a slowdown in query execution with this shift from in-row to off-row versions. The net effect is that CPU usage and logical reads creep up across the whole workload with no single query or plan standing out. This matters because row versioning is the foundation for the RCSI and snapshot isolation levels, which avoid concurrency issues due to lock blocking. Accelerated database recovery (ADR) also uses row versioning to provide fast recovery, instant rollback, and to keep the transaction log small. Both RCSI and ADR are enabled by default in Azure SQL Database, and we recommend the same configuration for most SQL Server workloads. What was missing until now is a simple way to detect when version reads start to slow you down. Shining a light on the problem In Azure SQL Database, we added version store read metrics in SET STATISTICS IO, DMVs, extended events, and query plan XML, covering both the older version store in tempdb and the newer persistent version store (PVS) in each user database. Tempdb version store: versions created for databases without ADR. In-row PVS: small versions that are stored inside the data row itself. Off-row PVS: versions that are stored in a special-purpose system table in the user database. Online index PVS: versions that are stored in a different system table while an online index operation runs. An index operation might require these versions for as long as it runs. Keeping them apart lets the regular PVS be cleaned up in the meantime. The new metrics are now exposed as follows: SET STATISTICS IO. When an object had version reads, a new line follows the familiar IO lines: Object '[dbo].[t]'. Tempdb versions read 0, in-row PVS versions read 0, off-row PVS versions read 1000, online index PVS versions read 0. Query plan XML. In a plan with runtime statistics, the RunTimeCountersPerThread element of each operator that reads a table or index has four new attributes: ActualTempdbVersionsRead, ActualInRowPvsVersionsRead, ActualOffRowPvsVersionsRead, and ActualOnlineIndexPvsVersionsRead. This shows which operator in the plan read row versions, what kind, and how many. Extended events. The sql_statement_completed, sp_statement_completed, sql_batch_completed, and rpc_completed events have four new fields each: tempdb_versions_read, inrow_pvs_versions_read, offrow_pvs_versions_read, and online_index_pvs_versions_read. Query and module statistics. sys.dm_exec_query_stats, sys.dm_exec_procedure_stats, sys.dm_exec_trigger_stats, and sys.dm_exec_function_stats have new total_version_reads, last_version_reads, min_version_reads, and max_version_reads columns. These columns report the total, last, minimum, and maximum of the sum of all four counters. You can rank cached queries and modules by version reads and see the correlation with the CPU (worker) time, elapsed time, and logical reads. Sessions and requests. sys.dm_exec_requests and sys.dm_exec_sessions have a new version_reads column, also combining the four counters. Use the first to see how many version reads a request has done so far. Use the second to see the total for all completed requests during the session's lifetime. When version cleanup is blocked, version reads increase along with CPU time and logical reads across the workload, and you might also see a shift from in-row to off-row versions in PVS. That steers your investigation to the version store rather than to query plans or the workload itself. From there, the Monitor and troubleshoot accelerated database recovery article describes how to find and remove whatever is blocking cleanup. To learn more, see Version read metrics in documentation. Is this about CPU or IO? You might wonder - if the cost of version traversal is higher CPU, why expose it under STATISTICS IO? Indeed, there was considerable debate: should we add a new SET STATISTICS command (STATISTICS VERSION_CHAIN perhaps) specifically for these metrics? In the end, we settled on STATISTICS IO for three reasons: We didn't want you to memorize and type an extra command. We wanted to make the new statistics easy to discover. Logical reads in STATISTICS IO are already about CPU work (buffer pool access, not disk IO). Version reads are the same kind of measure, only for rows instead of pages. Keeping them together is consistent. Could we expose the IO cost of version reads instead, counting the pages read from a version store? Sure, but it would still leave a large blind spot: in-row versions. There is no extra IO cost for reading them because they are stored with the data row itself, and brought into memory when the data page is read. Yet traversing in-row versions and reconstructing older versions from in-row diffs adds nontrivial CPU cost. That cost would remain hidden if we only exposed IO statistics for off-row version pages. Why did we make this improvement? Besides the obvious reason - removing the blind spot in the database engine monitoring capabilities - we wanted to remove the uncertainty about version read overhead. While it can't be zero by its nature, the concurrency and availability benefits of RCSI and ADR are enormous. Yet we sometimes see customers hesitate to enable these features in SQL Server because it's hard to monitor and quantify the overhead. We hope that this improvement takes that hesitation away :). What's next At this point, we anticipate two questions: What about Query Store? Why not expose version reads together with other query runtime statistics? This is under consideration. Do you need version reads as a new metric in Query Store? Please leave a comment and describe how this would help. What about SQL Server and Azure SQL Managed Instance? The new metrics will soon be available in Azure SQL Managed Instance with the always-up-to-date update policy. As for nearly all database engine improvements that first appear in Azure SQL Database, we plan to include this improvement in the next version of SQL Server (and in the corresponding versioned Azure SQL Managed Instance) once they arrive. Tell us what you think about this improvement in the comments. Are there other blind spots that make it harder to see what's going on in the database engine? Share what you run into in your daily work. Example Here's a quick way to see some of the new metrics. Connect to a database in Azure SQL Database, and execute as a single batch. Note that the graphical query plan in SSMS doesn't yet show the new metrics even though they are present in the actual plan XML. That will be available in a future version of SSMS. SET NOCOUNT ON; /* Create a table with 1000 rows. */ DROP TABLE IF EXISTS dbo.t; CREATE TABLE dbo.t (id int PRIMARY KEY, v int NOT NULL); INSERT INTO dbo.t (id, v) SELECT TOP (1000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), 0 FROM sys.all_columns; /* Create versions in Persistent Version Store (PVS). With Accelerated Database Recovery (ADR), rollback is instant, but the row versions from an aborted transaction stay in place until background cleanup happens later. */ BEGIN TRAN; UPDATE dbo.t SET v = v + 1; ROLLBACK; /* Immediately, read the data from the table. This requires version reads because PVS cleanup hasn't completed yet, but the SELECT must see the data before the aborted transaction. Version read statistics are returned after IO statistics. Showplan XML contains four new attributes under RunTimeCountersPerThread. */ SET STATISTICS IO, XML ON; SELECT COUNT(*) AS cnt FROM dbo.t WHERE v = 0; SET STATISTICS IO, XML OFF; /* Version reads are also present in the current request statistics. The value is doubled because of a statistics update by the previous SELECT, which also reads the same row versions. */ SELECT version_reads FROM sys.dm_exec_requests WHERE session_id = @@SPID; /* Aggregate version reads are also available for queries with cached plans, such as the previous SELECT (cached after auto-parameterization). */ SELECT qs.execution_count, qs.total_version_reads, qs.last_version_reads, qs.min_version_reads, qs.max_version_reads FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st WHERE st.text LIKE '%SELECT COUNT(*) \[cnt\] FROM \[dbo\].\[t\] WHERE%' ESCAPE '\';
Public Preview: Performance monitoring for Azure SQL in Database Hub
We're excited to announce the public preview of performance monitoring for Azure SQL in Database Hub in Fabric. Performance monitoring brings the health and performance of your SQL estate into Database Hub. You see every supported database in one place and quickly spot the ones that need attention. It works across: Azure SQL Database Azure SQL Managed Instance SQL Server on Azure Virtual Machines SQL Server enabled by Azure Arc Microsoft collects performance-related telemetry, runs the pipeline, and stores the data for you. There's nothing you need to deploy and nothing to operate. Turn it on, and your SQL resources show up on the Performance page in Database Hub, which is free. This post is a deep dive on the performance part of Database Hub. For the full tour, including the Overview, Estate, and Security pages, read Database Hub in Fabric: Now in Public Preview. Get started in three steps Open Database Hub in Fabric. During the preview, a Fabric administrator needs to turn on the Database Hub tenant setting. See the prerequisites. Turn on performance monitoring for your SQL resources. See Enable performance monitoring for Microsoft SQL. Go to the Performance page to see which databases need your attention. See your entire database estate in Database Hub Performance monitoring powers the Performance page in Database Hub. Database Hub is built for when you need to look across all your databases, not just one at a time. It brings your database estate across Azure, on-premises, and other clouds into one place, including: Azure SQL and SQL Server enabled by Azure Arc Azure Database for PostgreSQL Azure Cosmos DB The Performance page comes with prebuilt dashboards, so you can see performance at a glance without building anything yourself. The dashboards are designed to quickly answer two questions: Are my databases healthy? Which ones need my attention? From there, you can drill into resource usage, waits, and session activity to understand what's driving a change in performance. With Database Hub, you can: Get estate-wide visibility into health and performance across database types Investigate the root cause of performance issues across many databases Use AI-assisted analysis to find and explain issues faster To learn more, see What is Database Hub in Fabric? Why we built this Monitoring SQL performance at scale often meant building and running your own monitoring stack. Before you could effectively answer, "Which of my databases need attention right now?" you typically had to: Deploy and configure a collection resource, such as a watcher or an agent Build a telemetry pipeline to move the data Provision a data store, and then pay for it, secure it, and keep it running Build dashboards on top of all of it Repeat for every new server, database, or region That's a lot of work before you see your first chart. And every step is another thing that can break, drift, or quietly stop collecting data. Customers are also turning to AI to make sense of their database estate. They want an AI agent that can spot a performance problem, explain what's causing it, and recommend a fix. But an AI agent is only as good as the data it can reach. It operates best with one consistent source of performance data across every database, not a patchwork of tools and data stores. We heard this feedback loud and clear from customers. You told us you love having at-scale dashboards and ownership of your performance data. You also told us that setting up a telemetry stack was time-consuming, scale limits got in the way, and running the data store added operational burden and cost overhead. One customer put it simply: they wanted to spend less time managing their telemetry infrastructure and more time managing and improving their databases. Performance monitoring keeps the parts you valued and removes the infrastructure you had to manage. That's why it feeds Database Hub directly. You get one place to see your whole estate, and your AI agents get one consistent source of performance data. No infrastructure to manage (or pay for) With performance monitoring, there's no monitoring infrastructure for you to deploy, size, or run. It's all managed by Microsoft, with no scale limits on how many targets you can monitor. Telemetry is collected close to the database engine and sent to a Microsoft-managed telemetry pipeline and data store. Access to that data is governed by Azure role-based access control (RBAC), so people only see telemetry for the resources they already have access to. Consistent telemetry across your SQL estate Performance monitoring collects the same core set of performance data across every supported SQL deployment, whether it runs in Azure, on-premises, or in another cloud. That means one mental model and one set of dashboards in Database Hub, instead of a different tool for every flavor of SQL. The preview collects performance-related telemetry, including: CPU and memory utilization Wait statistics Active sessions Storage I/O and database storage utilization Performance counters Client connections Database properties Availability group, replica, and database replica health Go beyond Database Hub with KQL Database Hub covers the most common performance questions. When you need a view that Database Hub doesn't show, you can query the same telemetry directly with Kusto Query Language (KQL). The telemetry is available through a Microsoft-managed, RBAC-governed endpoint, so you don't need to create or pay for your own Azure Data Explorer cluster. Use it to: Build your own Real-Time Dashboards and reports Connect tools you already use, such as Grafana or Power BI Give an AI agent access to investigate performance across your estate To get started, see Query performance monitoring telemetry. It includes the schema, connection steps, and ready-to-run starter queries. Turn on performance monitoring How you turn on performance monitoring depends on the resource type. For all of the steps in one place, see Enable performance monitoring for Microsoft SQL. Resource type How monitoring is enabled in preview Step-by-step guidance Azure SQL Database Add an extended property to each database you want to monitor. You can also select Enable Performance Monitoring in Database Hub. Azure SQL Database Azure SQL Managed Instance Add an extended property to the master database Azure SQL Managed Instance SQL Server on Azure VMs Turn on a feature flag in the SQL IaaS Agent extension. SQL Server on Azure VMs SQL Server enabled by Azure Arc On by default once the server is connected to Azure Arc. SQL Server enabled by Azure Arc To view performance monitoring data, you need: The Reader role, or a role with higher privileges, on each subscription that contains the resources you want to view. The Microsoft.AzureArcData resource provider registered on each subscription. For steps, see Register the Azure resource provider. Availability Performance monitoring is available in public preview in select Azure regions. For the current list of supported regions, see Regional availability and data handling. Learn more Database Hub in Fabric: Now in Public Preview What is Database Hub in Fabric? Enable performance monitoring for Microsoft SQL Query performance monitoring telemetry Supplemental Terms of Use for Microsoft Azure PreviewsAnnouncing Automatic Backup Immutability for Azure SQL Database and Azure SQL Managed Instance
Built-in protection for your most recent backups - enabled automatically Today, we're excited to announce General Availability of automatic backup immutability up to the most recent 7 days of point-in-time restore (PITR) backups in Azure SQL Database and Azure SQL Managed Instance, at no additional cost. With this release, up to most recent 7 days of backups are automatically protected with immutability by default, regardless of your configured PITR retention period. No configuration changes, policy creation, or administrative action are required. This enhancement provides an additional layer of protection for one of your most critical recovery assets - your backups. Why backup immutability matters Cyberattacks continue to evolve, with ransomware increasingly targeting not only production data, but also backup systems. Attackers understand that if backups can be deleted, modified, or corrupted, recovery becomes significantly more difficult and costly. Traditional backup strategies focus on creating recoverable copies of data. Modern cyber-resilience strategies go further by ensuring those backups themselves cannot be altered or removed during a protected period. Immutable backups help ensure that recovery points remain available when you need them most - even in the face of malicious actions, accidental deletion, or compromised administrative credentials. What is changing? Starting with this release: Up to the most recent 7 days of Azure SQL Database and Azure SQL Managed Instance PITR backups are automatically protected by immutability Protection is enabled by default for all databases, with no additional cost No configuration or onboarding is required Protection applies regardless of the database's configured PITR retention setting Because this capability is built directly into the Azure SQL backup service, customers automatically benefit from stronger protection without changing existing backup, restore, or operational workflows. Designed for modern cyber resilience Organizations across industries increasingly require stronger safeguards around backup data as part of broader cyber-resilience programs. Automatic backup immutability helps customers: Improve protection against ransomware attacks Reduce the risk of accidental backup deletion Strengthen recovery readiness Increase confidence that recent recovery points remain available during an incident Simplify adoption of immutable backup practices without additional deployment effort This capability is particularly valuable because the backups most often used during recovery operations are typically the most recent ones. Supporting compliance and governance requirements Many industries must maintain records in a protected, tamper-resistant manner to satisfy regulatory and governance requirements. Azure Storage immutable storage capabilities have been validated for compliance scenarios involving requirements such as: SEC Rule 17a-4(f) CFTC Rule 1.31(d) FINRA record-retention requirements These regulations commonly require records to be retained in a nonerasable, non-rewritable format for a defined period of time. Azure immutable storage uses a Write Once, Read Many (WORM) model that helps organizations meet these requirements. Azure SQL database backups leverage this WORM capability from Azure storage to achieve immutability for the backups. While compliance requirements vary by organization and jurisdiction, automatic backup immutability provides an additional foundational control that can support broader security, governance, and resilience objectives. No additional complexity One of our goals with this release is to deliver stronger security without increasing operational burden. You don't need to: Create immutability policies Configure storage accounts Manage retention locks Extract data The protection is integrated directly into the Azure SQL managed backup service and works automatically for all Azure SQL Database and Azure SQL Managed Instance databases. Pricing and Availability Automatic backup immutability up to the most recent 7 days of point-in-time restore (PITR) backups is available for all Azure SQL Database and Azure SQL Managed Instance databases. There is no additional cost to use this capability. The protection is built into the Azure SQL managed backup service and is automatically applied to the most recent 7 days of backups. No configuration, policy management, or separate licensing is required. By enabling immutable protection by default and at no additional charge, Azure SQL helps customers strengthen their cyber-resilience posture, improve protection against ransomware and accidental deletion, and gain the benefits of immutable backups without added operational complexity. Building on Azure SQL's data protection foundation Automatic backup immutability is the latest enhancement in Azure SQL's ongoing investment in data protection, security, and business continuity. By combining automated backups, point-in-time restore capabilities, geo-redundant backup options, soft delete protection for your Azure SQL logical server and now automatic backup immutability for recent backups, Azure SQL continues to help organizations strengthen their resilience against both operational accidents and modern cyber threats. This is just the beginning. Azure SQL hyperscale backup immutability and a host of other additional capabilities are coming soon. FAQs Q: What is changing? A: Microsoft Azure SQL will start protecting the short-term retention backups for all Azure SQL DB and Azure SQL managed instance databases with immutability to protect against ransomware attacks. Q: Are all my backups protected? A: In this release, up to most recent 7 days of short-term retention backups are immutable, regardless of the configured retention period. For example, if the configured retention period is 7 or less, then all the backups are immutable. If the configured retention period is 35 days, then the most recent 7 days of backups are immutable. Q: Is there any additional cost for this feature? A: No. Immutability for the backups is being provided as a security feature natively. Q: When will this be available? A: The code to enable immutable policy is already in progress in all Azure regions worldwide. In the next few weeks all backups will be on immutable storage. Q: Do I need to do anything to enable/configure immutability? A: No. There is no action needed on your end. The backups will automatically be immutable once the enablement is complete. Q: How can I verify if my backups are immutable? A: In a future release, immutability status will be exposed as a database property. Q: How can I get immutability for my backups beyond 7 days? A: In this release backups up to most recent 7 days are immutable. Immutability for additional retention period will be added in a future release. Limitations Immutability for Azure SQL hyperscale is not included in this release but will be available soon. Learn more To learn more about immutable storage concepts and WORM (Write Once, Read Many) protection in Azure, see: https://learn.microsoft.com/azure/storage/blobs/immutable-storage-overview We are excited to bring this protection to every Azure SQL Database and Azure SQL Managed Instance customer automatically, helping you improve backup security and recovery readiness with no additional effort. Documentation updates More details at https://aka.ms/auto-immutability Looking ahead We are just getting started on this journey of ransomware protection. Additional flexibility and configuration options coming in future releases.Seconds of Downtime, Hours of Confusion: Handling Transient Azure SQL Disconnects
A database can recover in seconds while an application remains unavailable for much longer. Brief connectivity interruptions are expected failure modes that cloud applications should be designed to tolerate. With Azure SQL Database, transient connectivity errors can occur during short-lived service reconfiguration events. Microsoft notes that many such reconfiguration events complete in less than 60 seconds and recommends that applications implement retry logic rather than immediately surfacing transient failures to users. But the database interruption is only part of the story. An application might continue experiencing problems because of: Failed connections. Lost in-flight operations. Retry behavior. Connection-pool recovery. Authentication or token issues. Queued requests. Application instances retrying simultaneously. Backlogs that must drain after connectivity returns. This leads to an important troubleshooting principle: Database recovery and application recovery are separate milestones. Understanding that difference can turn a confusing availability incident into a defensible timeline and, more importantly, a more resilient application design. 1. Transient Does Not Mean Harmless A transient interruption is temporary, but its application impact doesn't have to be. Consider a simplified timeline: Time Event T0 Application healthy T1 Database connectivity is interrupted T2 Database connectivity becomes available again T3 Applications reconnect T4 Request backlog begins draining T5 Application returns to normal The actual database connectivity event is: T1 → T2 But users might experience impact across: T1 → T5 Calling the entire T1-to-T5 period a continuous database outage would be inaccurate unless evidence proves that the database itself remained unavailable. Instead, investigate the periods separately. 2. Build a Three-Layer Timeline A good post-incident investigation should correlate three timelines. Layer 1: Database/connectivity behavior Capture: Connection failures. Successful reconnections. Relevant Azure monitoring signals. Azure Service Health information when applicable. Database resource behavior. Layer 2: Client connectivity Capture: SQL exception number. Exception timestamp in UTC. Connection timeout. Retry attempts. Retry delays. Authentication failures. Network errors. Azure SQL troubleshooting guidance recommends logging failure timestamps and notes that SQL Database can return tracing information helpful during connectivity investigations. Layer 3: Application recovery Capture: Application health. Request failures. Queue depth. Retry volume. Connection-pool recovery. Backlog drain time. Time when users actually recovered. The timelines might reveal: Database connectivity restored │ ▼ Applications reconnect │ ▼ Backlog continues │ ▼ Application fully recovered That distinction is essential. 3. Classify the Failure Before Retrying It Not every SQL connectivity error means the same thing. Classify the symptom into categories such as: Transient availability/connectivity failure. Authentication failure. Token-related failure. Network-path problem. DNS problem. Firewall/configuration issue. Resource-governance condition. Application timeout. Long application recovery after SQL connectivity returns. Azure SQL Database documents multiple connectivity failure categories, including transient fault errors such as 40197 and 40613, authentication failures such as 18456, network termination conditions, firewall issues, and connection timeouts. The classification matters because: Not every failure should be retried in the same way. 4. Use Azure SQL Connectivity History Carefully Azure SQL Database exposes connectivity-event information through system views in the logical server's master database. One useful view is: sys.database_connection_stats It provides aggregated connection success and failure information in five-minute intervals. Connect to the logical server's master database and run: DECLARE @StartTime datetime2 = DATEADD(HOUR, -2, SYSUTCDATETIME()); SELECT database_name, start_time, end_time, success_count, total_failure_count, connection_failure_count FROM sys.database_connection_stats WHERE start_time >= @StartTime ORDER BY start_time DESC; This can help answer: Did connection failures occur? Which database was affected? In which five-minute interval? Were successful connections also occurring? But there's an important limitation. This is aggregated historical information, not a second-by-second outage trace. The data can also arrive later, with Microsoft documenting that it can take up to 24 hours for all information to appear. Therefore, don't use one five-minute bucket containing failures to claim: "The database was unavailable for five minutes." The evidence only tells you connection failures were recorded during that interval. 5. Look Deeper With sys.event_log For additional connectivity-event information, Azure SQL Database exposes: sys.event_log This view records successful database connections and connection failures and is intended to help track and troubleshoot Azure SQL Database connectivity activity. Again, connect to master. DECLARE @StartTime datetime2 = DATEADD(HOUR, -2, SYSUTCDATETIME()); SELECT database_name, start_time, end_time, event_category, event_type, event_subtype, event_subtype_desc, severity, event_count, description FROM sys.event_log WHERE start_time >= @StartTime AND event_category = N'connectivity' ORDER BY start_time DESC; Useful columns include: start_time end_time event_category event_type event_subtype_desc severity event_count description Microsoft cautions that querying sys.event_log can consume significant resources on logical servers with many databases or high login volumes, so keep the time range and other filters appropriately narrow. 6. Correlate SQL Evidence With Application Errors Now correlate the Azure SQL evidence with client telemetry. For every important application-side SQL exception, capture: UTC timestamp. SQL error number. Error message. Application instance. Operation. Retry attempt. Retry delay. Final outcome. Correlation or request ID when available. For example: UTC Layer Observation 10:00:02 Application SQL connection exception 10:00:04 Application Retry 1 fails 10:00:08 Application Retry 2 fails 10:00:15 SQL Connectivity succeeds 10:00:18 Application New connection succeeds 10:01:30 Application Queue still processing 10:05:00 Application Normal service restored Now you can separate: Connectivity interruption. Reconnection period. Application recovery period. That's substantially more useful than describing the full period as: "SQL was down." 7. Understand Connectivity and Reconfiguration Applications connect to the Azure SQL Database gateway, after which traffic is handled according to the effective connection policy and directed to the appropriate database environment. In a managed database service, applications must be designed for transient connectivity interruption. Microsoft specifically describes transient faults as conditions whose underlying cause resolves itself and notes that service reconfiguration can temporarily affect database connectivity. The application therefore shouldn't depend on one connection remaining valid indefinitely. Design for: Connections occasionally being terminated. New connection attempts occasionally failing. In-flight work requiring recovery. DNS and network paths needing to remain correctly configured. Authentication credentials or tokens remaining valid. Retry behavior that allows the system time to recover. Avoid guessing at undocumented platform internals when investigating an event. If evidence establishes only: "Connections failed briefly." Don't convert that into an unsupported explanation of why the platform changed state. 8. Retry Smartly, Not Aggressively Retry logic is essential for cloud applications that connect to Azure SQL Database. Microsoft explicitly recommends retry logic for transient connection failures. But a retry strategy needs boundaries. A good strategy considers: Number of attempts. Delay between attempts. Increasing delay. Random jitter. Maximum interval. Total retry budget. Whether the operation can safely be repeated. A bad strategy looks like: Failure ↓ Retry immediately ↓ Failure ↓ Retry immediately ↓ Failure ↓ Retry immediately... Across hundreds of application instances, this behavior can make recovery harder. A better pattern is: Failure ↓ Wait + jitter ↓ Retry ↓ Longer wait + jitter ↓ Retry ↓ Stop after bounded attempts 9. Use Microsoft.Data.SqlClient Retry Logic For .NET applications using Microsoft.Data.SqlClient, configurable retry logic is available in version 3.0 and later. Microsoft documents providers for configuring connection and command retries. A connection retry provider can be configured like this: var options = new SqlRetryLogicOption() { NumberOfTries = 5, DeltaTime = TimeSpan.FromSeconds(1), MaxTimeInterval = TimeSpan.FromSeconds(20) }; SqlRetryLogicBaseProvider provider = SqlConfigurableRetryFactory.CreateExponentialRetryProvider(options); using var connection = new SqlConnection(connectionString); connection.RetryLogicProvider = provider; connection.Open(); With this configuration: NumberOfTries = 5 Allows one initial attempt and up to four retries. DeltaTime Influences the delay between attempts. MaxTimeInterval Caps an individual retry delay. The exponential provider adds randomized jitter to retry intervals. The actual values should be tested against your application's: Latency requirements. Failure budget. Traffic volume. Concurrency. User experience. They're not universal service recommendations. 10. Connection Retry and Command Retry Are Different This is one of the most important resiliency distinctions. Suppose this fails: connection.Open(); Retrying the connection after an appropriate delay is generally straightforward for a transient connection condition. But suppose this fails: UPDATE dbo.Orders SET ProcessingStatus = N'Completed' WHERE OrderID = @OrderID; The application now has a harder question: Did the UPDATE execute before the connection was lost? Blindly repeating the command isn't always safe. Microsoft's Azure SQL guidance recommends establishing a fresh connection after a transient command failure before retrying, and retry logic must correctly account for the transaction's outcome. 11. Idempotency Matters Different operations require different retry treatment. Read-only operation For example: SELECT OrderID, ProcessingStatus FROM dbo.Orders WHERE OrderID = @OrderID; Repeating a read is often relatively straightforward, subject to your application's consistency requirements. Update operation For example: UPDATE dbo.Orders SET ProcessingStatus = N'Completed' WHERE OrderID = @OrderID; The client must determine whether repeating this operation is safe. Insert operation Consider: INSERT dbo.PaymentRequests ( RequestID, AccountID, Amount ) VALUES ( @RequestID, @AccountID, @Amount ); If the connection disappears after the server commits but before the client receives confirmation, an unconditional retry could create duplicate business activity unless the application and schema provide an idempotency mechanism. For example, a unique request identifier can make duplicate detection possible. The important principle is: Retryability is a business-operation property, not simply a SQL error property. 12. Transactions Need Special Treatment Be particularly careful with multi-statement transactions. Consider: BEGIN TRANSACTION; UPDATE dbo.Inventory SET Quantity = Quantity - @Quantity WHERE ProductID = @ProductID; INSERT dbo.ShipmentQueue ( RequestID, ProductID, Quantity ) VALUES ( @RequestID, @ProductID, @Quantity ); COMMIT TRANSACTION; If connectivity is interrupted, don't simply reconnect and retry the last statement. Your application needs a strategy that protects the integrity of the entire unit of work. Microsoft's retry documentation specifically cautions that if a transient failure invalidates an active transaction, the application should retry the entire transaction rather than only the failed statement. 13. Prevent the Retry Storm Imagine 500 application instances lose connectivity simultaneously. If every instance retries immediately: 500 requests fail ↓ 500 immediately retry ↓ Another synchronized wave ↓ Another synchronized wave Recovery traffic itself can become disruptive. Exponential backoff with jitter helps spread retries across time instead of concentrating them into synchronized bursts. The configurable retry provider in Microsoft.Data.SqlClient supports exponential retry behavior with randomized jitter. Also consider application-level resilience mechanisms such as: Circuit breakers. Back-pressure. Queueing. Controlled concurrency. Connection-pool recovery. Rate limiting. Load shedding where appropriate. The objective isn't: "Retry until SQL succeeds." It's: Recover gracefully without creating a second problem. 14. Don't Ignore the Network Path Not every SQL connection failure originates from the database. Azure SQL Database connectivity includes several components between the application and database, with connection behavior also influenced by the configured connection policy. Troubleshooting may need to include: DNS. Firewall configuration. Private endpoints. Network security rules. Client outbound connectivity. Connection policy. Authentication. Application host health. Azure SQL supports Redirect, Proxy, and Default connection-policy configurations depending on the environment and configuration. In Redirect mode, clients establish connections directly to the node hosting the database after the initial gateway interaction, which requires appropriate network access. This is another reason not to classify every connection exception as: "Azure SQL outage." 15. Test Resiliency Before an Incident Don't wait for a real platform event to discover whether your application's recovery logic works. Test controlled failure scenarios involving: New connections. Existing connections. Read-only requests. Writes. Multi-statement transactions. Multiple application instances. Queued workloads. Connection pools. Measure: Time to detect failure. First retry delay. Number of attempts. Time to establish a new connection. Failed user requests. Queue growth. Queue drain time. Full application recovery. The real success metric isn't merely: "Can I connect to the database again?" It's: "How quickly can the application return to normal service without compromising correctness?" 16. Write a Defensible Post-Incident Report A strong post-incident report separates facts, inferences, and unknowns. Confirmed facts Examples: Client connection failures were recorded during a specific UTC interval. Successful connections later resumed. Certain client instances continued failing after initial connectivity returned. Application backlog persisted beyond the connectivity event. Retry activity increased during recovery. Supported inference Example: Aggressive retry behavior likely contributed to slower application recovery. Use this classification only when application telemetry supports the relationship. Unknowns Examples: The exact internal platform operation responsible for a transient event. Whether an unrelated network event contributed. Whether a capacity change affected recovery. Don't fill gaps in telemetry with assumptions. 17. A Practical Decision Tree When an application reports an Azure SQL connectivity incident: 1. Is the database reachable now? No Check current Azure status and application connectivity. Validate networking, DNS, firewall, authentication, and Azure SQL availability. Yes Continue reconstructing the historical incident. 2. Did Azure SQL record connection failures? Yes Correlate: sys.event_log. sys.database_connection_stats. Application timestamps. Azure monitoring evidence. No or evidence isn't yet available Continue investigating application and network telemetry. Remember that historical connectivity data can arrive later. 3. Did the application retry? No Implement a bounded transient-fault handling strategy. Yes Evaluate: Retry count. Delay. Backoff. Jitter. Error classification. 4. Did many instances retry simultaneously? Yes Investigate: Retry synchronization. Circuit breaking. Back-pressure. Queue recovery. Connection pools. 5. Were writes or transactions interrupted? Yes Validate: Transaction outcome. Idempotency. Duplicate prevention. Whole-transaction retry behavior. 6. Did the application remain unhealthy after connections succeeded? Yes Measure application recovery separately from the original database connectivity event. Final Takeaway The hardest part of a transient Azure SQL event isn't always the interruption itself. Sometimes it's the application's response. A resilient architecture recognizes three separate timelines: Connectivity interruption Database/connectivity recovery Application recovery Azure SQL Database is designed as a cloud service where applications should expect and tolerate transient connectivity conditions. Microsoft recommends implementing retry logic rather than exposing appropriate transient failures directly to users. But resilient retry logic must be: Bounded. Delayed. Appropriately randomized. Error-aware. Transaction-aware. Idempotency-aware. Observable. And post-incident analysis should distinguish: What is confirmed. What is inferred. What remains unknown. Because: A brief database disconnect doesn't have to become a prolonged application outage. The objective isn't to design an application that never experiences a disconnected connection. The objective is to design an application that absorbs the interruption, recovers safely, and returns to normal operation predictably. Further reading Working with Transient Errors in Azure SQL Database Troubleshoot Common Connection Issues in Azure SQL Configure Retry Logic in Microsoft.Data.SqlClient Azure SQL Database Connectivity Architecture sys.event_log for Azure SQL Database sys.database_connection_stats for Azure SQL DatabaseThe Pipeline Is Slow, or Is SQL Waiting? Diagnosing Synapse-to-Azure SQL Regressions
A query can consume very little CPU and still keep a pipeline waiting for hours. A pipeline activity that normally finishes in minutes suddenly starts taking hours. Nothing obvious has changed: The pipeline definition looks the same. The stored procedure looks the same. Azure SQL Database isn't showing sustained high CPU. Pipeline monitoring still points toward the SQL activity. At this point, saying "SQL is slow" isn't enough. The request may actually be: Executing expensive work. Waiting for memory. Blocked by another transaction. Reading more data than before. Running with a different execution plan. Competing with concurrent pipeline activities. Experiencing different cardinality because of different parameter values. The key is to connect the pipeline invocation to the database execution and determine where elapsed time is really going. 1. Define the Regression Precisely Before troubleshooting SQL, establish what actually changed. Capture: Expected pipeline duration. Observed pipeline duration. Pipeline activity involved. Stored procedure or statement invoked. Exact parameter values. Affected and unaffected executions. Pipeline concurrency. Retry behavior. Connection and command timeout. Deployment or configuration changes. For example: Evidence Baseline Regression Pipeline duration Minutes Much longer Stored procedure Same Same Parameter range A B Concurrent activities Low Higher Azure SQL CPU Normal Normal/moderate SQL wait behavior Unknown To investigate The most important rule is: Don't assume the SQL workload is identical simply because the stored procedure is identical. Different parameters can result in dramatically different: Row counts. Join choices. Memory grants. Sort requirements. Execution plans. Total duration. 2. Preserve the Exact Invocation One of the easiest ways to lose a performance regression is to reproduce it with different parameters. Capture the invocation before testing: Procedure name. Parameter values. Date range. Entity or partition identifier. Batch size. Pipeline run ID. Retry number. Number of parallel activities. Relevant timeout configuration. Then sanitize any business-sensitive values before sharing them outside your organization. The diagnostic principle is simple: Reproduce the same workload before trying to improve the workload. 3. Reproduce Safely Whenever possible, reproduce the issue using: A nonproduction database containing representative data. A controlled production troubleshooting window. The same stored procedure. The same parameter pattern. Comparable concurrency. For a controlled test, SQL execution statistics can help: SET STATISTICS IO ON; SET STATISTICS TIME ON; EXEC dbo.SampleProcedure @StartDate = '2026-01-01', @EndDate = '2026-01-31', @EntityID = 100; SET STATISTICS TIME OFF; SET STATISTICS IO OFF; Replace these synthetic names and values with the sanitized equivalent of the invocation you're investigating. Capture: Elapsed time. CPU time. Logical reads. Physical reads when relevant. Execution plan. Returned row count. The goal isn't simply to reproduce "slow." The goal is to understand why it is slow. 4. First Question: Is SQL Running or Waiting? This distinction changes the entire investigation. During the slow execution, examine the active request. SELECT r.session_id, r.request_id, r.status, r.command, r.start_time, r.cpu_time, r.total_elapsed_time, r.wait_type, r.wait_time, r.last_wait_type, r.wait_resource, r.blocking_session_id, r.reads, r.writes, r.logical_reads, r.row_count FROM sys.dm_exec_requests AS r WHERE r.database_id = DB_ID() ORDER BY r.total_elapsed_time DESC; sys.dm_exec_requests is supported for Azure SQL Database and exposes information about currently executing requests. Pay particular attention to the relationship between: cpu_time and: total_elapsed_time Suppose a request has: Very high elapsed time. Relatively little accumulated CPU time. A significant current wait. That's a strong reason to investigate waiting, rather than immediately treating the problem as insufficient CPU. Conversely: High elapsed time. High accumulated CPU. High logical reads. points more strongly toward expensive execution. 5. Identify Blocking Another request may be preventing the pipeline's SQL request from progressing. Start with: SELECT session_id, status, wait_type, wait_time, wait_resource, blocking_session_id, cpu_time, total_elapsed_time FROM sys.dm_exec_requests WHERE blocking_session_id <> 0 ORDER BY wait_time DESC; Blocking can explain a scenario where: Pipeline duration increases dramatically. Database CPU remains moderate. The SQL activity appears "active" for a long time. Microsoft documents sys.dm_exec_requests as one of the tools for identifying requests suspended on lock-related waits and tracing blocking behavior in Azure SQL Database. Don't confuse: "The SQL call took two hours." with: "SQL executed for two hours." Those statements aren't necessarily equivalent. 6. Look at the SQL That's Actually Running During an active incident, retrieve the request and its current SQL text: SELECT r.session_id, r.status, r.command, r.cpu_time, r.total_elapsed_time, r.wait_type, r.blocking_session_id, r.logical_reads, txt.text AS batch_text FROM sys.dm_exec_requests AS r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS txt WHERE r.database_id = DB_ID() ORDER BY r.total_elapsed_time DESC; sys.dm_exec_sql_text accepts the sql_handle exposed by sys.dm_exec_requests and returns the associated SQL batch text. This helps validate an important cross-service question: Is the database executing the statement we think the pipeline invoked? 7. Investigate Memory Grants Some operations need execution memory before they can proceed efficiently. Common examples include: Sorts. Hash joins. Hash aggregates. Some index operations. sys.dm_exec_query_memory_grants exposes queries that: Requested a memory grant and received one. Requested a memory grant and are still waiting. It is supported for Azure SQL Database. SELECT mg.session_id, mg.request_id, mg.dop, mg.request_time, mg.grant_time, mg.requested_memory_kb, mg.granted_memory_kb, mg.required_memory_kb, mg.used_memory_kb, mg.max_used_memory_kb, mg.query_cost, mg.timeout_sec, mg.resource_semaphore_id FROM sys.dm_exec_query_memory_grants AS mg ORDER BY mg.requested_memory_kb DESC; Interpret the important columns carefully: requested_memory_kb Memory requested by the query. granted_memory_kb Memory actually granted. required_memory_kb Minimum memory required for execution. used_memory_kb Memory currently being used. max_used_memory_kb Maximum used so far. grant_time NULL when the requested grant hasn't yet been granted. Microsoft specifically documents that queries waiting for memory grants appear in this DMV, and that grant_time remains NULL until the grant is provided. 8. Find Waiting Memory Grants During the active regression, focus specifically on requests that haven't received their grants: SELECT mg.session_id, mg.request_id, mg.request_time, mg.grant_time, mg.requested_memory_kb, mg.required_memory_kb, mg.granted_memory_kb, mg.timeout_sec, mg.dop, mg.query_cost FROM sys.dm_exec_query_memory_grants AS mg WHERE mg.grant_time IS NULL ORDER BY mg.request_time; If the affected session repeatedly appears here, you now have meaningful evidence supporting a memory-grant contention hypothesis. But that's still not enough to state: "Memory grants caused the pipeline regression." You should also establish: Whether the waiting session is actually the pipeline request. How long it remained waiting. Whether other statements were consuming large grants. Whether concurrency changed. Whether the execution plan requested unusually large memory. Why the query requested that amount of memory. That last question is particularly important. Memory-grant problems can be downstream symptoms of: Poor cardinality estimates. Large sorts. Large hash joins. Plan changes. Data growth. Parameter sensitivity. Microsoft's memory-grant troubleshooting guidance emphasizes that operations such as sorts and hash operations can require execution-memory reservations and that these grants exist for the lifetime of the query. Memory-grant contention is a diagnostic hypothesis until the evidence supports causality. 9. Pipeline Concurrency Is Database Concurrency This connection is easy to overlook. Suppose one stored-procedure invocation performs acceptably. Now imagine the orchestration layer starts: 2 copies concurrently. 5 copies concurrently. 10 copies concurrently. 20 copies concurrently. Each execution may individually be reasonable. Together, they can compete for: CPU. Workers. Memory grants. Buffer pool resources. I/O. Locks. This means: A pipeline concurrency change is also a database workload change. Even when: SQL code hasn't changed. Database configuration hasn't changed. Stored procedure code hasn't changed. As part of troubleshooting, compare: Single-execution performance. Typical production concurrency. Regression-period concurrency. Temporarily reducing pipeline concurrency can also be a valuable diagnostic experiment, provided the change is safe for the workload. 10. Capture the Live Execution Plan For a request that's still executing, Azure SQL Database supports sys.dm_exec_query_statistics_xml(), which returns Showplan XML containing transient execution statistics for an in-flight request. First identify the target session: SELECT session_id, request_id, status, command, cpu_time, total_elapsed_time, wait_type, logical_reads FROM sys.dm_exec_requests WHERE database_id = DB_ID() ORDER BY total_elapsed_time DESC; Then, for a known session: DECLARE @SessionID smallint = 123; SELECT qsx.session_id, qsx.request_id, qsx.query_plan FROM sys.dm_exec_query_statistics_xml(@SessionID) AS qsx; Replace 123 with the session being investigated. The live plan can help answer: Which operator is consuming time? How many rows are flowing through operators? Are estimates diverging significantly from runtime behavior? Are large joins or sorts involved? Where does execution appear to be spending time? Microsoft describes access to execution-plan runtime information as particularly useful when investigating long-running queries, because the information can be inspected without waiting for the query to finish. Use live-plan collection purposefully, especially on heavily loaded production systems. 11. Compare Historical Behavior in Query Store A live snapshot explains what's happening now. Query Store helps answer: What was happening when the query used to be fast? For example, find queries with multiple retained Query Store plans: SELECT TOP (50) q.query_id, COUNT(DISTINCT p.plan_id) AS plan_count, COUNT(DISTINCT p.query_plan_hash) AS distinct_plan_shapes, MIN(p.first_execution_time) AS first_plan_execution, MAX(p.last_execution_time) AS last_plan_execution 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 HAVING COUNT(DISTINCT p.plan_id) > 1 ORDER BY plan_count DESC; Multiple plans aren't automatically evidence of a problem. Instead, investigate whether plan changes correlate with: Pipeline regressions. Duration changes. CPU changes. Logical-read changes. Memory behavior. Different parameter populations. 12. Compare Runtime Distributions, Not Just Averages Averages can hide regression patterns. Consider: Most executions take 10 seconds. A few take 45 minutes. The average won't fully describe the operational impact. For a known Query Store query_id, compare plans and runtime intervals: DECLARE @QueryID bigint = 123; SELECT p.plan_id, rsi.start_time, rsi.end_time, SUM(rs.count_executions) AS execution_count, CAST(AVG(rs.avg_duration) / 1000.0 AS decimal(18,2)) AS avg_duration_ms, CAST(MAX(rs.max_duration) / 1000.0 AS decimal(18,2)) AS max_duration_ms, CAST(AVG(rs.avg_cpu_time) / 1000.0 AS decimal(18,2)) AS avg_cpu_ms, CAST(AVG(rs.avg_logical_io_reads) AS decimal(18,2)) AS avg_logical_reads FROM sys.query_store_query AS q JOIN sys.query_store_plan AS p ON q.query_id = p.query_id JOIN sys.query_store_runtime_stats AS rs ON p.plan_id = rs.plan_id JOIN sys.query_store_runtime_stats_interval AS rsi ON rs.runtime_stats_interval_id = rsi.runtime_stats_interval_id WHERE q.query_id = @QueryID GROUP BY p.plan_id, rsi.start_time, rsi.end_time ORDER BY rsi.start_time DESC, p.plan_id; Replace 123 with the Query Store ID being investigated. Compare: Fast periods. Slow periods. Plans. Execution counts. CPU. Duration. Logical reads. This often produces a better picture than looking at lifetime averages. 13. Don't Force the Fastest Historical Plan Yet Suppose Query Store shows: Plan A took 30 seconds. Plan B takes 20 minutes. It might be tempting to immediately force Plan A. But first ask: How many times did Plan A execute? Which parameters used Plan A? Was the data distribution comparable? Was workload concurrency comparable? Did Plan A perform well repeatedly? Is the query parameter sensitive? A plan that was excellent for one parameter combination might be disastrous for another. The goal isn't to find the lowest historical duration. The goal is to find the plan behavior that's safe for the intended workload. If plan forcing is tested, define rollback criteria before deployment. 14. Test One Hypothesis at a Time Avoid simultaneously changing: Statistics. Indexes. Query code. Pipeline concurrency. Plan forcing. Compute tier. Doing everything together may improve performance, but you'll learn very little about why. Instead: Hypothesis: stale or inaccurate statistics Test: Appropriate statistics maintenance. Same parameters. Same concurrency. Then measure again. Hypothesis: memory-grant contention Test safely with: Reduced concurrency. Same parameter set. Same procedure. Same database configuration. Then compare: Grant wait behavior. Elapsed time. CPU time. Query plan. Hypothesis: plan regression Compare: Historical plans. Representative parameters. Runtime distributions. Only then evaluate targeted plan intervention. Hypothesis: poor cardinality Look for: Estimate-to-actual row differences. Parameter sensitivity. Implicit conversions. Sort/hash behavior. Excessive memory requests. This approach turns troubleshooting from guesswork into experimentation. 15. Correlation Is Not Root Cause This is perhaps the most important principle in cross-service troubleshooting. Suppose you see: A slow pipeline. A SQL memory-grant wait. Several concurrent activities. Those facts are correlated. They don't yet prove: "Memory contention caused the pipeline regression." A stronger conclusion requires a causal chain such as: The pipeline invokes the identified SQL request. That request repeatedly waits for execution memory during the regression. Competing queries hold substantial memory grants during the same period. Reducing the competing concurrency removes or materially reduces the grant wait. The same SQL invocation then returns toward its normal runtime. Repeating the workload reproduces the relationship. Now the evidence for causality is much stronger. This distinction matters because Azure SQL memory pressure can arise from several sources, including the chosen service objective, aggregate workload demand, and individual query memory demand. 16. Build a Cross-Service Evidence Package For difficult Synapse or Data Factory to Azure SQL regressions, both teams should be able to work from the same timeline. Capture: Pipeline evidence Pipeline run ID. Activity name. Activity start and end times. Sanitized parameters. Retry count. Timeout. Concurrent activities. Azure SQL evidence Session/request identification when captured live. Query Store query_id. Query Store plan_id. Runtime intervals. CPU time. Elapsed time. Logical reads. Wait type. Blocking session. Memory grant information. Execution plan. Investigation evidence Separate everything into: Facts Pipeline execution took longer than baseline. SQL request was identified. Request waited for memory. Multiple pipeline activities overlapped. A different plan appeared during the regression. Hypotheses Pipeline concurrency created memory-grant contention. Cardinality changes increased the requested memory grant. Parameter sensitivity caused a plan regression. Blocking contributed to runtime. Resource contention amplified an otherwise acceptable query. Experiments Document: What changed. Why it changed. Test duration. Result. Whether the hypothesis was strengthened or weakened. This prevents the pipeline and database teams from investigating two different incidents that simply happened at the same time. A Practical Decision Tree When a Synapse or Data Factory pipeline's Azure SQL activity suddenly becomes slow: 1. Can you identify the exact invocation? No Capture it before continuing. Yes Reproduce with equivalent parameters and concurrency. 2. Is SQL consuming significant CPU? Yes Investigate: Execution plan. Logical reads. Cardinality. Indexing. Parameter sensitivity. No Look at waits. 3. Is the request blocked? Yes Investigate: Blocking session. Transaction duration. Locking pattern. No Continue. 4. Is the request waiting for a memory grant? Yes Investigate: Requested versus granted memory. Other active grants. Sorts and hash operations. Cardinality estimates. Pipeline concurrency. No Investigate the actual current wait. 5. Did the execution plan change? Yes Compare: Runtime distributions. Parameter sets. CPU. Reads. Memory requirements. No Continue correlating waits, workload volume, and concurrency. 6. Does reducing concurrency improve the same invocation? Yes Resource competition becomes a stronger hypothesis. No Continue investigating query-specific behavior. Final Takeaway When a pipeline goes from minutes to hours, the fact that the Azure SQL activity is taking a long time doesn't automatically mean Azure SQL is spending all that time executing. The request might be spending much of its lifetime: Waiting for memory. Waiting behind another transaction. Waiting for another resource. Executing with a different plan. Processing a different parameter population. Competing with concurrent pipeline activities. A reliable investigation connects two perspectives: Pipeline context What exactly was invoked? With which parameters? At what time? At what concurrency? Database context Was the SQL request executing or waiting? How much CPU did it actually consume? What was it waiting for? Which plan did it use? How much memory did it request? What else was happening concurrently? sys.dm_exec_requests can identify active requests and their current execution state, while sys.dm_exec_query_memory_grants exposes statements that have requested or received execution memory. Azure SQL Database also supports obtaining transient plan information for in-flight requests through sys.dm_exec_query_statistics_xml(). Most importantly: Treat waits, memory grants, plan changes, and concurrency as evidence to test, not automatic root causes. That is how "the pipeline is slow" becomes a reproducible, evidence-backed performance diagnosis.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 practices
