Why is this SQL Server table using so much space

Question

We have a table in an Azure SQL Server DB that is using about 15x the expected used space for the number of rows x average row size. This is occuring with a customer that uses Azure SQL (not managed instance), compared to other customers using AWS RDS or an on-prem SQL Server DB where the used space is good. The cause could be usage pattern related and not Azure related but I have compared customers that are quite similar.

This table holds report data and each day there are about 1-5 report runs inserting ~2M-20M rows each, and old reports are automatically deleted after 3-14 days (ie. also deleting 1-5 report runs daily but at a later date).

We extended the DB and ran a new report and it filled the DB again, using too much space for the row count and row size in the new report. To delete a report run we had to extended the DB again (because it was at the limit and DELETE FROM failed), this time by 50GB, deleted one report (DELETE FROM ReportRuns WHERE ID = x) which deleted ~20M rows and took many hours and oddly the used space grew by 48GB!

The issue was only identified after this customer a) upgraded our application (which by design now writes 6x the number of rows), and b) moved to a new Azure SQL DB. They could have very well been experiencing the problem with their previous DB but didn't notice because the row counts were much lower.

Table Structure

CREATE TABLE ReportDetails(
  [RunID] [int] NOT NULL,
  [FromDate] [datetime] NOT NULL,
  [Period] [int] NULL,
  [Quantity] [float] NOT NULL,
  [Peak] [char](1) NULL,
  [GroupByValues] [varchar](500) NULL,
  [Statistic] [char](5) NULL,
  [Interval] [int] NULL
) ON [PRIMARY]

ALTER TABLE ReportDetails  
WITH NOCHECK ADD CONSTRAINT [FK_ReportDetails_RunID] 
FOREIGN KEY([RunID]) REFERENCES [ReportRuns] ([ID])
ON DELETE CASCADE

CREATE NONCLUSTERED INDEX [IX_REPORTDETAILS] ON ReportDetails
(
  [RunID] ASC,
  [FromDate] ASC,
  [Period] ASC,
  [Statistic] ASC
) WITH (STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF, 
  ONLINE = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) 
  ON [PRIMARY]

Results using this query before we deleted the rows:

table_name       ReportDetails     ReportDetails
index_name                NULL  IX_REPORTDETAILS
object_type              TABLE             INDEX
index_type                HEAP      NONCLUSTERED
partition_count              1                 1
row_count            136901432         137498512
data_compression          NONE              NONE
total_space_MB       175139.38          11588.80
used_space_MB        175109.33           9704.92
unused_space_MB          30.05           1883.88

That's 1341 bytes per row on average. The values in the GroupByValues varchar(500) column range from only 20 to max 62 characters, so I would have expected it to be around 80 bytes per row.

And then after we deleted 20M rows:

index_name                NULL  IX_REPORTDETAILS
object_type              TABLE             INDEX
row_count            116347856         119155072
total_space_MB       222879.35          10059.18
used_space_MB        222842.63           8426.55
unused_space_MB          36.73           1632.63

The index appears to be behaving but why does the table have 20M fewer rows but 47GB increase in space used?

I have checked that there are no active transactions (that might be causing a build up of log entries, table lock, etc).

After a lot of trial and error and investigation I found that the pages are barely used. This is a 'DETAILED' sys.dm_db_index_physical_stats report for the HEAP/table:

fragmentation_percent  page_space_used_percent  page_count  alloc_unit_type_desc
          1.734230907               7.54381023    22413932           IN_ROW_DATA

It appears as though report runs are not using the free space in the pages. I would have expected the new report run would have used free space and not increased space used. And somehow deleting the report made things worse.

Thanks for your help.

Answer 1

There's a lot to digest / address here based on your description and responses so far, but I'll try to address the main things:

1. You should almost always have a clustered index on your tables. The clustered index itself doesn't add any meaningful overhead in space usage, because it is the data of your table itself, sorted on the fields you define it on, rather than a copy of the data like a nonclustered index is. (Note the fields you choose as the clustered index key do get stored in each additional nonclustered index you create too, for facilitating key lookups, so there is a little extra overhead there if you use nonclustered indexes as well).

2. Ironically, the fact that you're not using a clustered index could very well be your problem because of the way a table without a clustered index, aka a Heap, stores data. The link in Dennis's comment on Heaps does a great job explaining how this can be an issue relevant to your particular case.

3. Outside of the above, I'm with vonPryz on verifying your fill factor setting and that the Transaction Log growth settings and / or it's backups aren't the stem of your issues if it isn't anything related to the Heap table you have (as discussed in the linked article in point #2). These are some things that can easily be configured differently between one database and another that would result in some of the behavior you're experiencing.