Tuning Azure SQL Elastic Pools

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).

newpool

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.

toohigh

This is the problem query which you can see from query performance insight – the bar on the right.

var

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.

queryid

This is actually on a server where I have not enabled automatic tuning on as the default.

querycode

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

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).
Advertisements

2 thoughts on “Tuning Azure SQL Elastic Pools

  1. Great post, thanks. I’m new at this. Where can I find out about the “classic techniques most know about” (I don’t)?

    Like

    • Hey John classic techniques from on-premises SQL Server is very much applicable. Things like is a scan really needed? why is the key lookup there ? Am I seeing implicit conversions etc.

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s