Início / dba / Perguntas / 131891
Accepted
swasheck
Asked: 2016-03-11 12:59:48 +0800 CST

Existe uma maneira de determinar o arquivo exato que contém uma unidade de alocação em um grupo de arquivos de vários arquivos?

  • 772

Eu esperava obter uma visão granular de quais arquivos de banco de dados continham quais unidades de alocação para os vários HoBTs (alinhados e não alinhados) que viviam em um banco de dados.

A consulta que sempre usei (veja abaixo) me serviu bem até começarmos a criar vários arquivos de dados por grupo de arquivos e só consigo descobrir como obter o nível granular do grupo de arquivos.

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;

No entanto, essa consulta não funcionará quando houver vários arquivos em um grupo de arquivos, pois só consigo relacionar uma unidade de alocação a um espaço de dados e, por fim, a um grupo de arquivos. Gostaria de saber se há outro DMV ou catálogo que estou perdendo e que posso usar para identificar melhor qual arquivo no grupo de arquivos contém uma unidade de alocação.

A questão por trás dessa questão é que estou tentando avaliar os efeitos reais da compactação de estruturas particionadas. Eu sei que posso fazer um antes e depois usando FILEPROPERTY(FileName,'SpaceUsed')para o arquivo e um antes e depois sys.allocation_units.used_pages/128.para obter essas informações, mas o próprio exercício me fez pensar se eu poderia identificar o arquivo específico que contém uma unidade de alocação específica.

Eu tenho brincado %%physloc%%na esperança de que isso possa ajudar, mas não me dá o que estou procurando. Os links abaixo foram fornecidos por Aaron Bertrand :

sql-server sql-server-2014

2 respostas

  1. Best Answer

    Tente a seguinte consulta. Ele primeiro cria uma tabela temporária local e a preenche com as associações AllocationUnitID-to-FileID encontradas em sys.dm_db_database_page_allocations, uma função de gerenciamento dinâmico (DMF) não documentada introduzida no SQL Server 2012 (para versões anteriores a 2012, você pode obter essas informações em DBCC IND()). Essa tabela temporária local é então unida a uma versão modificada da consulta original.

    Os dados desse DMF são colocados em uma tabela temporária para desempenho, pois, dependendo do tamanho do banco de dados, pode levar mais de alguns segundos para obter esses dados. A DISTINCTpalavra-chave é usada porque esse DMF retorna uma linha por página de dados e há várias páginas de dados por cada unidade de alocação.

    Juntei esses dados à esquerda na consulta original, pois a consulta original retorna unidades de alocação que têm 0 páginas de dados (normalmente ROW_OVERFLOW_DATAe LOB_DATAtipos). Também adicionei o total_pagescampo para que seja mais fácil relacionar esse ponto de dados às linhas que possuem NULLs para os arquivos de dados. Se você não se importa com as unidades de alocação que têm 0 linhas, seria bom mudar isso LEFT JOINpara ser um arquivo 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, em 21 de maio de 2013, respondeu a esta pergunta:

    Um grupo de arquivos, vários arquivos de dados, como obter uma lista de tabelas em cada arquivo

    Sua resposta foi:

    Um objeto em um grupo de arquivos usará todos os arquivos de dados no grupo de arquivos. Qualquer tabela em FG1 reside igualmente em Datafile1, Datafile2 e Datafile3. Se você precisar controlar o posicionamento, precisará criar grupos de arquivos distintos.

    • 1

relate perguntas