Monitor, configure and optimize database resources

Monitoring resource activity and performance

Baseline and metric sources

A baseline is a record of normal behaviour (CPU, IO, memory, waits, query durations, connection counts) at known load. Without it, you can’t tell if a number is bad. Capture it over a representative period, store it (Log Analytics, a table, Query Store), and compare after changes such as migration, tier change or upgrade.

Metrics to know: CPU percentage (compute pressure), Data IO / Log IO percentage (storage throttling), Workers percentage (request concurrency limit), Sessions percentage, tempdb and Storage percentage. When any approaches 100% in the tier’s limits you either tune queries or scale the tier.

Configure and monitor activity

• Create alert rules on metrics (static or dynamic threshold) with action groups (email, SMS, webhook, Logic App, runbook).
• In SSMS use Activity Monitor (VM/MI), Performance Dashboard, and Query Performance Insight (SQL Database portal) for top CPU, duration and execution count queries, built on Query Store.
• Log Analytics with KQL against diagnostic logs for correlation. A Workbook can show multiple resources.

Database watcher

Database watcher collects detailed performance and configuration data from SQL targets without agents on the database and stores it in an Azure Data Explorer cluster or Real-Time Analytics (Fabric) database. Key facts:

• A watcher has a managed identity, a data store, and a list of targets (SQL Database, elastic pool, MI, or SQL Server on VM through Arc). The identity needs login and permissions on targets and ingestion rights on the store.
• Uses private endpoints if the target or store has no public access.
• Provides estate-wide dashboard (health, resource use) and detailed dashboards (queries, waits, blocking, storage, backups, replicas, configuration) and sample collection for current activity with per-minute granularity.
• Use it for fleet-level near real-time monitoring with long retention and KQL. For classic deep, per-database tuning, Query Store is the tool.

Extended Events (XEvents)

Extended Events is the lightweight tracing system replacing SQL Trace/Profiler (deprecated). Components: session, events, actions, predicates (filters), targets.

• SQL Database/MI sessions are database-scoped (SQL Database) or server-scoped (MI, VM). Targets: ring_buffer, event_file (in Azure Blob storage for PaaS), event_counter, histogram.
• Typical sessions: deadlock graph (xml_deadlock_report; system_health already captures it on VM/MI), slow queries (sql_statement_completed with duration predicate), wait_info, blocked_process_report.
• Create in SSMS with the session wizard, or CREATE EVENT SESSION ... ON DATABASE. View live data in Watch Live Data or read files with sys.fn_xe_file_target_read_file.

Monitoring and optimizing query performance

Query Store

Query Store is a built-in flight recorder for queries: query text, every plan, and aggregated runtime statistics per time interval. It survives restart and failover.

• On by default in Azure SQL Database and MI; off by default for SQL Server 2019 and earlier (on by default for new databases from 2022). Enable on VM: ALTER DATABASE db SET QUERY_STORE = ON;.
• Configure: OPERATION_MODE = READ_WRITE, QUERY_CAPTURE_MODE (AUTO ignores trivial queries; ALL for baseline), MAX_STORAGE_SIZE_MB, DATA_FLUSH_INTERVAL_SECONDS, INTERVAL_LENGTH_MINUTES, STALE_QUERY_THRESHOLD_DAYS, SIZE_BASED_CLEANUP_MODE. A full store goes READ_ONLY, so monitor its size. Wait statistics capture is available too, and Query Store on readable secondaries is supported on newer builds.
• Reports in SSMS: Regressed Queries, Overall Resource Consumption, Top Resource Consuming Queries, Queries With Forced Plans, Queries With High Variation, Tracked Queries, Query Wait Statistics.
• Force plan (sp_query_store_force_plan) pins a plan when a plan regression occurs, for example after a compatibility-level upgrade. Query Store hints (sp_query_store_set_hints) add query hints without changing code.
• Compare before/after using Query Store when upgrading the compatibility level; this is the intended workflow for Query Tuning Assistant.

