How to join the sys.allocation_units system view with the fn_virtualfilestats() system function view?

If you want to watch the I/O statistics of physical files related to a give table, you need to join sys.allocation_units system view with fn_virtualfilestats() as shown below:

-- fyi_get_io_stats_by_table
-- Copyright (c) FYIcenter.com 

select t.name, p.partition_id, p.rows, a.type_desc, 
   a.data_space_id, a.data_pages, f.file_id, 
   s.NumberReads, s.BytesRead, s.NumberWrites, s.BytesWritten
   from sys.objects t, sys.partitions p, sys.allocation_units a, 
      sys.database_files f, fn_virtualfilestats(DB_ID(),NULL) s
   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
      and f.file_id = s.fileid
   order by p.partition_id

NAME   PARTITION_ID  ROWS  DATA_SPACE_ID  DATA_PAGES  FILE_ID  NUMBERREADS  BYTESREAD  ...
ORDER  721467753      140  1              4           1        4156         44335872   ...
ORDER  721468408      140  1              2           1        4156         44335872   ...
ORDER  721469064      140  1              1           1        4156         44335872   ...

As you can see, this ORDER table has 3 partitions in a single physical file.

⇒ I/O Tests on INSERT Statements

⇐ What Is fn_virtualfilestats()

⇑ SQL Server System Views and Functions

⇑⇑ SQL Server Storage Tutorials