Free Space Usage inside filegroup partitions
Hi All, in last 12 months I've used filegroup partitions based on Range Right partition scheme, based on 120 ranges assigned to 8 filegroup round robin at application level we have a DataStream Ingestion based on Identity values partition function is tuned to keep 1 month of data data retention in the data Stream staging schema is 3 months Everything seems to work and partitions are purged even if the high watermark size measured via occupancy ( sys.allocation_units + sys.dm_db_index_physical_stats etc..) is about 100 GBytes the filegroups raised 600 GBytes and I had to manually shrink DBCC SHRINKFILE (10, TRUNCATEONLY ) ; DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages ------ ----------- ----------- ----------- ----------- -------------- 68 10 27736520 65536 3712 3656 this is the complete query used for reporting and rebuilding SELECT object_name(p.object_id) AS ObjectName, total_pages / 128. AS SpaceUsed_MB, case when [total_pages] = 0 THEN 0.0 else round( [rows] / ( 1.0 * ( total_pages / 128.0 ) ),2) end as row_occupancy, p.partition_id, p.object_id, p.index_id, si.name as indexname, p.partition_number, p.rows, a.avg_fragmentation_in_percent, right_prv.value partition_extreme, p.data_compression_desc, (case when (p.index_id = 0 and isnull(a.avg_fragmentation_in_percent,0) > 60.0) OR ( p.index_id = 0 AND [rows] = 0 AND total_pages > 1000 ) then ' ALTER TABLE ' + QUOTENAME( object_name(p.object_id) ) + ' REBUILD PARTITION = ' + convert(varchar(19), p.partition_number) + ' WITH ( maxdop = 1 ); ' else ' /* NOTHING */ ' END ) + ' ' + (case when (p.index_id > 0 and isnull(a.avg_fragmentation_in_percent,0) > 60.0) OR ( p.index_id > 0 AND [rows] = 0 AND total_pages > 1000 ) then ' ALTER INDEX ' + si.name + ' ON ' + QUOTENAME( object_name(p.object_id) ) + ' REBUILD PARTITION = ' + convert(varchar(19), p.partition_number) + ' WITH ( maxdop = 1 ); ' else ' /* NOTHING */ ' end ) as REBUILD1, df.physical_name, df.name as LogicalNAme FROM sys.partitions AS p left outer join sys.indexes si on si.object_id = p.object_id and si.index_id = p.index_id inner JOIN sys.allocation_units AS au ON p.partition_id = au.container_id -- and p.partition_number = 16 and total_pages / 128.0 > 10.0 -- INNER JOIN sys.filegroups f ON au.data_space_id = f.data_space_id inner join sys.database_files df ON df.data_space_id = f.data_space_id LEFT JOIN sys.partition_schemes ps ON ps.data_space_id = si.data_space_id LEFT JOIN sys.partition_functions pf ON ps.function_id = pf.function_id LEFT JOIN sys.partition_range_values left_prv ON left_prv.function_id = ps.function_id AND left_prv.boundary_id + 1 = p.partition_number LEFT JOIN sys.partition_range_values right_prv ON right_prv.function_id = ps.function_id AND right_prv.boundary_id = p.partition_number cross apply sys.dm_db_index_physical_stats ( db_id() , p.object_id, p.index_id, p.partition_number ,'LIMITED') A ORDER BY SpaceUsed_MB DESC, right_prv.value desc;
