Domain 3 of 5 · Chapter 2 of 3

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

QuestionQuery StoreDMVsQuery Performance InsightIntelligent Insights
What it showsQuery texts, plans, runtime and wait statistics per intervalCurrent requests, sessions, locks and waits; cache-lifetime query totalsTop CPU, duration and execution-count queriesDetected degradations with root cause analysis
Time horizonRetained history; data kept 30 days by defaultNow, or since the plan was cached or counters resetCaptured Query Store history; needs a couple of hours of dataLast hour compared with a seven-day baseline
Where it runsSQL Server, Azure SQL Database, Azure SQL Managed InstanceSQL Server, Azure SQL Database, Azure SQL Managed InstanceAzure SQL Database single and pooled databasesAzure SQL Database and Azure SQL Managed Instance (preview)
How you read itSSMS reports or Query Store catalog viewsT-SQL queries against the viewsAzure portalSQLInsights resource log through diagnostic settings
Typical questionWhich 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

Is it happeningright now?DMVscurrent stateDetect againsta baseline?IntelligentInsightsTop queries inthe portal?Query PerformanceInsightQuery Storehistory and plansYesYesYesNoNoNo

Cheat sheet

  • Query Store needs a writable operating state to collect new history
  • Capture mode filters incoming queries while cleanup manages retained history
  • Query Store aggregation windows and disk-flush intervals have different purposes
  • Query Store links historical slowdowns to changes in query plans
  • Query Store cost comparisons require a consistent execution, interval, and replica scope
  • Query Store wait categories connect resource contention to affected queries
  • Query Performance Insight exposes captured query history rather than complete workload accounting
  • Query Store's statement coverage cannot establish a complete DDL execution history
  • Plan forcing can stabilize a regressed query without changing application SQL
  • Query Store hints apply a query-specific policy without editing its source text
  • Resolve a blocking incident by following the chain to its head blocker
  • Unfinished client work can retain blocking even when useful processing has stopped
  • Row versioning reduces reader-writer blocking but does not eliminate all conflicts
  • A deadlock graph identifies the resolved victim and the cycle that needs prevention
  • Optimized locking reduces long-lived data locks while retaining transaction protection
  • Cached query statistics describe completed work for the lifetime of a cached plan
  • Wait diagnosis requires the right scope and a comparable observation interval
  • The query memory-grant DMV separates waiting requests from grant holders
  • Per-file IO statistics help distinguish data-read stalls from log-write stalls
  • Tempdb remedies depend on distinguishing latch contention, storage waits, and shared-space pressure
  • ADR's fast rollback does not remove transaction-driven version retention
  • Performance DMV access depends on the specific view and deployment rather than one universal grant
  • Index keys provide access order while included columns can eliminate lookups
  • A filtered index is useful when queries repeatedly target the same well-defined subset
  • Missing-index suggestions are candidates rather than a complete index design
  • A clustered key becomes the row locator carried by nonclustered indexes
  • Indexed-view access requires an eligible match rather than merely naming a view
  • Columnstore indexes favor broad analytical scans rather than selective transactional lookups
  • Searchable predicates expose indexed values instead of obscuring them with expressions
  • Parameterization promotes reuse while targeted recompilation trades reuse for specialization
  • A cheaper query construct is appropriate only when it preserves the required results
  • Query-level grant hints bound workspace memory without changing the whole workload's policy
  • Execution plans expose different evidence before, during, and after execution
  • A seek is not automatically cheaper than a scan for the required row set
  • Join algorithms and spills must be interpreted against input size and ordering
  • Last-known actual-plan retrieval requires prior collection and a surviving cached plan
  • Intelligent Insights detects degradation against a workload-specific baseline
  • The SQLInsights resource log carries the findings needed to investigate an insight

Unlock with Premium — includes all practice exams and the complete study guide.

