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.

enter image description here

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.

The answers in the original question already cover what happens. The "simple" count is actually contrived, expensive and has to scan the entire table. Don't do that. If you had a WHERE clause that used indexed fields the optimizer would use an Index Seek operation resulting in far better performance. A seek reads the index B-Tree to find matching rows before calculating the total (ie far less IO, better speed). If you use a field with high selectivity you'll have to read far fewer index pages. — Panagiotis Kanavos
Just FTR, the 'simple' count isn't contrived - it's what I need to do with/without various where clauses. — chrisb
Did you ever get to a satisfying answer why this is happening? I see the same thing with S2 (50 DTU) over a 127GB database with a single table of 550 million rows, and count(1) takes close to an hour. — Gena Kukartsev
I also see a similar thing when bulk-importing this data. I used freebcp utility, and the rate was abysmally low, something like 5000 rows per second. I wrote it off to the utility's inefficiency but I now checked, and that bulk insert also maxed out DTUs. — Gena Kukartsev
This DTU limit does not make sense to me: one of the comments in your other question on this topic suggested that P1 is similar to a quad core 12GB RAM server but P1 only has 125 DTU - a factor of 2.5 from what I have, and that is not enough to get the count to a few seconds range, where it should be. — Gena Kukartsev

usr usr · Accepted Answer · 2014-10-08T08:04:50

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

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

3 Answers