How to find the File IDs of a Given Table

If you are performing an I/O test on a specific table and want to use the fn_virtualfilestats() function to gather I/O statistics, you need to follow this tutorial to find which files is used to store a given table.

To find out which files are used to store a given table, we need to remember how data records are stored in SQL Server database:

• Data in a table are stored as partitions (only 1 partition in most cases).
• A partition is allocated to a logical filegroup.
• A filegroup is mapped to one or more physical files.

Here is the logic you can follow to find which files are used to store a given table:

• Use the sys.objects view or OBJECT_ID() function to get the object_id for the given table.
• Use the sys.partitions view to get a hobt_id list for the given object_id. A hobt_id is unique to a specific partition.
• Use the sys.allocation_units view to get a data_space_id list for the given hobt_id list. A data_space_id is unique to a specific logical filegroup.
• Use the sys.database_files view to get a file_id list for the given data_space_id list. A file_id is unique to a specific physical file.

For example, we can implement the above logic in query to find all physical files used by the "ORDER" table:

select t.name, t.object_id, p.partition_id, p.rows, 
   a.data_space_id, a.data_pages, f.file_id, f.physical_name
   from sys.objects t, sys.partitions p, sys.allocation_units a, sys.database_files f
   where t.name = 'ORDER'
      and t.object_id = p.object_id 
      and p.hobt_id = a.container_id
      and a.data_space_id = f.data_space_id
   order by p.partition_id

name   object_id  partition_id  rows  data_space_id  data_pages  file_id  physical_name
ORDER  720515     1361707008    4398  2              415         7        D:\DATA\FILE_3.MDF
ORDER  720515     1361707008    4398  2              415         8        D:\DATA\FILE_4.MDF
ORDER  720515     1361772544    4398  2              203         7        D:\DATA\FILE_3.MDF
ORDER  720515     1361772544    4398  2              203         8        D:\DATA\FILE_4.MDF
ORDER  720515     1361838080    4398  8              1152        18       D:\DATA\FILE_6.MDF

As you can see, the ORDER table is divided into 3 partitions. The first two partitions (1361707008 and 1361772544) is allocated in one filegroup (data_space_id=2). The third partition (1361838080) is allocated in another filegroup (data_space_id=8). But the first filegroup (data_space_id=2) is mapped to 2 physical files, so don't really know exactly in which physical file the first two partitions will be using.

⇒ What Is fn_virtualfilestats()

⇐ What Is sys.allocation_units

⇑ SQL Server System Views and Functions

⇑⇑ SQL Server Storage Tutorials