Czy istnieje sposób na określenie dokładnego pliku zawierającego jednostkę alokacji w grupie plików wielu plików?

Miałem nadzieję uzyskać szczegółowy widok, które pliki bazy danych zawierały, które jednostki alokacji dla różnych HoBT (zarówno wyrównanych, jak i niezrównanych) żyjących w bazie danych.

Zapytanie, którego zawsze używałem (patrz poniżej), dobrze mi służyło, dopóki nie zaczęliśmy tworzyć wielu plików danych na grupę plików i jestem w stanie dowiedzieć się, jak uzyskać stopień szczegółowości na poziomie grupy plików.

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;

Jednak to zapytanie nie będzie działać, gdy w grupie plików jest wiele plików, ponieważ mogę uzyskać tylko tyle, aby powiązać jednostkę alokacji z przestrzenią danych i, ostatecznie, aplikacjami. Chciałbym wiedzieć, czy istnieje inny DMV lub katalog, którego mi brakuje, którego mogę użyć do dalszego zidentyfikowania, który plik w grupie plików zawiera jednostkę alokacji.

Pytanie kryjące się za tym pytaniem polega na tym, że próbuję ocenić rzeczywiste skutki kompresji struktur partycjonowanych. Wiem, że mogę użyć pliku przed i po FILEPROPERTY(FileName,'SpaceUsed')nim oraz przed i po, sys.allocation_units.used_pages/128.aby uzyskać te informacje, ale samo ćwiczenie sprawiło, że zastanawiałem się, czy mogę zidentyfikować konkretny plik zawierający określoną jednostkę alokacji.

Mieszałem się %%physloc%%w nadziei, że to może pomóc, ale nie do końca rozumiem, czego szukam. Poniższe linki zostały dostarczone przez Aarona Bertranda :

• Gdzie są moje rzędy? - Korzystanie z wirtualnej kolumny %% physloc %% (sqlity.net)
• SQL Server 2008: Nowa (nieudokumentowana) funkcja fizycznego lokalizatora wierszy autorstwa Paula Randala

Wypróbuj następujące zapytanie. Najpierw tworzy lokalną tabelę tymczasową, a następnie zapełnia ją powiązaniami AllocationUnitID-to-FileID znajdującymi się w sys.dm_db_database_page_allocationsnieudokumentowanej funkcji zarządzania dynamicznego (DMF) wprowadzonej w SQL Server 2012 (dla wersji wcześniejszych niż 2012, można uzyskać te informacje z DBCC IND()). Lokalna tabela temp jest następnie ŁĄCZONA w zmodyfikowaną wersję pierwotnego zapytania.

Dane z tego DMF są umieszczane w tabeli tymczasowej w celu zwiększenia wydajności, ponieważ w zależności od wielkości bazy danych uzyskanie tych danych może zająć więcej niż kilka sekund. Słowo DISTINCTkluczowe jest używane, ponieważ DMF zwraca jeden wiersz na stronę danych, a na każdą jednostkę alokacji przypada wiele stron danych.

Dołączyłem te dane do pierwotnego zapytania, ponieważ pierwotne zapytanie zwraca jednostki alokacji, które mają 0 stron danych (zazwyczaj ROW_OVERFLOW_DATAi LOB_DATAtypy). Dodałem również total_pagespole, aby łatwiej było powiązać ten punkt danych z wierszami zawierającymi NULLs dla plików danych. Jeśli nie przejmujesz się jednostkami alokacji, które mają 0 wierszy, dobrze byłoby zmienić to LEFT JOINna 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;

Remus Rusanu, 21 maja 2013 r., Udzielił odpowiedzi na to pytanie:

Jedna grupa plików, wiele plików danych, jak uzyskać listę tabel w każdym pliku

Jego odpowiedź brzmiała:

Obiekt w grupie plików używa wszystkich plików danych w grupie plików. Każda tabela w FG1 znajduje się w równym stopniu na Datafile1, Datafile2 i Datafile3. Jeśli chcesz kontrolować umieszczanie, musisz utworzyć odrębne aplikacjami.