cause of / how to fix a table with excessive reserved pages

Question

I have several tables with clustered indexes in azure PaaS sql server which have blown out to be much bigger than their data, with reserved page count several times bigger than the actual amount of data in the table. These tables are insert only (no deletes or updates), although from time to time rows are inserted, which then fail insert due to PK violation.

1. is this an issue? - azure is reporting that all the 'reserved' space in this database is actually in use (somewhat expected from what i understand about what reserved means)
2. is there a known cause for this blowout? - a 10gb table consuming 70gb of space is a bit excessive.
3. is there a way to clean this up?
4. is there a strategy i can employ to prevent this happening again?

an example

partition_id    object_id   index_id    partition_number    in_row_data_page_count  in_row_used_page_count  in_row_reserved_page_count  lob_used_page_count lob_reserved_page_count row_overflow_used_page_count    row_overflow_reserved_page_count    used_page_count reserved_page_count row_count
72057594050904064   274100017   1   1   1082295 1086782 5527998 0   0   0   0   1086782 5527998 61559096

This is an object with known extreme fragmentation (>90%) however my personal expectation is that a bad layout on disk shouldn't cause huge amounts of wasted space, just excessive seek latency due to minimising contiguous reads.

Also, assuming that individual pages under random insert fill up, then split into two pages half full, then each page should oscillate between 50% and 100% (average 75%) resulting in an approx 25% storage overhead for a highly fragmented table/index.

Answer 1

Please examine the level of fragmentation of those indexes with below query. Index fragmentation could be the culprit.

SELECT
 DB_NAME() AS DBName
 ,OBJECT_NAME(ps.object_id) AS TableName
 ,i.name AS IndexName
 ,ips.index_type_desc
 ,ips.avg_fragmentation_in_percent
 FROM sys.dm_db_partition_stats ps
 INNER JOIN sys.indexes i
 ON ps.object_id = i.object_id
 AND ps.index_id = i.index_id
 CROSS APPLY sys.dm_db_index_physical_stats(DB_ID(), ps.object_id, ps.index_id, null, 'LIMITED') ips
 ORDER BY ps.object_id, ps.index_id

If you rebuild your indexes, you will reclaim that extra space. You will reduce the size of those indexes and the size of the overall database. Perform index maintenance with the following script:

DECLARE @TableName varchar(255)

 DECLARE TableCursor CURSOR FOR
 (
 SELECT '[' + IST.TABLE_SCHEMA + '].[' + IST.TABLE_NAME + ']' AS [TableName]
 FROM INFORMATION_SCHEMA.TABLES IST
 WHERE IST.TABLE_TYPE = 'BASE TABLE'
 )

 OPEN TableCursor
 FETCH NEXT FROM TableCursor INTO @TableName
 WHILE @@FETCH_STATUS = 0

 BEGIN
 PRINT('Rebuilding Indexes on ' + @TableName)
 Begin Try
 EXEC('ALTER INDEX ALL ON ' + @TableName + ' REBUILD with (ONLINE=ON)')
 End Try
 Begin Catch
 PRINT('Cannot do rebuild with Online=On option, taking table ' + @TableName+' down for douing rebuild')
 EXEC('ALTER INDEX ALL ON ' + @TableName + ' REBUILD')
 End Catch
 FETCH NEXT FROM TableCursor INTO @TableName
 END

 CLOSE TableCursor
 DEALLOCATE TableCursor

Create an Azure Runbook to defragment indexes regularly and avoid using more storage space.