ExamFinch.com My Study

Study guide

9.1 Configuring and Monitoring with Query Store

Practice this exam

DP-300: Administering Microsoft Azure SQL Solutions Exam

Track your progress as you read

Finished sections get a check mark.

1 Introduction & Exam Overview

1.1 Current DP-300 Exam Facts 1.2 Skills Outline and Domain Weights 1.3 Study Strategy and Official Resources

2 Planning and Deploying Azure SQL Solutions

2.1 Choosing and Deploying Azure SQL Database Offerings 2.2 Automated Deployment, Azure Arc, and SQL Database in Microsoft Fabric 2.3 Table Partitioning and Sharding Strategy 2.4 Hybrid SQL Server and IaaS Patching

3 Configuring Resources for Scale and Performance

3.1 Configuring Azure SQL Database for Scale and Performance 3.2 Configuring Azure SQL Managed Instance for Scale and Performance 3.3 Configuring SQL Server on Azure VMs for Scale and Performance 3.4 Configuring Table Partitioning and Data Compression

4 Planning and Implementing a Migration Strategy

4.1 Evaluating Migration Requirements and Strategies 4.2 Implementing Online, Offline, and Azure Migrations 4.3 Migrating Between Azure SQL Services and Troubleshooting

5 Configuring Database Authentication and Authorization

5.1 Microsoft Entra ID Authentication for Azure SQL 5.2 Authentication for SQL on Azure VMs and Managed Instance; Troubleshooting 5.3 Security Principals and Entra-Backed Users 5.4 Permissions, Least Privilege, and T-SQL Management

6 Implementing Security for Data at Rest and in Transit

6.1 Transparent Data Encryption and Object-Level Encryption 6.2 Always Encrypted and VBS Enclaves 6.3 Firewall Rules, Private Links, and Service Endpoints

7 Implementing Compliance Controls for Sensitive Data

7.1 Data Classification and Server/Database Auditing 7.2 Change Data Tracking and Dynamic Data Masking 7.3 Row-Level Security and Ledger in Azure SQL

8 Monitoring Resource Activity and Performance

8.1 Operational Baselines and Performance Metric Sources 8.2 Interpreting Metrics and Configuring Activity Monitoring 8.3 Monitoring with Database Watcher (Preview) 8.4 Monitoring with Extended Events

9 Monitoring and Optimizing Query Performance

9.1 Configuring and Monitoring with Query Store 9.2 Session Blocking and Dynamic Management Views 9.3 Index Changes and Execution Plan Review 9.4 Query Construct Modifications and Intelligent Insights

10 Configuring Database Solutions for Optimal Performance

10.1 Index, Statistics, and Integrity Maintenance 10.2 Automatic Tuning and Database-Scoped Configuration 10.3 Server Settings and Resource Governor 10.4 Compute/Storage Scaling and Intelligent Query Processing

11 Creating and Managing SQL Server Agent Jobs

11.1 Schedules for Regular Maintenance Jobs 11.2 Job Alerts, Notifications, and Agent Troubleshooting 11.3 Automating Deployment with ARM and Bicep Templates

12 Automating Deployments and Database Tasks in Azure

12.1 Azure PowerShell, Azure CLI, and Deployment Monitoring 12.2 Elastic Jobs for Cross-Database Automation 12.3 Azure Automation Runbooks, Alerts, and Troubleshooting Automated Tasks

13 Planning an HA/DR Strategy and Backup and Restore

13.1 Recommending HA/DR Strategy from RPO and RTO 13.2 Hybrid and Azure-Specific HA/DR Solutions and Testing 13.3 Backup and Restore Strategy with Native Tools 13.4 Point-in-Time Restore and Recovery Operations

14 Configuring HA/DR: Long-Term Retention, Geo-Replication, and Failover Groups

14.1 Long-Term Retention, T-SQL Backup/Restore, and Cloud Storage 14.2 Active Geo-Replication 14.3 Failover Groups

