Home > SQL Server > Get Database file I/O statistics information – SQL Server

Get Database file I/O statistics information – SQL Server


fn_virtualfilestats system table-valued function and dm_io_virtual_file_stats DMV function are used to get the database and log file(s) I/O statistics information. DMV function is the latest one and it is always better to use DMV function in the new application.

These functions need database id and file id in arguments to get the file io statistics information. If we use null in both argument then it will return io statistics information for all databases in the instance.

The values return by these functions are cumulative value since the SQL server was last started.

Implementation

1. Execute fn_virtualfilestats system table-valued function.

SQL Script:

–Reading Data file io stat for the database ‘Data_lab’
SELECT *
FROM fn_virtualfilestats(DB_ID(‘Data_lab’), file_id(‘Data_lab’));
GO

–Reading log file io stat for the database ‘Data_lab’
SELECT *
FROM fn_virtualfilestats(9, 2);
GO

pic12. Execute DMV function dm_io_virtual_file_stats

SQL Script

–Reading Data file io stat for the database ‘Data_lab’
SELECT *
FROM sys.dm_io_virtual_file_stats (DB_ID(‘Data_lab’), file_id(‘Data_lab’));
GO

–Reading file io statistics information for all databases in the instance
SELECT *
FROM sys.dm_io_virtual_file_stats (null, null);
GO

pic2

This io statistics data is very useful to understand the read and write activities happen in the files system. This data is the key information for troubleshooting IO problems in the SQL server.

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: