SQL Server is a resource-intensive database system that requires adequate CPU and memory capacity to run transactions with acceptable performance. Another resource that can cause poor performance is the storage subsystem. We should carefully plan storage subsystems for SQL Server and follow Microsoft best practices to place files on multiple storage systems to reduce IO overheads and improve database performance. Sometimes, we start facing performance issues despite having all the best practices in place and adequate computing resources allocated to the SQL Server. The reason behind this could be anything from code level changes to the increased workload to poor IO response time etc.

Today, I will explain how to check IO statistics for each database file of a database which is very important if you are troubleshooting an IO-related performance issue. SQL Server offers a system function, fn_virtualfilestats, using which we can get IO statistics of each database file of a database. We can also use a DMV, sys.dm_io_virtual_file_stats, to get a similar result for database files. I will explain this DMV as well in this article.

Syntax

The syntax to use this function is given below:

fn_virtualfilestats ( { db_id | NULL } , { file_id | NULL } )

Here,

• db_id is the id of a database
• file_id is the id of a database file
• NULL, if you specify NULL it will return information about all files or all databases

The syntax to use the above DMV is given below:

sys.dm_io_virtual_file_stats ( { db_id | NULL },   { file_id | NULL })

Here,

• db_id is the id of a database
• file_id is the id of a database file
• NULL, if you specify NULL it will return information about all files or all databases

Use Cases

This section will demonstrate the use cases of both system objects. Let’s understand the source database on which we will run this function and DMV. First launch SQL Server management studio or SQL tools to execute below queries used in this section.

The tempdb database is one of the crucial system databases involved in processing most transactions, so let’s do this exercise on this database. Run the below simple SQL query to get the database id and its respective file ids.

SELECT DB_ID(‘tempdb’) AS [DB ID]
GO
Sp_helpdb ‘tempdb’
GO

We can see the below image in which we have the database id of system database tempdb along with its database file ids highlighted in red.

We got the database id of tempdb as 2 and its file ids are 1, 2 and 3. Next, we will use these ids in the above system function and DMVs to return their IO statistics.

fn_virtualfilestats

Here, I want to understand the IO statistics of the tempdb log file. We can see the file id of the tempdb log file is 3, so we will specify the DB id argument as 2 and the file id argument as 3 in the function fn_virtualfilestats.

SELECT *
FROM fn_virtualfilestats(2,3)
GO

Have a look at the below output. We can see various useful statistics have been returned for the specified file id. We can see the number of reads and writes, size of reads and write, stall details, etc. Next, you can correlate this information and analyze whether your IO is adequate and acceptable as per the approved baseline performance if you need to improve it.

If you do not know the database id, then you can simply specify another system function DB_ID(), along with the target database name in this function to return its output. You can see I have specified DB_ID() function and passed the tempdb database name along with its file id 3 to return the same result set I returned in the above image.

SELECT *
FROM fn_virtualfilestats(DB_ID(‘tempdb’),3)
GO

You can see the output of the above query in the below image.

Next, I will show you the following use case by specifying the NULL value. As I stated above, if we specify NULL in place of any argument, it will return statistics of all databases or their files.

If you specify:

• NULL as db_id argument, it will return statistics information of all databases hosted on that instance
• NULL as file_id argument, it will return statistics information of all database files of the specified database
• NULL as both db_id and file_id then the output will return statistics information of all database files of all databases hosted on that SQL Server instance

Let’s understand it with the help of an example.

SELECT *
FROM fn_virtualfilestats(2, NULL)
GO

Here, I have specified the file_id argument as NULL for database id 2, which is tempdb. The output will return IO statistics of all database files created under database tempdb. Let’s run the above query and see its output. We can see the below image, which is the output of the above query. Here, we can see the IO details of all files of database tempdb.

Similarly, I have specified the db_id argument as NULL and file_id as 2. It means the output will return IO statistics of file id 2 of all databases hosted on that instance.

SELECT *
FROM fn_virtualfilestats(NULL, 2)
GO

Have a look at the output below. The IO statistics have been returned for each file id 2 of all databases from that instance where it is hosted.

Next, I have specified NULL as both arguments in the below query.

SELECT *
FROM fn_virtualfilestats(NULL, NULL)
GO

The output of the above query shows that IO statistics have been returned for all files of all databases hosted on the SQL Server instance.

DMV sys.dm_io_virtual_file_stats

This DMV provides the same information about database file IO statistics of a database that the above function fn_virtualfilestats provides. I have described its syntax in the first section of this article. Here. I will explain its use cases so you can understand how to use it.

Here also, we need two arguments db_id and respective file_id, about which we want to display the IO statistics. If you don’t have the database id then you can use DB_ID() function in this DMV. Look at the below query where I specified the tempdb database in another function DB_ID and specified its log file id to return its result.

SELECT * FROM sys.dm_io_virtual_file_stats(DB_ID(N'tempdb'), 3);  
GO

You can use this DMV, in the same manner, I use the above function.

Let me show you an example of both system objects and compare their output. I will use the db id as 2 and the file id as NULL as shown in the below query.

SELECT *  
FROM fn_virtualfilestats(2, NULL)
GO
SELECT *  
FROM sys.dm_io_virtual_file_stats(2, NULL)
GO

Have a look at the output of the above query. Both results are the same, the first output is returned by function fn_virtualfilestats, whereas the second result is returned by DMV sys.dm_io_virtual_file_stats.

Conclusion

I have explained how to return IO statistics of database files using a system function fn_virtualfilestats and a DMV sys.dm_io_virtual_file_stats in this article. I have also explained various use cases of both system objects. You can go ahead and learn these use cases to understand them better.