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

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;

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.