The below image is a beautiful picture, now it could be worse. The red line and the green line could peak at the same time and for a very long time or the blue line could behave the same as the red line and peak at the same time as the green line. Regardless of the situation the point of this blog post is when you are hitting your eDTU (elastic database transaction unit) limits within your elastic pools, tune your queries and do not knee jerk and just scale up (straightaway that is).
So what should you do? Here I want to look into the database behaviour for the green line. You can drill into the database from this view. It takes you to the actual database. Guess what, I have a DTU warning.
This is the problem query which you can see from query performance insight – the bar on the right.
Rather than just running the create index hints from the recommendations section, take a look at the code and optimise it. Click the query details to see the code.
This is actually on a server where I have not enabled automatic tuning on as the default.
I changed the query for the better using classic techniques most know about. Soon as I changed the stored procedure things started to behave better
So stay ahead of the game and configure those eDTU alerts. When you get alerted find those expensive queries, just as you would do with your on-premises SQL Server databases.
Just to review
- Configure your alerts.
- Use query performance insight to find the query id.
- Tune it.
- Monitor it.
- If you are still hitting your limits, then consider scaling up your pool (next blog post).