You’ve got an Azure SQL DB, and your queries are going slow. You’re wondering, “Am I hitting the performance limits? Is Microsoft throttling my queries?”
There’s an easy way to check: run sp_BlitzFirst. sp_BlitzFirst is our free performance health check stored procedure that analyzes a lot of common performance issues and then gives you a prioritized list of reasons why your server might be slow right now.
Here’s what it looks like when you’ve hit your DTU limits – note that there are multiple rows, one for each 15-second period where you approached or hit your DTU limits in the last 15 minutes:
If you click on the “ClickToSeeDetails” column, you get:
At 2018-10-21 17:02:09.153, your database approached (or hit) your DTU limits:
Average CPU percent: 99.88
Average data IO percent: 0.00
Average log write percent: 0.32
Max worker percent: 2.22
Max session percent: 0.88
We’re checking the last 15 minutes of the sys.dm_db_resource_stats management view to see if you hit 90% or higher for avg_cpu_percent, avg_data_io_percent, avg_log_write_percent, avg_memory_usage_percent, max_worker_percent, or max_session_percent.
Uh oh – I’m maxed out. Now what?
You could tune queries, tune indexes, lower your app’s workload, or upgrade to a higher DTU limit.
To find which queries to tune, run sp_BlitzCache @SortOrder = ‘___’, where ___ is based on which limit you’re hitting:
- CPU limits – run sp_BlitzCache @SortOrder = ‘cpu’
- Data IO – run sp_BlitzCache @SortOrder ‘reads’
- Log write – run sp_BlitzCache @SortOrder = ‘writes’
If you’d rather tune indexes, run sp_BlitzIndex and focus on the missing index warnings. They’re chock full of dangerous pitfalls, but they’re still one of the easiest ways to get started. We teach you how to interpret the recommendations in our Fundamentals of Index Tuning videos.
Lowering your app’s workload is a totally valid option, too: try caching things in your application rather than hitting the database every time, or staggering your workload so that queries are run at different times.
And of course, you could throw hardware at it by raising your DTU level. If you started on a really low level, like S1/S2/S3, and you’re constantly hitting these throttling limits, then it might be time to upgrade. To keep things in perspective, an S4 with 200 DTUs is only $300/month – if you’re any lower than that, you’re probably going to hit limits sooner rather than later.
All these scripts are part of our free First Responder Kit.