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:
Best Answer
Try the following query. It first creates a local temporary table and then populates it with the AllocationUnitID-to-FileID associations found in
sys.dm_db_database_page_allocations
, an undocumented Dynamic Management Function (DMF) introduced in SQL Server 2012 (for versions prior to 2012, you can get this info fromDBCC IND()
). That local temp table is then JOINed into a modified version of the original query.The data from that DMF is placed into a temporary table for performance since, depending on the size of the database, it could take more than a few seconds to get that data. The
DISTINCT
keyword is used because that DMF returns one row per data page, and there are multiple data pages per each allocation unit.I left-JOINed that data into the original query since the original query returns allocation units that have 0 data pages (typically
ROW_OVERFLOW_DATA
andLOB_DATA
types). I also added thetotal_pages
field so that it would be easier to relate that data point to the rows that haveNULL
s for the Data Files. If you don't care about the Allocation Units that have 0 rows, then it would be fine to change thatLEFT JOIN
to be anINNER JOIN
.