Blocking and deadlocks

Blocking: one session holds a lock another needs; normal and temporary. Deadlock: circular wait, the engine kills a victim (error 1205). Find blocking:

SELECT r.session_id, r.blocking_session_id, r.wait_type, r.wait_time, t.text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.blocking_session_id <> 0;

Follow blocking_session_id to the head blocker (often an open transaction, sys.dm_tran_active_transactions, sys.dm_exec_sessions with open_transaction_count > 0). Resolve by: committing/rolling back, KILL session_id, shortening transactions, adding indexes that reduce scan locks, using Read Committed Snapshot Isolation (RCSI) (default on in Azure SQL Database) or snapshot isolation, and consistent object access order for deadlocks. Accelerated database recovery (ADR) reduces long rollbacks and is on in Azure SQL. Deadlock graphs come from Extended Events or Query Store/diagnostic logs.

DMVs

Common waits: PAGEIOLATCH_* (reading data from disk: IO or memory), LCK_M_* (blocking), CXPACKET/CXCONSUMER (parallelism), SOS_SCHEDULER_YIELD (CPU pressure), WRITELOG (log flush, log IO limit), RESOURCE_SEMAPHORE (memory grants), ASYNC_NETWORK_IO (client slow to consume).

Indexes and query shape

• Index types: clustered, nonclustered, covering (INCLUDE columns), filtered, columnstore (analytics), unique, XML, spatial. Rowstore for OLTP, columnstore for scans and aggregation.
• Missing index DMVs and the Database Advisor (SQL Database) propose indexes. Validate before creating. Don’t create duplicates. Remove unused/duplicate indexes (dm_db_index_usage_stats shows no reads) because they slow writes.
• Key column order: equality columns first, then range column; put selected extras in INCLUDE.
• Query construct modifications driven by resource use: avoid non-SARGable predicates (functions on columns such as WHERE YEAR(d)=2026, implicit conversions from mismatched types), SELECT *, scalar UDF in large selects (inlined in 2019+), row-by-row cursors vs set-based, parameter sniffing issues (use OPTION (RECOMPILE), OPTIMIZE FOR, Parameter Sensitive Plan optimization, or Query Store hints), OR to UNION ALL, and NOT IN to NOT EXISTS. Reduce returned rows and batch large modifications.

Execution plans

Estimated plan (no run) vs actual plan (with row counts) vs live statistics. Look for:

• Scans where seeks were expected, Key Lookups (add covering include), Sort/Hash spills (tempdb warnings, bad memory grants), estimated vs actual row differences (stale statistics, parameter sniffing), thick arrows, parallelism skew, implicit conversion warnings, missing index hints.
• Operators: Index Seek/Scan, Nested Loops (small input), Hash Match (large unsorted), Merge Join (sorted), Stream Aggregate.
• Tools: SSMS plan viewer and Compare Showplan; SET STATISTICS IO, TIME ON; Query Store plan view; sys.dm_exec_query_plan_stats (last actual plan) with LAST_QUERY_PLAN_STATS.

Intelligent Insights

Intelligent Insights (SQL Database and elastic pools only, not Managed Instance) uses AI over Query Store data to detect performance degradation patterns (reaching resource limits, workload increase, memory pressure, locking, increased MAXDOP, pagelatch contention, missing index, new query, increased wait statistics, tempdb contention, elastic pool DTU shortage, plan regression, database-scoped configuration value change, slow client, pricing tier downgrade) and writes a JSON diagnostics log (the SQLInsights category) with root-cause analysis and recommendations. Send it to Log Analytics, Event Hubs or Storage. Enable via diagnostic settings.

Configuring for optimal performance

Index and statistics maintenance

