다음 쿼리를 시도하십시오. 먼저 로컬 임시 테이블을 만든 다음 sys.dm_db_database_page_allocationsSQL Server 2012에 도입 된 문서화되지 않은 DMF (Dynamic Management Function)에 있는 AllocationUnitID-FileID 연결로 채 웁니다 (2012 이전 버전의 경우이 정보를 얻을 수 있음 DBCC IND()). 그런 다음 해당 로컬 임시 테이블은 원래 쿼리의 수정 된 버전으로 결합됩니다.

데이터베이스의 크기에 따라 해당 데이터를 가져 오는 데 몇 초 이상 걸릴 수 있으므로 해당 DMF의 데이터는 성능을 위해 임시 테이블에 배치됩니다. DISTINCTDMF는 데이터 페이지 당 하나 개의 행을 반환하기 때문에 키워드가 사용되며, 각 할당 단위에 여러 데이터 페이지가있다.

나는 왼쪽 합류 0 데이터 페이지 (일반적으로 가지고있는 원래의 쿼리를 반환 할당 단위부터 원래의 질의에 데이터 ROW_OVERFLOW_DATA및 LOB_DATA유형). 또한 total_pages해당 데이터 포인트를 NULL데이터 파일의 행과 더 쉽게 연관시킬 수 있도록 필드를 추가했습니다 . 행이 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;

답변