References

  1. https://learn.microsoft.com/en-us/azure/azure-sql/database/identify-query-performance-issues
  2. https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/performance/troubleshoot-memory-grant-issues
  3. https://learn.microsoft.com/en-us/azure/azure-sql/database/monitor-tune-overview
  4. https://learn.microsoft.com/en-us/azure/azure-sql/database/intelligent-insights-overview
  5. https://learn.microsoft.com/en-us/sql/relational-databases/performance/monitoring-performance-by-using-the-query-store
  6. https://learn.microsoft.com/en-us/sql/relational-databases/performance/manage-the-query-store
  7. https://learn.microsoft.com/en-us/azure/azure-sql/database/query-performance-insight-use
  8. https://learn.microsoft.com/en-us/azure/azure-sql/database/understand-resolve-blocking
  9. https://learn.microsoft.com/en-us/sql/relational-databases/performance/best-practice-with-the-query-store
  10. https://learn.microsoft.com/en-us/credentials/certifications/resources/study-guides/dp-300
  11. https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-set-options
  12. https://learn.microsoft.com/en-us/sql/relational-databases/extended-events/extended-events
  13. https://learn.microsoft.com/en-us/sql/relational-databases/performance/query-store-usage-scenarios
  14. https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-query-store-runtime-stats-transact-sql
  15. https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-query-store-wait-stats-transact-sql
  16. https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-query-store-runtime-stats-interval-transact-sql
  17. https://learn.microsoft.com/en-us/sql/relational-databases/performance/query-store-for-secondary-replicas
  18. https://learn.microsoft.com/en-us/azure/azure-sql/database/automatic-tuning-overview
  19. https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-query-store-force-plan-transact-sql
  20. https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-query-store-plan-transact-sql
  21. https://learn.microsoft.com/en-us/sql/relational-databases/performance/query-store-hints
  22. https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-requests-transact-sql
  23. https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-tran-locks-transact-sql
  24. https://learn.microsoft.com/en-us/azure/azure-sql/database/monitoring-with-dmvs
  25. https://learn.microsoft.com/en-us/sql/relational-databases/accelerated-database-recovery-concepts
  26. https://learn.microsoft.com/en-us/sql/t-sql/statements/set-transaction-isolation-level-transact-sql
  27. https://learn.microsoft.com/en-us/sql/relational-databases/performance/optimized-locking
  28. https://learn.microsoft.com/en-us/sql/relational-databases/sql-server-transaction-locking-and-row-versioning-guide
  29. https://learn.microsoft.com/en-us/sql/relational-databases/accelerated-database-recovery-management
  30. https://learn.microsoft.com/en-us/sql/relational-databases/accelerated-database-recovery-troubleshoot
  31. https://learn.microsoft.com/en-us/azure/azure-sql/database/analyze-prevent-deadlocks
  32. https://learn.microsoft.com/en-us/sql/relational-databases/sql-server-deadlocks-guide
  33. https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-objects/sys-dm-os-waiting-tasks-transact-sql
  34. https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-objects/sys-dm-exec-session-wait-stats-transact-sql
  35. https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-objects/sys-dm-db-wait-stats-azure-sql-database
  36. https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-objects/sys-dm-os-wait-stats-transact-sql
  37. https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-objects/sys-dm-exec-query-stats-transact-sql
  38. https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-query-memory-grants-transact-sql
  39. https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-io-virtual-file-stats-transact-sql
  40. https://learn.microsoft.com/en-us/sql/relational-databases/performance/display-the-estimated-execution-plan
  41. https://learn.microsoft.com/en-us/sql/relational-databases/performance/display-an-actual-execution-plan
  42. https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-objects/sys-dm-exec-query-profiles-transact-sql
  43. https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-objects/sys-dm-exec-query-statistics-xml-transact-sql
  44. https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-objects/sys-dm-exec-query-plan-stats-transact-sql
  45. https://learn.microsoft.com/en-us/sql/relational-databases/performance/intelligent-query-processing-memory-grant-feedback
  46. https://learn.microsoft.com/en-us/sql/relational-databases/performance/query-profiling-infrastructure
  47. https://learn.microsoft.com/en-us/sql/relational-databases/indexes/clustered-and-nonclustered-indexes-described
  48. https://learn.microsoft.com/en-us/sql/relational-databases/sql-server-index-design-guide
  49. https://learn.microsoft.com/en-us/sql/t-sql/statements/set-statistics-io-transact-sql
  50. https://learn.microsoft.com/en-us/sql/t-sql/statements/set-statistics-time-transact-sql
  51. https://learn.microsoft.com/en-us/sql/relational-databases/performance/joins
  52. https://learn.microsoft.com/en-us/sql/relational-databases/indexes/create-indexes-with-included-columns
  53. https://learn.microsoft.com/en-us/sql/t-sql/statements/create-index-transact-sql
  54. https://learn.microsoft.com/en-us/sql/relational-databases/indexes/tune-nonclustered-missing-index-suggestions
  55. https://learn.microsoft.com/en-us/sql/relational-databases/indexes/create-filtered-indexes
  56. https://learn.microsoft.com/en-us/sql/relational-databases/indexes/columnstore-indexes-overview
  57. https://learn.microsoft.com/en-us/sql/relational-databases/views/create-indexed-views
  58. https://learn.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-query
  59. https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/performance/troubleshoot-high-cpu-usage-issues
  60. https://learn.microsoft.com/en-us/sql/t-sql/functions/year-transact-sql
  61. https://learn.microsoft.com/en-us/sql/relational-databases/showplan-logical-and-physical-operators-reference
  62. https://learn.microsoft.com/en-us/sql/relational-databases/cursors
  63. https://learn.microsoft.com/en-us/azure/azure-sql/database/performance-guidance
  64. https://learn.microsoft.com/en-us/sql/t-sql/language-elements/set-operators-union-transact-sql
  65. https://learn.microsoft.com/en-us/sql/relational-databases/query-processing-architecture-guide
  66. https://learn.microsoft.com/en-us/sql/relational-databases/performance/plan-guides
  67. https://learn.microsoft.com/en-us/sql/relational-databases/performance/parameter-sensitive-plan-optimization
  68. https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-executesql-transact-sql
  69. https://learn.microsoft.com/en-us/sql/relational-databases/resource-governor/resource-governor
  70. https://learn.microsoft.com/en-us/azure/azure-sql/database/metrics-diagnostic-telemetry-logging-streaming-export-configure
  71. https://learn.microsoft.com/en-us/azure/azure-sql/database/sql-insights-overview
  72. https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-query-store-query-transact-sql