15 Advanced HA/DR: Availability Groups, FCIs, Log Shipping, and Troubleshooting

15.1 Always On Availability Groups on SQL Managed Instance and Azure VMs 15.2 Always On Failover Cluster Instances and Log Shipping 15.3 Monitoring and Troubleshooting HA/DR Solutions

9.1 Configuring and Monitoring with Query Store

Key Takeaways Explain This Topic AI

Query Store is a database-scoped flight-data recorder that captures query text, execution plans, runtime statistics, and wait stats per plan, enabling time-based regression analysis that the plan cache alone cannot provide

Operation mode Read Write is required for capture; capture mode Auto skips small/no-op queries, All captures everything, None freezes capture while keeping reporting - use custom capture policies for fine-grained control

Size-based cleanup, stale query threshold, and query capture mode together bound Query Store growth; defaults differ by platform and Azure SQL Database enables Query Store on by default with automatic plan correction

Catalog views sys.query_store_query, sys.query_store_plan, and sys.query_store_runtime_stats join on query_id and plan_id to expose top resource consumers, regressed queries, and forced plans

Forcing a plan pins a chosen shape for a query; automatic plan correction promotes last known good plans and unforces bad ones, working only when Query Store is enabled

Last updated: August 2026

Why Query Store Matters for DP-300

The plan cache is volatile and memory-only: when SQL Server restarts, memory pressure evicts plans, or plans get recompiled, the history of what ran well and what regressed is gone. Query Store (sometimes written QS ) solves this by persisting query performance data in the user database as system tables, behaving like a flight-data recorder for query workloads. For the DP-300 exam, Query Store is the single most important tool in the "Monitor, configure, and optimize" domain because nearly every regression investigation starts with it, and on Azure SQL Database it powers automatic plan correction .

Query Store captures four classes of information per query and per plan:

Captured artifact Stored in What it tells you

Query text (and parameterized shape) sys.query_store_query The SQL string, object name, and the query hash used to group identical shapes

Execution plans sys.query_store_plan One row per compiled plan; includes plan XML, is_forced_plan, and the compilation time

Runtime execution statistics sys.query_store_runtime_stats CPU, logical reads, duration, row counts, memory, and DOP aggregated per interval

Wait stats per plan sys.query_store_wait_stats Which wait types (lock, latch, I/O, CPU) dominated each plan's executions

Because Query Store separates runtime stats per plan, you can see that the same query had a cheap plan last week and an expensive plan today - something the live plan cache, which only keeps the current plan, cannot show.

Enabling and Configuring Query Store

Query Store is a database-scoped feature enabled with ALTER DATABASE ... SET QUERY_STORE = ON . On Azure SQL Database it is enabled by default for new databases with automatic plan correction also enabled; on Azure SQL Managed Instance and SQL Server (2016+) it is off by default and you enable it explicitly. Enable Query Store before you need it - it cannot backfill history that was never captured.

The configuration knobs live under ALTER DATABASE ... SET QUERY_STORE = (...) and map to several categories:

Operation mode : READ_WRITE (capture and persist) is the default when Query Store is enabled; READ_ONLY freezes capture but keeps reporting on existing data. Use READ_ONLY deliberately when you want to observe without growing the store.

Capture mode controls which queries get recorded: AUTO (default) skips trivial, small, and no-op queries to save space; ALL captures every query batch; NONE pauses capture while keeping history available for reporting. CUSTOM unlocks granular predicates via custom capture policies .

Size-based cleanup policy : SIZE_BASED_CLEANUP_MODE = AUTO purges the oldest data when Query Store approaches MAX_STORAGE_MB (default 100 MB on SQL Server; larger defaults on Azure SQL DB). Disable cleanup only for short diagnostic windows.

Stale query threshold ( STALE_QUERY_THRESHOLD_DAYS , default 30 on SQL Server, 90 on Azure SQL DB): queries with no activity for this many days are eligible for cleanup.

