Monitor, configure, and optimize database resources
High resource use can call for more resources or for query tuning
Suppose the CPU percentage chart of a database in Azure SQL Database sits near 100 percent every morning. Microsoft's monitoring guidance for Azure SQL Database and Azure SQL Managed Instance says that high CPU or IO utilization might indicate that your workload needs more resources, and might also indicate that queries need to be optimized, so the chart alone does not choose the fix. The fix follows from answering, in order: which resource boundary is busy, and since when?, where a boundary (what Monitor resource activity and performance calls the scope of a signal) is a database, an elastic pool (databases on a single server that share a set number of resources), a managed instance, or a virtual machine (VM) and its disks; which query is slow, and is it running or waiting?, the split that Microsoft's troubleshooting guidance for Azure SQL Database asks first, because different resolutions apply depending on the answer; and which change, at which scope, removes the cause?, where the scope of a change, the sense Configure database solutions for optimal performance uses, is the set of queries it reaches. The usual mix-up skips the middle question: the same guidance turns to resource limits when the query plan is optimal, and Microsoft's scaling guidance for Azure SQL offers more resources for performance issues due to increased usage that cannot be fixed using indexing or query rewrite methods.
One Microsoft DP-300 study guide heading for each question
Monitor, configure, and optimize database resources carries 20–25% of the DP-300 exam, and the Microsoft DP-300 study guide lists three headings under it, one per question. Monitor resource activity and performance answers which resource boundary is busy, and since when?, from 'Prepare an operational performance baseline' to 'Monitor by using Extended Events'. Its performance signal sources include Azure Monitor metrics, each a series of numeric values measured at regular time intervals, on which alerts send notifications when metric values indicate a potential problem; resource-statistics views such as sys.dm_db_resource_stats, dynamic management views (DMVs, views that return server state information) used to monitor resource usage at the database level in Azure SQL Database; database watcher, a preview service that collects in-depth workload monitoring data from Azure SQL Database and Azure SQL Managed Instance (it retires on April 30, 2027, and since November 9, 2026 you cannot create new watchers or edit existing ones); and Extended Events, an event architecture that enables users to collect as much or as little data as is necessary to monitor, identify, or troubleshoot performance. Monitor and optimize query performance answers which query is slow, and is it running or waiting?, from 'Configure Query Store' to 'Monitor by using Intelligent Insights'. Its query evidence comes from Query Store, which automatically captures a history of queries, plans, and runtime statistics, and retains these for your review, the DMVs that diagnose performance problems caused by blocked or long-running queries, resource bottlenecks, suboptimal query plans, and more, and Intelligent Insights, a preview feature that compares the database workload from the last hour with the past seven-day baseline workload; its fixes, the third question's narrowest answers, act on one query, such as plan forcing, in which Query Store directs the Query Processor to use a specific execution plan for a query. The study guide lists choosing such a fix under Monitor and optimize query performance, as in 'Recommend query construct modifications based on resource usage'; changes reaching more queries, up to 'Configure compute and storage resources for scaling', fall under Configure database solutions for optimal performance. Configure database solutions for optimal performance answers which change, at which scope, removes the cause?, from 'Implement index maintenance tasks' to 'Identify use cases for intelligent query processing (IQP) features'. Its changes include automatic tuning, which continuously monitors queries executed on a database, and automatically improves performance in Azure SQL Database and Azure SQL Managed Instance, Resource Governor, which enables you to manage Database Engine workloads and resources by specifying reservations and limits on resource consumption by requests, server and database settings, and scaling compute and storage; choosing a service tier or compute size for a deployment falls under 'Configure Azure SQL Database for scale and performance' in Configure resources for scale and performance, and scheduling maintenance jobs under 'Manage schedules for regular maintenance jobs' in Create and manage SQL Server Agent jobs. To place a task, ask whether it concerns which resource boundary is busy, and since when (such as preparing a baseline or reading metrics, alerts, resource-statistics views, database watcher, or Extended Events), then whether it concerns query evidence or a fix for one query, as the decision tree shows.
Choose the narrowest scope that covers the measured cause
When two changes both remove a cause, prefer the one whose scope reaches fewer queries. Parallelism shows the levels: in SQL Server, the max degree of parallelism (MAXDOP) server configuration option limits the number of processors employed to run a single statement, for each parallel plan execution, and that server value can be overridden at the query level with the MAXDOP query hint or Query Store hints, at the database level with the MAXDOP database scoped configuration, and at the workload level with the MAX_DOP option of a Resource Governor workload group; the query level is the fix for one query listed above under Monitor and optimize query performance. A workload group is a container for sessions that are classified in the same way, and each one is in a resource pool, a container for the physical resources of the server, such as CPU, memory, and I/O. Azure SQL Database has no workload level: there user configuration of resource pools and workload groups isn't supported. Scaling compute and storage is the widest change, reserved for issues that indexing or query rewrites cannot fix. After any change, re-measure the same workload: Microsoft's index maintenance strategy names Query Store as a good way to compare performance before and after maintenance, and automatic tuning operations are verified automatically, and reverted if there's no improvement.
Monitor, configure, and optimize database resources: questions, features, and platform limits
| Microsoft DP-300 study guide heading | Question it answers | Features include | Platform limit to check |
|---|---|---|---|
| Monitor resource activity and performance | Which resource boundary is busy, and since when? | Azure Monitor metrics and alerts, resource-statistics views (DMVs), database watcher, Extended Events | Database watcher (preview): Azure SQL Database and Azure SQL Managed Instance; no new or edited watchers since November 9, 2026; existing ones stop April 30, 2027 |
| Monitor and optimize query performance | Which query is slow, is it running or waiting, and which fix for that one query applies? | Query Store, plan forcing, DMVs for blocked or long-running queries, Intelligent Insights | Intelligent Insights (preview): Azure SQL Database and Azure SQL Managed Instance |
| Configure database solutions for optimal performance | Which change, at which scope, removes the cause? | Index maintenance, automatic tuning, MAXDOP settings, Resource Governor, compute and storage scaling | Resource Governor pools and workload groups: not user-configurable in Azure SQL Database |