是否存在一種方法來確定包含多個文件的文件組中的分配單元的確切文件？

我希望能夠詳細了解哪些數據庫文件包含數據庫中各種 HoBT（對齊和非對齊）的分配單元。

在我們開始為每個文件組創建多個數據文件之前，我一直使用的查詢（見下文）對我很有幫助，我只能弄清楚如何獲得與文件組級別一樣精細的數據。

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;

但是，當文件組中有多個文件時，此查詢將不起作用，因為我只能將分配單元與數據空間以及最終的文件組相關聯。我想知道是否還有另一個 DMV 或目錄我失去了，我可以用它來進一步辨識文件組中的哪個文件包含分配單元。

這個問題背後的問題是我試圖評估壓縮分區結構的實際效果。我知道我可以FILEPROPERTY(FileName,'SpaceUsed')對文件進行前後使用sys.allocation_units.used_pages/128.以獲取此資訊，但練習本身讓我想知道我是否可以辨識包含特定分配單元的特定文件。

我一直在胡鬧，%%physloc%%希望它能有所幫助，但它並沒有完全讓我得到我想要的東西。以下連結由Aaron Bertrand提供：

• 我的行在哪裡？– 使用 %%physloc%% 虛擬列(sqlity.net)
• SQL Server 2008：Paul Randal的新（未記錄的）物理行定位器功能

嘗試以下查詢。它首先創建一個本地臨時表，然後使用 SQL Server 2012 中引入的未記錄的動態管理函式 (DMF) 中找到的 AllocationUnitID-to-FileID 關聯填充它sys.dm_db_database_page_allocations（對於 2012 之前的版本，您可以從 獲取此資訊DBCC IND()）。然後將該本地臨時表加入到原始查詢的修改版本中。

出於性能考慮，來自該 DMF 的數據被放置到一個臨時表中，因為根據數據庫的大小，獲取該數據可能需要幾秒鐘以上的時間。使用該DISTINCT關鍵字是因為 DMF 每個數據頁返回一行，並且每個分配單元有多個數據頁。

我將該數據左聯接到原始查詢中，因為原始查詢返回具有 0 個數據頁（通常ROW_OVERFLOW_DATA和LOB_DATA類型）的分配單元。我還添加了該total_pages欄位，以便更容易將該數據點與NULL數據文件具有 s 的行相關聯。如果您不關心具有 0 行的分配單元，那麼將其更改LEFT JOIN為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;

引用自：https://dba.stackexchange.com/questions/131891