reporting space consumption to files in filegroup - how to reverse-engineer the algorithm spreading data across files

I would like to know how to calculate the distribution of data consumed per file in a filegroup, back to the index (HEAP, CLUSTERED, NONCLUSTERED) storing it. My intention is to define which I/O goes where on disk.

I get to data_space_id level from sys.indexes, showing pages used, allocated; and data_space_id size from sys.filegroups. So I get to where the weighted (by free space ratio?) algorithm for storing data to files within the filegroup takes effect. I can join to sys.database_files using data_space_id.

From sys.dm_allocation_units (joined to indexes by object_Id and index_Id) I get partition_ID; joining with sys.dm_partitions advises row count, pages used and allocated, allowing a calculation to show also what is free per partition. Can't get to partition to file...?

I have a query which I apportion DATA to FILE based on ratio of used pages per file in a filegroup, applying this ratio to the index data stored on the file group the files belong to.

Is there a better way of drilling down table/index data to file level allocation? (Measure instead of compute?)

For indid = 0 or indid = 1, dpages is the count of data pages used.

For indid > 1, dpages is the count of index pages used.

For indid = 0 or indid = 1, used is the count of the total pages used for all index and table data.

For indid > 1, used is the count of pages used for the index.

For indid = 0 or indid = 1, reserved is the count of pages allocated for all indexes and table data.

For indid > 1, reserved is the count of pages allocated for the index.

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

Upon consideration, I can't do what I want to do, better than I am doing it. SQL Server doesn't store what goes where deeper than the filegroup level it seems. From there it's all internals only; and no exposed methods to view.

Background links:

Round Robin vs. Proportional Fill by Rob Nicholson

Understanding the -E Startup Parameter by James Rowland-Jones

StackExchange Database Administrators Q#84696, answer score: 2