Monitor and optimize query performance
Unlock the complete study guide + 25 practice exams totaling 1,500 questions.
Bundled into the existing Administering Microsoft Azure SQL Solutions premium course — no separate purchase.
14-day refund guarantee on one-time purchases and your first subscription charge.
Included in this chapter:
- Running or waiting: the diagnostic model
- Configuring Query Store collection and cleanup
- Monitoring with Query Store reports and catalog views
- Query Performance Insight in the Azure portal
- Plan forcing and Query Store hints
- Blocking chains and the head blocker
- Isolation levels and row versioning
- Accelerated database recovery and version cleanup
- Optimized locking
- Deadlocks: victim, cycle and prevention
- Wait statistics: scope, deltas and wait types
- Cached query statistics in sys.dm_exec_query_stats
- Memory grants: waiting requests and grant holders
- File I/O stalls: data files versus log files
- tempdb and page-latch contention
- DMV permissions by view and platform
- Execution plan evidence: estimated, actual, live
- Reading plan operators: access paths and joins
- Rowstore index structure and key choice
- Specialized indexes: filtered, columnstore, indexed views
- Missing-index suggestions are candidates
- Query construct rewrites that preserve results
- Plan reuse: parameterization versus recompilation
- Query memory grant hints
- Intelligent Insights and the SQLInsights log
- Matching query performance symptoms to tools and fixes
Query Store vs DMVs vs Query Performance Insight vs Intelligent Insights
| Question | Query Store | DMVs | Query Performance Insight | Intelligent Insights |
|---|---|---|---|---|
| What it shows | Query texts, plans, runtime and wait statistics per interval | Current requests, sessions, locks and waits; cache-lifetime query totals | Top CPU, duration and execution-count queries | Detected degradations with root cause analysis |
| Time horizon | Retained history; data kept 30 days by default | Now, or since the plan was cached or counters reset | Captured Query Store history; needs a couple of hours of data | Last hour compared with a seven-day baseline |
| Where it runs | SQL Server, Azure SQL Database, Azure SQL Managed Instance | SQL Server, Azure SQL Database, Azure SQL Managed Instance | Azure SQL Database single and pooled databases | Azure SQL Database and Azure SQL Managed Instance (preview) |
| How you read it | SSMS reports or Query Store catalog views | T-SQL queries against the views | Azure portal | SQLInsights resource log through diagnostic settings |
| Typical question | Which plan changed during last night's slowdown? | Who is blocking whom right now? | Which queries used the most CPU this week? | Has the workload degraded against normal, and why? |
Decision tree
Cheat sheet
Unlock with Premium — includes all practice exams and the complete study guide.
References
- https://learn.microsoft.com/en-us/azure/azure-sql/database/identify-query-performance-issues
- https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/performance/troubleshoot-memory-grant-issues
- https://learn.microsoft.com/en-us/azure/azure-sql/database/monitor-tune-overview
- https://learn.microsoft.com/en-us/azure/azure-sql/database/intelligent-insights-overview
- https://learn.microsoft.com/en-us/sql/relational-databases/performance/monitoring-performance-by-using-the-query-store
- https://learn.microsoft.com/en-us/sql/relational-databases/performance/manage-the-query-store
- https://learn.microsoft.com/en-us/azure/azure-sql/database/query-performance-insight-use
- https://learn.microsoft.com/en-us/azure/azure-sql/database/understand-resolve-blocking
- https://learn.microsoft.com/en-us/sql/relational-databases/performance/best-practice-with-the-query-store
- https://learn.microsoft.com/en-us/credentials/certifications/resources/study-guides/dp-300
- https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-set-options
- https://learn.microsoft.com/en-us/sql/relational-databases/extended-events/extended-events
- https://learn.microsoft.com/en-us/sql/relational-databases/performance/query-store-usage-scenarios
- https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-query-store-runtime-stats-transact-sql
- https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-query-store-wait-stats-transact-sql
- https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-query-store-runtime-stats-interval-transact-sql
- https://learn.microsoft.com/en-us/sql/relational-databases/performance/query-store-for-secondary-replicas
- https://learn.microsoft.com/en-us/azure/azure-sql/database/automatic-tuning-overview
- https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-query-store-force-plan-transact-sql
- https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-query-store-plan-transact-sql
- https://learn.microsoft.com/en-us/sql/relational-databases/performance/query-store-hints
- https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-requests-transact-sql
- https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-tran-locks-transact-sql
- https://learn.microsoft.com/en-us/azure/azure-sql/database/monitoring-with-dmvs
- https://learn.microsoft.com/en-us/sql/relational-databases/accelerated-database-recovery-concepts
- https://learn.microsoft.com/en-us/sql/t-sql/statements/set-transaction-isolation-level-transact-sql
- https://learn.microsoft.com/en-us/sql/relational-databases/performance/optimized-locking
- https://learn.microsoft.com/en-us/sql/relational-databases/sql-server-transaction-locking-and-row-versioning-guide
- https://learn.microsoft.com/en-us/sql/relational-databases/accelerated-database-recovery-management
- https://learn.microsoft.com/en-us/sql/relational-databases/accelerated-database-recovery-troubleshoot
- https://learn.microsoft.com/en-us/azure/azure-sql/database/analyze-prevent-deadlocks
- https://learn.microsoft.com/en-us/sql/relational-databases/sql-server-deadlocks-guide
- https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-objects/sys-dm-os-waiting-tasks-transact-sql
- https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-objects/sys-dm-exec-session-wait-stats-transact-sql
- https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-objects/sys-dm-db-wait-stats-azure-sql-database
- https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-objects/sys-dm-os-wait-stats-transact-sql
- https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-objects/sys-dm-exec-query-stats-transact-sql
- https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-query-memory-grants-transact-sql
- https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-io-virtual-file-stats-transact-sql
- https://learn.microsoft.com/en-us/sql/relational-databases/performance/display-the-estimated-execution-plan
- https://learn.microsoft.com/en-us/sql/relational-databases/performance/display-an-actual-execution-plan
- https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-objects/sys-dm-exec-query-profiles-transact-sql
- https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-objects/sys-dm-exec-query-statistics-xml-transact-sql
- https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-objects/sys-dm-exec-query-plan-stats-transact-sql
- https://learn.microsoft.com/en-us/sql/relational-databases/performance/intelligent-query-processing-memory-grant-feedback
- https://learn.microsoft.com/en-us/sql/relational-databases/performance/query-profiling-infrastructure
- https://learn.microsoft.com/en-us/sql/relational-databases/indexes/clustered-and-nonclustered-indexes-described
- https://learn.microsoft.com/en-us/sql/relational-databases/sql-server-index-design-guide
- https://learn.microsoft.com/en-us/sql/t-sql/statements/set-statistics-io-transact-sql
- https://learn.microsoft.com/en-us/sql/t-sql/statements/set-statistics-time-transact-sql
- https://learn.microsoft.com/en-us/sql/relational-databases/performance/joins
- https://learn.microsoft.com/en-us/sql/relational-databases/indexes/create-indexes-with-included-columns
- https://learn.microsoft.com/en-us/sql/t-sql/statements/create-index-transact-sql
- https://learn.microsoft.com/en-us/sql/relational-databases/indexes/tune-nonclustered-missing-index-suggestions
- https://learn.microsoft.com/en-us/sql/relational-databases/indexes/create-filtered-indexes
- https://learn.microsoft.com/en-us/sql/relational-databases/indexes/columnstore-indexes-overview
- https://learn.microsoft.com/en-us/sql/relational-databases/views/create-indexed-views
- https://learn.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-query
- https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/performance/troubleshoot-high-cpu-usage-issues
- https://learn.microsoft.com/en-us/sql/t-sql/functions/year-transact-sql
- https://learn.microsoft.com/en-us/sql/relational-databases/showplan-logical-and-physical-operators-reference
- https://learn.microsoft.com/en-us/sql/relational-databases/cursors
- https://learn.microsoft.com/en-us/azure/azure-sql/database/performance-guidance
- https://learn.microsoft.com/en-us/sql/t-sql/language-elements/set-operators-union-transact-sql
- https://learn.microsoft.com/en-us/sql/relational-databases/query-processing-architecture-guide
- https://learn.microsoft.com/en-us/sql/relational-databases/performance/plan-guides
- https://learn.microsoft.com/en-us/sql/relational-databases/performance/parameter-sensitive-plan-optimization
- https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-executesql-transact-sql
- https://learn.microsoft.com/en-us/sql/relational-databases/resource-governor/resource-governor
- https://learn.microsoft.com/en-us/azure/azure-sql/database/metrics-diagnostic-telemetry-logging-streaming-export-configure
- https://learn.microsoft.com/en-us/azure/azure-sql/database/sql-insights-overview
- https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-query-store-query-transact-sql