向文件組中的文件報告空間消耗 - 如何對跨文件傳播數據的算法進行逆向工程

我想知道如何計算文件組中每個文件消耗的數據分佈，返回到儲存它的索引（HEAP、CLUSTERED、NONCLUSTERED）。我的目的是定義哪些 I/O 在磁碟上的位置。

我從 到達data_space_id級別sys.indexes，顯示已使用、已分配的頁面；和data_space_id大小從sys.filegroups。所以我得到了用於將數據儲存到文件組中的文件的加權（按可用空間比率？）算法生效的地方。我可以加入sys.database_files使用data_space_id。

從s （通過andsys.dm_allocation_unit加入索引）我得到; 加入建議行數，使用和分配的頁面，允許計算顯示每個分區的可用空間。無法分區到文件…？object_Id``index_Id``partition_ID``sys.dm_partitions

我有一個查詢，我根據文件組中每個文件的已用頁的比率將 DATA 分配給 FILE，將此比率應用於儲存在文件所屬的文件組上的索引數據。

有沒有更好的方法將表/索引數據向下鑽取到文件級分配？（測量而不是計算？）

對於 indid = 0 或 indid = 1，dpages 是使用的數據頁數。

對於 indid > 1，dpages 是使用的索引頁數。

對於 indid = 0 或 indid = 1，used 是用於所有索引和表數據的總頁數。

對於 indid > 1，used 是用於索引的頁數。

對於 indid = 0 或 indid = 1，reserved 是為所有索引和表數據分配的頁數。

對於 indid > 1，reserved 是為索引分配的頁數。

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

經過考慮，我不能做我想做的事，比我正在做的更好。SQL Server 不會儲存比看起來更深的文件組級別的內容。從那裡開始，它只是內部的；並且沒有公開的查看方法。

背景連結：

Rob Nicholson的Round Robin vs. Proportional Fill理解James Rowland-Jones

的 -E 啟動參數

有沒有更好的方法將表/索引數據向下鑽取到文件級分配？

要查看堆/索引之間的關係以及它們的數據儲存在哪個文件中，請參閱我對以下問題的回答：

是否存在一種方法來確定包含多個文件的文件組中的分配單元的確切文件？

我的目的是定義哪些 I/O 在磁碟上的位置。

不，SQL Server 如何跨可用文件分配數據是不可配置的。

引用自：https://dba.stackexchange.com/questions/84696