Collections:
Find the File IDs of a Given Table
How to find the File IDs of a Given Table
✍: FYIcenter.com
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:
Here is the logic you can follow to find which files are used to store a given table:
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
2019-06-29, 1184🔥, 0💬
Popular Posts:
How to generate ISBN numbers? To help you to obtain some ISBN numbers for testing purpose, FYIcenter...
How to convert IPv4 to IPv6 addresses? In order to help your programming or testing tasks, FYIcenter...
How to valid IPv6 addresses? In order to help your programming or testing tasks, FYIcenter.com has d...
How to call JMeter command in a Windows batch file? I want to create a single batch job to run JMete...
How to generate user birthday dates? Test user birthday dates are frequently needed in testing appli...