1

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.

1 Answer 1

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.

6
  • the indexes in question suffer from random-insert. I realise this causes page splits, but i would think that the worst overhead would be 100%. if every page has 50% space free, then an insert at any arbitrary location would be met with an empty row - on average i would expect an index to stabailze at about 75% - all pages half way between filling, and being split back to 2 pages at 50%. (fragmentation on these indexes is >90%, but they are backed by SSD, so having to fetch from random parts of the disk should be approx free, especially when IO limited by cloud services) Commented Oct 8, 2018 at 5:21
  • average_page_space_used for the object in question is still >50% (as expected), which doesn't match with the reserved page overhead. Commented Oct 8, 2018 at 5:26
  • yes, i believe cleanup is only possible with a rebuild - not sure why the blowout is so big though :( Commented Oct 8, 2018 at 5:30
  • Let me share this blog post with you. blogs.msdn.microsoft.com/dilkushp/2013/07/27/… Commented Oct 8, 2018 at 11:17
  • rebuild did fix the issue, so i'll accept as answer, even though the answer to 'why does high fragmentation come with such a high space overhead' isn't currently known. - i'd expect maximum overhead to be 2x (that blog links to a database shrunk from 1.5gb to 350mb in azure) Commented Oct 9, 2018 at 3:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.