Insanely poor query performance on SQL Azure

Question

We have S1: 20 DTU 250GB SQL Azure database with following table

CREATE TABLE [dbo].[Reads]
(
    [ReadId] [INT] IDENTITY(1,1) NOT NULL,
    [LicenseNumber] [VARCHAR](50) NULL,
    [Name] [VARCHAR](50) NULL,
    [Serial] [VARCHAR](20) NULL,
    [FirstSeenUtc] [DATETIME] NULL,
    [LastSeenUtc] [DATETIME] NULL,
    [Count] [INT] NOT NULL,
    [Model] [VARCHAR](100) NULL,
    [Make] [VARCHAR](100) NULL,
    [TimestampUtc] [DATETIME] NOT NULL,
    [PortNumber] [INT] NOT NULL,
    [Code] [VARCHAR](50) NULL,
    [Peak] [FLOAT] NULL,

    CONSTRAINT [PK_Reads] 
        PRIMARY KEY CLUSTERED ([ReadId] ASC)
                WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

This table has more than 80 million rows and simple query as

select count(1) from dbo.Reads

took 1 hour and 30 minutes to run. The load on the database in minimal with a process adding maybe around 1000 rows every minute. Currently nothing reading from this table and overall pretty much no load on the database.

I upgraded the database to S2: 50 DTU and the above query took 18 minutes to run.

I updated stats but didn't help much. I ran Brent Ozar's BlitzFirst stored procedure while the above query was running and it said database is maxing out data IO. Same database restored on my surface laptop returns row count in a second. Database performance tab does not have any recommendations.

S2: 50 DTU costs $75 per month and next option is S3: 100 DTU at $150 per month.

My plan was to create a database for every customer I sign up but at $150 per database per month I will go out of business pretty quick!

Is this SQL Azure's expected level of performance? Shouldn't this sort of basic query yield instantaneous result? Would moving to SQL Server on VM be better?

[Update 2019-03-10 11:35AM EST]

The table does have following IX

CREATE NONCLUSTERED INDEX [IX_Read_License_Code_TimeStamp] ON [dbo].[Reads]
(
    [LicenseNumber] ASC,
    [Code] ASC,
    [TimestampUtc] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]

I see now that some of the columns can be safely changed into NOT NULL and could help improve things.

[Update: 2019-03-10 8:40PM EST]

I altered the table to make LicenseNumber and Code NOT NULL that took more than 6 hours. After that the count query ran in 1 minute and 32 seconds.

Following query returned results in 40 seconds

select Code, LicenseNumber, TimeStampUtc from dbo.Reads Where TimestampUtc >= '2019-03-10'

Try putting an index on `PortNumber` - that's a **non-nullable** `INT` column, and once that index is present, determining the number of rows in the table should be significantly faster. If you don't have such an index, then determining the number of rows means basically doing a full table scan and loading **ALL** the data; with an index on a narrow, non-nullable column, just that index (much less data) needs to be loaded and examined — marc_s, Mar 10 '19 at 07:32
@marc_s Doesn't the `PRIMARY KEY` constraint on `ReadId` automatically index that field? — aaaaaa123456789, Mar 10 '19 at 07:56
@aaaaaa123456789: yes - but **NOT** with a "single-column" non-clustered index. The primary key automatically creates the **clustered index** - which is basically the whole data table - not just a single `INT` column. — marc_s, Mar 10 '19 at 08:04
@marc_s This is very interesting. My understanding was same as aaaaaa123456789's. The table does have an non clustered index (that I will add to original question) and when I ran the count query with actual query plan on then it ran for 30 minutes and showed that 92% of the cost was scanning that IX and remaining 8% was aggregate operation. Question is why same query on my same database restored on my laptop would return results in a second? — Shahid Syed, Mar 10 '19 at 15:31
Since some (or all) of those columns in your existing index are **nullable**, SQL Server cannot use this index for counting rows. The counting of rows happens at the **leaf level** of the index - and for the clustered index, that's the whole table data --> therefore it's slow. The best bet is a non-clustered index on **one, single, narrow, non-nullable column** - like an `INT NOT NULL` or something similar - that would have the most entries on a single data page, and thus scanning the whole index to count the rows will be the fastest possible — marc_s, Mar 10 '19 at 19:39
Even if your index were with non-nullable columns only - with up to 108 bytes of space needed, for 80 million rows, you'd end up having around 1 million index pages at the leaf level to load and scan. With an index on the 4-byte, non-nullable `INT` column, that drops to roughly 40'000 pages - that's a **MASSIVE** difference! — marc_s, Mar 10 '19 at 19:43
I'm going to ask a dumb question: why is a count of all rows in the table important to you? If that number is off by a few, would you know? Can you use the value in sys.partitions as an O(1) lookup as an approximation? — Ben Thul, Mar 10 '19 at 22:35
I started writing queries to extract data out of this table, but queries would run forever. If I were to share the query I wrote over here then that would've changed the topic to "is my query bad" instead of focusing on why its so darn slow for a simplest query you can write. — Shahid Syed, Mar 11 '19 at 00:36

score 0 · Accepted Answer · answered Mar 13 '19 at 03:09

Dropping the index and creating it again did it for me. Before this even queries that were completely covered by the index was taking several minutes to execute. After re-creating the index same queries are running under a second.

Thanks to everyone who commented on this question. I learned new things.

Insanely poor query performance on SQL Azure

1 Answers1