relatando o consumo de espaço para arquivos no grupo de arquivos - como fazer engenharia reversa do algoritmo espalhando dados entre arquivos

Gostaria de saber como calcular a distribuição dos dados consumidos por arquivo em um grupo de arquivos, voltando ao índice (HEAP, CLUSTERED, NONCLUSTERED) que o armazena. Minha intenção é definir qual I/O vai para onde no disco.

Chego ao data_space_idnível de sys.indexes, mostrando as páginas usadas, alocadas; e data_space_idtamanho de sys.filegroups. Portanto, chego onde o algoritmo ponderado (por proporção de espaço livre?) Para armazenar dados em arquivos dentro do grupo de arquivos entra em vigor. Eu posso juntar-me a sys.database_filesusar data_space_id.

De sys.dm_allocation_units (juntado a índices por object_Ide index_Id) obtenho partition_ID; juntando com sys.dm_partitionsavisa a contagem de linhas, páginas usadas e alocadas, permitindo um cálculo para mostrar também o que está livre por partição. Não é possível obter a partição para arquivo...?

Eu tenho uma consulta que distribuo DATA para FILE com base na proporção de páginas usadas por arquivo em um grupo de arquivos, aplicando essa proporção aos dados de índice armazenados no grupo de arquivos ao qual os arquivos pertencem.

Existe uma maneira melhor de detalhar os dados da tabela/índice para a alocação no nível do arquivo? (Medir em vez de calcular?)

Para indid = 0 ou indid = 1, dpages é a contagem de páginas de dados usadas.
Para indid > 1, dpages é a contagem de páginas de índice usadas.

Para indid = 0 ou indid = 1, usado é a contagem do total de páginas usadas para todos os dados de índice e tabela.
Para indid > 1, used é a contagem de páginas usadas para o índice.

Para indid = 0 ou indid = 1, reservado é a contagem de páginas alocadas para todos os índices e dados da tabela.
Para indid > 1, reservado é a contagem de páginas alocadas para o índice.

SQL:

Select  T.Name TableName,
        ISNULL(SI.Name, SI.type_desc) IndexName, 
    SI.index_id, 
    SI.type_desc, 
    SI.data_space_id,
    S.rows Rows,
    S.rowmodctr,
    PIx.avg_fragmentation_in_percent,
    PIx.fragment_count,
    PIx.avg_fragment_size_in_pages,
    CASE SI.Index_ID
    WHEN 0 THEN S.dpages
    WHEN 1 THEN S.dpages
    END DataPages,
    CASE 
    WHEN SI.index_id > 1
        THEN S.dpages
    END IndexPages,
    FileUsageRatio,
    S.dpages*FileUsageRatio RationedDataPagesToFile,
    physical_name,
    CASE SI.Index_ID
    WHEN 0 THEN S.Used 
    WHEN 1 THEN S.Used
    END UsedTableDataPages,
    CASE 
    WHEN SI.index_id > 1
        THEN S.Used
    END UsedIndexPages,
    CASE SI.Index_ID
    WHEN 0 THEN S.reserved 
    WHEN 1 THEN S.reserved
    END ReservedTableDataPages,
    CASE 
    WHEN SI.index_id > 1
        THEN S.Reserved
    END ReservedIndexPages,
    FG.name FileGroupName,
    FG_SpaceUsage.FG_AllocatePages,
    FG_SpaceUsage.FG_UsedPages,
    FG_SpaceUsage.FG_FreePages,
    FG.type_desc FileGroup_Type_desc,
    OIx.singleton_lookup_count,
    OIx.range_scan_count,
    OIx.page_io_latch_wait_count,
    OIx.page_io_latch_wait_in_ms,
    OIx.page_latch_wait_count,
    OIx.page_latch_wait_in_ms,
    OIx.row_lock_count,
    OIx.row_lock_wait_count,
    OIx.row_lock_wait_in_ms,
    OIx.page_lock_count,
    OIx.page_lock_wait_count,
    OIx.page_lock_wait_in_ms
from SYS.tables T
JOIN sys.indexes SI
ON T.Object_ID = SI.Object_ID
JOIN sys.filegroups FG
ON FG.Data_Space_ID = SI.Data_Space_ID
JOIN sys.partitions P
ON P.index_id = SI.index_id
AND P.object_id = SI.object_id
JOIN sys.allocation_units AU
ON AU.allocation_unit_id = P.partition_id
JOIN sys.dm_db_index_operational_stats(db_id(),NULL,NULL,NULL) OIx
ON OIx.index_id = P.index_id
AND OIx.object_id = P.object_id
JOIN sys.dm_db_index_physical_stats(db_id(),NULL,NULL,NULL,NULL) PIx
ON PIx.index_id = P.index_id
AND PIx.object_id = P.object_id
JOIN sys.sysindexes S
ON S.IndID = SI.Index_ID
AND S.ID = SI.Object_ID
JOIN (
SELECT Data_Space_ID, SUM(SIZE) FG_AllocatePages, SUM(UsedPages) FG_UsedPages,   SUM   (SIZE) - SUM(UsedPages) FG_FreePages
FROM SYS.database_files DBF
    CROSS APPLY (   SELECT FILEPROPERTY(DBF.name, 'SpaceUsed') AS UsedPages) PagesUsed
GROUP BY DBF.data_space_id
) FG_SpaceUsage
ON FG_SpaceUsage.data_space_id = FG.data_space_id
JOIN 
(
 SELECT CTE.data_space_id, CTE.file_ID, CTE.physical_name,      CTE.UsedPages*1.0/AGGREGATED.Total_UsedPages FileUsageRatio
 FROM (
 SELECT data_space_Id, File_ID, Physical_Name, Size, PagesUsed.UsedPages, Size-     PagesUsed.UsedPages FreePages
 FROM sys.database_Files DBF
 CROSS APPLY (  SELECT FILEPROPERTY(DBF.name, 'SpaceUsed') AS UsedPages) PagesUsed
 ) CTE
JOIN
    (SELECT Data_Space_ID, SUM(UsedPages) Total_UsedPages
     FROM (
            SELECT data_space_Id, File_ID, Physical_Name, Size, PagesUsed.UsedPages, Size-PagesUsed.UsedPages FreePages
            FROM sys.database_Files DBF
            CROSS APPLY (   SELECT FILEPROPERTY(DBF.name, 'SpaceUsed') AS UsedPages) PagesUsed
            ) CTE
     GROUP BY Data_Space_ID
     ) AGGREGATED
ON CTE.data_space_id = AGGREGATED.data_space_id
) DataUsageRatio
ON DataUsageRatio.data_space_id = FG.data_space_id

ORDER BY TableName, Index_ID