主页 / dba / 问题 / 131891
Accepted
swasheck
Asked: 2016-03-11 12:59:48 +0800 CST

是否存在一种方法来确定包含多个文件的文件组中的分配单元的确切文件？

  • 772

我希望能够详细了解哪些数据库文件包含数据库中各种 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提供：

sql-server sql-server-2014

2 个回答

  1. Best Answer

    尝试以下查询。sys.dm_db_database_page_allocations它首先创建一个本地临时表，然后使用在 SQL Server 2012 中引入的未记录的动态管理函数 (DMF)中找到的 AllocationUnitID 到 FileID 关联填充它（对于 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;
    • 11

  2. Remus Rusanu 在 2013 年 5 月 21 日回答了这个问题：

    一个文件组，多个数据文件，如何获取每个文件中的表列表

    他的回应是：

    文件组中的对象将使用文件组中的所有数据文件。FG1 中的任何表均等地驻留在 Datafile1、Datafile2 和 Datafile3 上。如果您需要控制位置，则需要创建不同的文件组。

    • 1

相关问题