Dynamic Management Views and Dynamic Management Functions - Microsoft SQL Server 2012 Performance Tuning - page 169

,CASE MF.type
WHEN 0 THEN 'Data File'
WHEN 1 THEN 'Log File'
END AS FileType
,PIOR.io_type AS InputOutputOperationType
,PIOR.io_pending AS Is_Request_Pending
,PIOR.io_handle
,PIOR.scheduler_address
FROM sys.dm_io_pending_io_requests AS PIOR
INNER JOIN sys.dm_io_virtual_file_stats(DB_ID('[AdventureWorks2012
]'),NULL) AS VFS
ON PIOR.io_handle = VFS.file_handle
INNER JOIN sys.master_files AS MF
ON VFS.database_id = MF.database_id AND VFS.file_id = MF.file_id
GO

How it works...

In the first query, we examined the data and log files for all the databases by using the sys.
dm_io_virtual_file_stats() function. It accepts the following two parameters:

Parameter name Description

database_id This is the internal ID of a database assigned by SQL Server. If

database_id is specified, then this function returns I/O statistics
details for specified database only. If database_id is NULL then I/O
statistics details for all databases is returned.

file_id This is the internal ID of a database file assigned by SQL Server. If

file_id is specified then this function returns I/O statistics details
for specified file of a particular database. If file_id is NULL then I/O
statistics details for all databases is returned.

Because we are passing NULL value for both parameters I/O statistics details for all the files
of all the databases will be returned. We joined the output of the sys.dm_io_virtual_
file_stats() function with the sys.master_files system catalog view on database_
id to retrieve database file details. The sys.master_files returns the list of data and the
log file details of all the databases. We determine whether a file is a data file or a log file by
checking the type column of sys.master_files .

We then executed the sample query shown against AdventureWorks2012 that simply
retrieves the records from Sales.SalesOrderDetails table. Before running this query we
cleared the data cache by running the DBCC DROPCLEANBUFFERS command to make sure
that the query reads data from the disk and not from the data cache.