Azure SQL - DTU CPU usage disparity

4
votes

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!

1
c#azure-sql-databaseconnection-pooling
Look at what is taking the most CPU time in Query Store. - leppie
Yeah i done that, but as you can see from the pic i attached, the CPU usage for the top 20 queries only uses about 5-10%, you will see that the DTU usage is always about 30% higher and sometimes it spikes to close to 100% DTU usage!! other DB's the DTU and CPU usually track eachother like this acom.azurecomcdn.net/80C57D/cdn/mediahandler/docarticles/… - user1957687
Query Id 122 is using 61.5% ! Add those top 5 together, and you almost hit 85% I would definitely see if you can improve 122. Post a query plan if possible. - leppie
that was an example i gave you to show how DTU is closely mapped the CPU usage ... see my original post with the actual screenshot of the performance graph. - user1957687
anyone? ............ - user1957687

1 Answers

1
votes

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!