Statistics collection interval ( DATA_FLUSH_INTERVAL_SECONDS , default 15 minutes): how often runtime stats are flushed from memory to disk; shorter intervals reduce data loss on crash but add I/O.

Query capture mode also includes the modern CUSTOM mode, which exposes capture_policy with predicates like EXECUTION_COUNT , TOTAL_COMPILE_CPU_TIME_MS , TOTAL_LOGICAL_READS_MB , and WAIT_TIME_MS thresholds so you can capture only queries exceeding a cost bar - useful on busy warehouses where AUTO still floods the store.

A classic configuration trap: leaving MAX_STORAGE_MB too small on a busy OLTP database causes size-based cleanup to churn, evicting the very history you need for regression analysis. Microsoft guidance is to size Query Store so that cleanup is rare, then monitor sys.database_query_store_options to confirm actual_state_desc stays READ_WRITE and readonly_reason is 0.

Querying Query Store Catalog Views

The core catalog views join on query_id and plan_id . The three most heavily tested: -- Top 10 queries by average CPU in the last 7 days

  • SELECT TOP (10)
  • q.query_id, q.query_hash, qt.query_sql_text,
  • rs.avg_cpu_time, rs.avg_duration, rs.avg_logical_io_reads, rs.count_executions
  • FROM sys.query_store_runtime_stats rs
  • JOIN sys.query_store_plan p ON rs.plan_id = p.plan_id
  • JOIN sys.query_store_query q ON p.query_id = q.query_id
  • JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
  • WHERE rs.last_execution_time > DATEADD(day, -7, SYSUTCDATETIME())
  • ORDER BY rs.avg_cpu_time DESC;

Other catalog views to know: sys.query_store_query_variant (for the plan variants produced by Parameter Sensitive Plan optimization), sys.query_store_replica_specs (for readablesecondary stats), and sys.query_store_wait_stats for wait breakdowns. The built-in Query Store reports in SSMS and Azure portal wrap these views: Top Resource Consuming Queries , Regressed Queries , Queries with Forced Plans , Queries with High Wait Time , and Tracked Queries .

Forced Plans and Automatic Plan Correction

When the optimizer picks a bad plan, you can pin a known good plan with sp_query_store_force_plan @query_id, @plan_id . A forced plan is one that the optimizer must use regardless of new statistics or parameter values - useful for parameter-sensitivity regressions. Force the plan only after confirming it is stable across parameter distributions; a forced plan that was good for one parameter set can backfire for others.

Automatic plan correction is the cloud-native extension: on Azure SQL Database and SQL Managed Instance, the engine watches Query Store data and, when it detects that the last forced good plan outperforms the current plan, automatically forces it via sp_query_store_force_plan and records the action. If the forced plan later regresses, automatic tuning unforces it. The verification is continuous - you see these as actions in sys.dm_db_tuning_recommendations with status Pending, Executing, Verifying, Success, Reverted, or Error. You can also script a recommendation manually with the T-SQL it emits.

Interplay points the exam likes: Query Store must be ON and READ_WRITE for automatic plan correction to work; forcing is per query, not per database; forcing does not change the plan the optimizer generates, only the one it uses; and a forced plan survives statistics updates but is removed if the underlying schema changes. On SQL Server on Azure VMs, automatic plan correction is also available when Query Store is enabled, but you must opt in via database-scoped configuration AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON) .

Platform Differences at a Glance

Azure SQL Database : Query Store ON by default, automatic plan correction ON by default, larger default storage and stale threshold, captures readable-secondary stats.

Azure SQL Managed Instance : off by default; once enabled behaves like SQL Server but supports automatic plan correction via database-scoped config.

SQL Server (on Azure VMs and on-premises) : off by default in 2016-2019, ON by default in 2022+ for new databases; automatic plan correction requires SQL 2017+ and explicit opt-in.

The exam frames Query Store as the gateway to automatic tuning: a scenario describing intermittent query regression on Azure SQL Database almost always resolves to confirming Query Store is enabled and automatic plan correction is on, then inspecting sys.dm_db_tuning_recommendations .