Does there exist a way to determine the exact file that contains an allocation unit in a filegroup of multiple files?
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 NULL
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 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;