I was hoping to get a granular view of which database files contained which allocation units for the various HoBTs (both aligned and non-aligned) living in a database.
The query I've always used (see below) has served me well until we began creating multiple data files per filegroup and I'm only able to figure out how to get as granular as the filegroup level.
select
SchemaName = sh.name,
TableName = t.name,
IndexName = i.name,
PartitionNumber = p.partition_number,
IndexID = i.index_id,
IndexDataspaceID = i.data_space_id,
AllocUnitDataspaceID = au.data_space_id,
PartitionRows = p.rows
from sys.allocation_units au
join sys.partitions p
on au.container_id = p.partition_id
join sys.indexes i
on i.object_id = p.object_id
and i.index_id = p.index_id
join sys.tables t
on p.object_id = t.object_id
join sys.schemas sh
on t.schema_id = sh.schema_id
where sh.name != 'sys'
and au.type = 2
union all
select
sh.name,
t.name,
i.name,
p.partition_number,
i.index_id,
i.data_space_id,
au.data_space_id,
p.rows
from sys.allocation_units au
join sys.partitions p
on au.container_id = p.hobt_id
join sys.indexes i
on i.object_id = p.object_id
and i.index_id = p.index_id
join sys.tables t
on p.object_id = t.object_id
join sys.schemas sh
on t.schema_id = sh.schema_id
where sh.name != 'sys'
and au.type in (1,3)
order by t.name, i.index_id,p.partition_number;
However, this query won't work when there are multiple files in a filegroup as I can only get so far as to relate an allocation unit to a data space and, ultimately, a filegroup. I'd like to know if there's another DMV or catalog that I'm missing that I can use to further identify which file in the filegroup contains an allocation unit.
The question behind this question is that I'm trying to evaluate the actual effects of compressing partitioned structures. I know I can do a before-and-after using FILEPROPERTY(FileName,'SpaceUsed')
for the file and a before-and-after on sys.allocation_units.used_pages/128.
to get this information, but the exercise itself made me wonder if I could identify the specific file that contains a specific allocation unit.
I've been messing around with %%physloc%%
in the hopes it could help, but it doesn't quite get me what I'm seeking. The links below were provided by Aaron Bertrand: