Monitor, configure, and optimize database resources

A query on an Azure SQL Database joins a large orders table to a filtered set of customers. The number of qualifying customer rows varies dramatically between executions depending on the WHERE predicate values. You observe that some executions use a nested loops join (fast for small inputs) while others suffer badly because a nested loops plan was cached and reused when the input was actually large. The database is on a recent compatibility level. Which Intelligent Query Processing feature will let the engine defer the choice of physical join type until the actual number of rows is known at runtime?

You manage an Azure SQL Database supporting a high-concurrency OLTP application. Users intermittently report query timeouts that coincide with statistics being updated. Investigation shows queries are waiting during query compilation because the optimizer triggers a synchronous statistics update on a large table before the plan is generated. You want to reduce this compilation-time blocking without disabling automatic statistics updates. Which database setting change should you make?

You manage an Azure SQL Database supporting an OLTP application. Over the past weeks, several queries have degraded because commonly filtered columns lack supporting indexes, while other unused indexes consume write overhead. Your team wants Azure SQL to automatically create beneficial indexes and drop redundant ones, but only after validating that each change actually improves performance—and to revert changes that cause regressions. Which configuration should you enable?

You manage an Azure SQL Database supporting an OLTP application. Users intermittently report slow response times after application deployments and statistics updates. Investigation shows that certain queries suddenly choose worse execution plans, causing CPU spikes that resolve on their own hours later. You want Azure to automatically detect these plan regressions and revert to the previously well-performing plan without manual intervention. Which configuration should you enable?

An Azure SQL Database (compatibility level 150) hosts a large rowstore-only reporting table. Analysts run heavy aggregation queries (SUM, GROUP BY) over millions of rows, and execution plans show these queries process rows one at a time, consuming significant CPU. There are no columnstore indexes, and business rules prohibit adding them. Which Intelligent Query Processing feature is designed to improve these CPU-bound analytic queries without a columnstore index?

You manage a fleet of 12 Azure SQL databases and one Azure SQL Managed Instance for a retail company. Leadership wants a centralized, low-overhead monitoring solution that collects detailed performance data (query statistics, wait statistics, and resource utilization) across all these resources, stores the data for historical trend analysis, and integrates with Azure dashboards without requiring you to deploy or maintain agents on each database. Which Azure feature should you implement?

You manage a 6 TB SQL Server database on an Azure VM. The nightly maintenance window is short, and a full DBCC CHECKDB currently exceeds the window, causing significant tempdb pressure and I/O contention with backups. You must still detect the most common forms of on-disk corruption (torn pages, checksum failures) within the available window. Which approach best meets these requirements?

Users report that an Azure SQL Database is intermittently unresponsive during business hours. You suspect blocking. You need to identify the root blocking session (the head of the blocking chain) and the query it is running, using the least effort. Which approach should you use?

You administer an Azure SQL Managed Instance running an OLTP application. Monitoring shows the plan cache is consuming a large amount of memory, and DMV queries reveal thousands of single-use (adhoc) compiled plans, each with a usecounts value of 1. Available memory for the buffer pool is being squeezed, causing increased physical reads. You want to reduce the memory wasted by these single-use plans with minimal application changes. Which configuration should you apply?

You manage an Azure SQL Database supporting a nightly ETL process that bulk-loads several million rows into a large staging table before it is queried by reporting jobs. Operators report that queries running immediately after the load sometimes choose poor execution plans with severely underestimated row counts, even though AUTO_UPDATE_STATISTICS is enabled. You confirm that the statistics update threshold has not yet been crossed after the bulk load. Which action best ensures the optimizer has accurate cardinality estimates for the reporting queries?

You manage an Azure SQL Database supporting a line-of-business application. Users report intermittent slowness during business hours, but performance seems normal when you check it. Before making any configuration or index changes, your manager asks you to quantify what 'normal' performance looks like over a two-week period so future changes can be measured against it. Which approach best establishes this reference for comparison?

A production Azure SQL Database intermittently reports transaction rollbacks that users describe as 'the app just freezes and then errors out.' You suspect deadlocks but the errors are sporadic and you cannot reproduce them on demand. You need to capture the full deadlock graph, including the resource and process lists, with the least ongoing overhead so you can analyze the victim and surviving transactions after the fact. Which approach should you use?

You manage an Azure SQL Database and users report intermittent slowness during business hours. You need to capture the exact T-SQL text and duration of statements that run longer than 5 seconds, storing the collected data for later analysis with minimal performance overhead. Which approach should you use?

You manage several Azure SQL databases. Application owners report intermittent slowdowns, but by the time you investigate, performance has returned to normal. You want an automated capability that continuously analyzes each database's workload, detects performance degradation issues, identifies root causes such as resource limits or plan regressions, and generates diagnostic findings you can review after the fact. Which feature should you use?

You manage an Azure SQL Database at compatibility level 160. A reporting query intermittently spills large sorts to tempdb because SQL Server overestimates the memory grant on some executions and underestimates it on others, causing either wasted memory or disk spills. You want the engine to automatically adjust the memory grant over successive executions based on actual runtime usage, without rewriting the query or forcing plans. Which feature should you rely on?

You administer an Azure SQL Database supporting an OLTP application. Users report that several reporting queries have become slow. You want to identify which missing index would provide the greatest overall performance benefit before making any changes, using built-in dynamic management views. Which combination of DMVs should you query, and how should you prioritize the results?

You manage an Azure SQL Database at compatibility level 160. A frequently executed stored procedure filters an Orders table by CustomerId. A few customers have millions of rows while most have only a handful. Users report that the procedure sometimes runs fast and sometimes very slowly depending on which plan happens to be cached. You want the engine to automatically maintain multiple cached plans suited to different predicate value distributions without you having to force plans or rewrite the query. Which capability addresses this?

You manage an Azure SQL Database that serves an application generating thousands of unique ad-hoc queries per hour, most of which execute only once. After enabling Query Store with the default configuration, you notice the Query Store storage is filling rapidly and frequently switching to READ_ONLY mode, causing you to lose telemetry for the important, frequently-executed queries. You want to reduce the noise from single-use queries while continuing to capture meaningful, resource-intensive queries. What should you configure?

You manage an Azure SQL Database. A critical reporting query intermittently uses a suboptimal execution plan after statistics updates, causing timeouts. You have identified in Query Store that one specific plan consistently performs well. You need the least intrusive way to ensure the query always uses the good plan without modifying application code or the query text. What should you do?

A stored procedure on an Azure SQL Database has been running quickly for months, but after a recent statistics update it intermittently runs 20x slower depending on which parameter value is passed first. Using Query Store, you confirm the procedure has two execution plans: an older plan that performs well for all parameter values and a newer plan that only performs well for a narrow range. You need an immediate mitigation that keeps the good plan in use while you investigate a permanent fix, without changing application code. What should you do?