Azure SQL Database - Lessons learned from the trenches

on YouTube ↗58m

" summary

A production database hits 100% CPU because one frequently run parameterized query repeatedly converts a parameter, causing a clustered index scan that reads ~400,000 rows per execution; diagnosis uses the Azure portal metrics, Query Store and execution plans to find query id 632 and workers stuck at 1%. Fixing the client parameter type restores the index and drops CPU and duration; other lessons include using extended events/Profiler saved to blob for errors, keeping stats and indexes current, and validating connection and scaling choices.

AI-written summary · about

" chapters (10)

1. 0:00Introduction and speakers
2. 1:07Lesson learned from support work
3. 3:14First case: Saturday performance issue
4. 27:11Hard lessons from the first case
5. 31:14Troubleshooting performance problems
6. 36:31Second case: Managed Instance queries
7. 46:23Diagnostics and tools in Azure
8. 49:46Third case: Connectivity problems
9. 53:02Connectivity best practices
10. 55:02Creating support requests

" related talks

• 51mPerformance Tuning Azure SQL DatabaseMonica Rathbun · SQLBits 2023
• 59mLessons Learned from the Trenches: Connectivity and Performance Tips&TricksJose Manuel Jurado Diaz · NDC London 2021
• 49mPerformance Features in SQL Server and Azure SQLJavier Villegas · SQL Server Virtual Conference 2021
• 58mFixing poor performance patterns in Azure SQL Database applicationsMartin Cairney · NDC Sydney 2020
• 53mOptimizing your Azure SQL Database workloadsSanjay Mishra, Denzil Ribeiro, Dimitri Furman · SQLBits 2020
• 49mSQL Server on Azure VM - Configuring for Price-PerformanceDavid Pless · SQLBits 2024
• 33mDriver for Data-Driven Change - Azure Cosmos DB Performance, Security & Cost Optimization Done RightKristoffer Axelsson · Azure Cosmos DB Conf 2022
• 50mBuilt-in Intelligence with Azure SQL and SQL Server 2022David Pless · SQLBits 2023