Simple select count(id) uses 100% of Azure SQL DTUs

Question

This started off as this question but now seems more appropriately asked specifically since I realised it is a DTU related question.

Basically, running:

select count(id) from mytable

EDIT: Adding a where clause does not seem to help.

Is taking between 8 and 30 minutes to run (whereas the same query on a local copy of SQL Server takes about 4 seconds).

Below is a screen shot of the MONITOR tab in the Azure portal when I run this query. Note I did this after not touching the Database for about a week and Azure reporting I had only used 1% of my DTUs.

A couple of extra things:

• In this particular test, the query took 08:27s to run.
• While it was running, the above chart actually showed the DTU line at 100% for a period.
• The database is configured Standard Service Tier with S1 performance level.
• The database is about 3.3GB and this is the largest table (the count is returning approx 2,000,000).

I appreciate it might just be my limited understanding but if somebody could clarify if this is really the expected behaviour (i.e. a simple count taking so long to run and maxing out my DTUs) it would be much appreciated.

Answer 1

From the query stats in your previous question we can see:

300ms CPU time
8000 physical reads

8:30 is about 500sec. We certainly are not CPU bound. 300ms CPU over 500sec is almost no utilization. We get 16 physical reads per second. That is far below what any physical disk can deliver. Also, the table is not fully cached as evidenced by the presence of physical IO.

I'd say you are throttled. S1 corresponds to

934 transactions per minute

for some definition of transaction. Thats about 15 trans/sec. Maybe you are hitting a limit of one physical IO per transaction?! 15 and 16 are suspiciously similar numbers.

Test this theory by upgrading the instance to a higher scale factor. You might find that SQL Azure Database cannot deliver the performance you want at an acceptable price.

You also should find that repeatedly scanning half of the table results in a fast query because the allotted buffer pool seems to fit most of the table (just not all of it).