Roadmap › Azure Databases — Azure SQL, Managed Instance, Cosmos DB, PostgreSQL and Migration › Module: Azure SQL
Lessons in this course (9)
5. Performance monitoring and tuning in Azure SQL
IntermediateMediumLesson 5 of 9
- Query Store — on by default: top resource-consuming queries, regressed plans, plan forcing.
- Automatic tuning —
FORCE_LAST_GOOD_PLAN(on by default), optional automaticCREATE INDEX/DROP INDEXrecommendations. - Query Performance Insight and Intelligent Insights in the portal; Azure Monitor metrics (CPU %, Data IO %, Log IO %, DTU/vCore usage, deadlocks, connection failures, storage).
- DMVs work as on-prem (
sys.dm_exec_requests,sys.dm_db_resource_statsfor the last hour at 15-second granularity). - Typical fixes are the same as SQL Server (indexes, SARGable queries, parameter sniffing) — plus right-sizing the tier, read scale-out for reports (
ApplicationIntent=ReadOnly), and connection pooling (many short-lived connections from serverless functions can exhaust limits). - Resource limits: each tier caps CPU, I/O, log rate, workers and sessions — "throttling" shows up as
LOG_RATE_GOVERNOR,RESOURCE_SEMAPHOREwaits or error 10928/10936 (worker/session limits).