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.
- 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)
- is there a known cause for this blowout? - a 10gb table consuming 70gb of space is a bit excessive.
- is there a way to clean this up?
- is there a strategy i can employ to prevent this happening again?
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.