• Fragmentation thresholds (from sys.dm_db_index_physical_stats): under 5% nothing; 5-30% REORGANIZE (online, always); over 30% REBUILD (can be ONLINE = ON, resumable on PaaS). Only matter for indexes over about 1,000 pages. On SSD storage fragmentation matters far less. Page density also matters.
• Statistics drive the optimizer. AUTO_UPDATE_STATISTICS is on by default; AUTO_UPDATE_STATISTICS_ASYNC avoids blocking queries; UPDATE STATISTICS ... WITH FULLSCAN or sp_updatestats for refresh after bulk loads; incremental stats on partitioned tables. Rebuild index refreshes the index’s stats with a full scan.
• Schedule with SQL Agent (MI, VM), elastic jobs or Azure Automation (SQL Database). Ola Hallengren scripts are a common option.

Integrity checks

DBCC CHECKDB verifies allocation and structural integrity. Azure SQL Database and MI run integrity checks and page-verify automatically, but running DBCC CHECKDB yourself is still allowed. On VMs, schedule it (weekly, on a copy or secondary, or PHYSICAL_ONLY for lighter runs). Page verification is CHECKSUM. Repair options: restore from backup is preferred; REPAIR_ALLOW_DATA_LOSS last resort.

Automatic tuning

The engine detects a regression, forces the last good plan, validates, and reverts if it didn’t help. Index actions are validated and rolled back if performance worsens. Inherit settings from the server or set per database. Check sys.dm_db_tuning_recommendations. Enable on SQL Server with ALTER DATABASE ... SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON).

Server and database settings

• MAXDOP (max degree of parallelism): on SQL Server, set around cores per NUMA node up to 8. In Azure SQL Database it’s a database-scoped configuration (default 8; set it per database).
• Cost threshold for parallelism (default 5; raise to 25-50 on modern OLTP) on VM/MI.
• Max server memory: leave memory for the OS on VMs.
• Optimize for ad hoc workloads to reduce plan cache bloat. Tempdb: multiple equal-sized data files on VMs.
• Compatibility level controls optimizer behaviour. New databases use the latest; test lower level for legacy apps and raise with Query Store.

Resource Governor

Resource Governor (SQL Server VM, Managed Instance; not SQL Database) limits CPU, memory, and IO per workload. Parts: resource pools (min/max CPU, memory, IOPS), workload groups (importance, request limits, MAXDOP, mapped to a pool), and a classifier function (in master) that routes sessions based on login, app name, host. ALTER RESOURCE GOVERNOR RECONFIGURE applies. Use to stop a reporting user or batch job from starving OLTP. In SQL Database the equivalent is tier/elastic pool limits and workload groups internally governed by the service.

Database-scoped configuration

ALTER DATABASE SCOPED CONFIGURATION SET ... changes behaviours per database, allowing different settings in an elastic pool or per tenant: MAXDOP, LEGACY_CARDINALITY_ESTIMATION, PARAMETER_SNIFFING, QUERY_OPTIMIZER_HOTFIXES, LAST_QUERY_PLAN_STATS, OPTIMIZE_FOR_AD_HOC_WORKLOADS, ELEVATE_ONLINE, ELEVATE_RESUMABLE, ACCELERATED_PLAN_FORCING. It can also be applied to secondaries (FOR SECONDARY). The usual answer for “change MAXDOP/CE for one database only in Azure SQL Database”.

Scale compute and storage

• Database: scale tier/vCores/storage online (DTU → vCore), add read replicas, use serverless for spiky, Hyperscale for storage-growth.
• MI: scale vCores and storage. VM: bigger VM size, more disks, tempdb on local SSD, read-only caching on data disks.
• Know symptoms: CPU 100% = tune queries or scale vCores; log IO % 100% = batch writes, minimal logging, scale; workers % = fix blocking and connection spikes; storage = grow max size or compress; tempdb full = tune sort/spills.

Intelligent query processing (IQP)

IQP features improve performance without code changes, mostly by raising the compatibility level.

Memory grant feedback fixes spills or oversized grants; PSP fixes parameter sniffing. Use the latest compatibility level for new work and use Query Store to protect against regressions.