/Partitioned-tables-indexes-filegroups-boundary-points-sizes.sql
Last active Feb 1, 2017
SELECT | |
sc.name + N'.' + so.name as [Schema.Table], | |
si.index_id as [Index ID], | |
si.type_desc as [Structure], | |
si.name as [Index], | |
stat.row_count AS [Rows], | |
stat.in_row_reserved_page_count * 8./1024./1024. as [In-Row GB], | |
stat.lob_reserved_page_count * 8./1024./1024. as [LOB GB], | |
p.partition_number AS [Partition #], | |
pf.name as [Partition Function], | |
CASE pf.boundary_value_on_right | |
WHEN 1 then 'Right / Lower' | |
ELSE 'Left / Upper' | |
END as [Boundary Type], | |
prv.value as [Boundary Point], | |
fg.name as [Filegroup] | |
FROM sys.partition_functions AS pf | |
JOIN sys.partition_schemes as ps on ps.function_id=pf.function_id | |
JOIN sys.indexes as si on si.data_space_id=ps.data_space_id | |
JOIN sys.objects as so on si.object_id = so.object_id | |
JOIN sys.schemas as sc on so.schema_id = sc.schema_id | |
JOIN sys.partitions as p on | |
si.object_id=p.object_id | |
and si.index_id=p.index_id | |
LEFT JOIN sys.partition_range_values as prv on prv.function_id=pf.function_id | |
and p.partition_number= | |
CASE pf.boundary_value_on_right WHEN 1 | |
THEN prv.boundary_id + 1 | |
ELSE prv.boundary_id | |
END | |
/* For left-based functions, partition_number = boundary_id, | |
for right-based functions we need to add 1 */ | |
JOIN sys.dm_db_partition_stats as stat on stat.object_id=p.object_id | |
and stat.index_id=p.index_id | |
and stat.index_id=p.index_id and stat.partition_id=p.partition_id | |
and stat.partition_number=p.partition_number | |
JOIN sys.allocation_units as au on au.container_id = p.hobt_id | |
and au.type_desc ='IN_ROW_DATA' | |
/* Avoiding double rows for columnstore indexes. */ | |
/* We can pick up LOB page count from partition_stats */ | |
JOIN sys.filegroups as fg on fg.data_space_id = au.data_space_id | |
ORDER BY [Schema.Table], [Index ID], [Partition Function], [Partition #]; | |
GO |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment