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;