Does there exist a way to determine the exact file that contains an allocation unit in a filegroup of multiple files?

Question

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:

• Where are my Rows? – Using the %%physloc%% Virtual Column (sqlity.net)
• SQL Server 2008: New (undocumented) physical row locator function by Paul Randal

• Paul White: Related: reporting space consumption to files in filegroup - how to reverse-engineer the algorithm spreading data across files

Answer 1

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 from DBCC 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 and LOB_DATA types). I also added the total_pages field so that it would be easier to relate that data point to the rows that have NULLs for the Data Files. If you don't care about the Allocation Units that have 0 rows, then it would be fine to change that LEFT JOIN to be an INNER JOIN.

IF (OBJECT_ID(N'tempdb..#AllocationsToFiles') IS NULL)
BEGIN
 -- DROP TABLE #AllocationsToFiles;
 CREATE TABLE #AllocationsToFiles
 (
 ObjectID INT NOT NULL,
 IndexID INT NOT NULL,
 PartitionID INT NOT NULL,
 RowsetID BIGINT NOT NULL,
 AllocationUnitID BIGINT NOT NULL,
 AllocatedPageFileID SMALLINT NOT NULL
 );
END;
IF (NOT EXISTS(SELECT * FROM #AllocationsToFiles))
BEGIN
 --TRUNCATE TABLE #AllocationsToFiles;
 INSERT INTO #AllocationsToFiles (ObjectID, IndexID, PartitionID, RowsetID,
 AllocationUnitID, AllocatedPageFileID)
 SELECT DISTINCT alloc.[object_id], alloc.[index_id], alloc.[partition_id],
 alloc.[rowset_id], alloc.[allocation_unit_id], alloc.[allocated_page_file_id]
 FROM sys.dm_db_database_page_allocations(DB_ID(), NULL, NULL, NULL,
 'LIMITED') alloc
 WHERE alloc.is_allocated = 1
 AND alloc.is_iam_page = 0;
END;
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],
 TotalPages = au.total_pages,
 AllocationUnitType = au.type_desc,
 LogicalFileName = dbf.[name],
 PhysicalFileName = dbf.[physical_name]
 --,p.[object_id], p.[partition_id], au.allocation_unit_id
FROM sys.allocation_units au
INNER JOIN sys.partitions p
 ON au.container_id = IIF(au.[type] = 2, p.[partition_id], p.[hobt_id])
INNER JOIN sys.indexes i 
 ON i.[object_id] = p.[object_id]
 AND i.index_id = p.index_id
INNER JOIN sys.tables t 
 ON p.[object_id] = t.[object_id]
INNER JOIN sys.schemas sh
 ON t.[schema_id] = sh.[schema_id]
LEFT JOIN (#AllocationsToFiles alloc
 INNER JOIN sys.database_files dbf
 ON dbf.[file_id] = alloc.AllocatedPageFileID
 ) 
 ON alloc.ObjectID = p.[object_id]
 AND alloc.IndexID = p.index_id
 AND alloc.PartitionID = p.partition_number
 AND alloc.AllocationUnitID = au.allocation_unit_id
WHERE sh.name <> N'sys'
ORDER BY t.name, i.index_id, p.partition_number;

• James Jenkins: This is a great tool, thank you. I do want to point out that the column TotalPages, is total pages for the index. When the results return multiple rows per index, the index is spread across multiple files, but it does not show how much of the index is on each file. Each row will show the total number of pages per index, not per file. (The first few times I ran it I thought, cool my indexes are perfectly balanced across files, I was wrong)

Answer 2

Remus Rusanu, on May 21, 2013, provided an answer to this question:

One filegroup, multiple datafiles, how to get list of tables in each file

His response was:

An object in a filegroup will use all datafiles in the filegroup. Any table in FG1 resides equally on Datafile1, Datafile2 and Datafile3. If you need to control placement you need to create distinct filegroups.

• swasheck: Thanks. I'm don't really want to control where it goes, but rather see where it's gone.
• Sean Gallardy: FYI - this is correct assuming all of the files were created at the same time. If files were added to the filegroup or other trace flags were used it may not be in all files. Not saying he's wrong, because he's not, saying that it depends :)