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