CODEWITHSUNDEEP
c#azure-sql-databaseconnection-pooling
user1957687
user1957687

Reputation: 89

Azure SQL - DTU CPU usage disparity

I have been analyzing a DB that is running in Azure SQL that is performing VERY badly. it is on the premium tier with 1750 DTUs available, and at times can still max out DTUs.

Ive identified a variety of querys and terrible data access patterns thru stored procs, which has reduced load. But there is still this massive disparity between DTU and CPU usage in the image below, any other image i see of the "Query Performance Insight" in azure sql shows the DTU line aligning with the CPU usage for the most part.

DTU (in red) to CPU usage per query

Looking at the C# app sitting ontop of this, for each user that uses the app, it creates a SQL user, and uses that user in the connection string to access the DB. This means that connection pooling is not being used, resulting in a massively larger number of active users/sessions on the SQL azure DB. Could this be the sole reason why there is such high DTU usage?

Or could i possibly be missing something regarding IO that isnt visible in the Azure portal?

Thanks Neil

EDIT: Adding sessions and workers image!

enter image description here

Based on that im not convinved now.. what is the session percentage of? Like its 10% but 10% of what? the max allowed?

Edit2: Adding more metrics:

One week: enter image description here

2-3 hours when load is high: enter image description here

The purple spike i believe is the reindex so can ignore that!

Upvotes: 4

Views: 4080

Answers (1)

Shannon Lowder
Shannon Lowder

Reputation: 482

Trying to understand DTU versus resources was a stumbling block for me too. Click on your Resource utilization chart and click Editdefault resource chart

Then you get a slider with a lot of resources you can monitor. Select Sessions, and Workers percent. More than likely one of these are your problems. If not, you can add in: CPU, Data IO, Log IO, and/or In-memory OLTP percentage. Hit OK.

Resource options

Now, what you should find is the real cost in your query or queries. Learning how your queries consume the different resources can help you fix performance problems like these. I learned this when doing large inserts, I was maxing out my Log IO, and everything else was <5% utilization.

Try that, and if you are right about connection pooling, unfortunately that will mean some refactoring in the application. At the very least, using this will give you more insight than just looking at that DTU percentage!

Upvotes: 1

Related Questions