Query SQL Server Database File I/O Statistics

SQL Server

A SQL Server instance with one database (or hundreds) often has its database files spread across multiple physical drives to help with the read/write performance. However, as more and more databases are added to the server, the read/write activity among the drives can become unbalanced and slower than acceptable. One drive could be taking a heavier workload than others and become the performance bottleneck. When these sorts of read/write problems occur, you need to monitor the activity of all the drives on Performance Monitor, compare their workloads, measure their performance, and identify the bottlenecks.

Some of the perfomance counters regarding disk read/write are listed below.

PhysicalDisk\% Disk Time
PhysicalDisk\Avg. Disk Queue Length
PhysicalDisk\Avg. Disk Read Queue Length
PhysicalDisk\Avg. Disk Write Queue Length
LogicalDisk\% Disk Time
LogicalDisk\% Disk Read Time
LogicalDisk\% Disk Write Time
LogicalDisk\Avg. Disk Queue Length
LogicalDisk\Avg. Disk Read Queue Length
LogicalDisk\Avg. Disk Write Queue Length
LogicalDisk\Disk Bytes/sec

From the performance data, if a drive is busy for a much higher percentage of time or its disk queue length is greater than acceptable, you might want to move some database files from the drive to other drives with a lighter workload, or configure a new drive to allieviate the read/write pressure. To decide which database files to move, you should inspect the activity of each database file so you can estimate how the drives will perform after any reorganization. Each database has different levels of activity. It is not always that the faster-growing or larger databases are busier or causing the indicated issue. In this example you will see a simple query for the read/write statistics of each database file, including reads, writes, and time spent waiting for reads and writes.

In SQL Server 2005 and higher, a new dynamic management function sys.dm_io_virtual_file_stats is available. This function accepts two parameters, a database id and a file id. When both parameters are null, then this function returns I/O statistics for all data and log files.

The read/write statistics for all the data and log files has been returned. The columns regarding read/write activity are described as follows.

sample_ms – Number of milliseconds since the computer was started. This column can be used to compare different outputs from this function.

num_of_reads – Number of reads issued on the file.

num_of_bytes_read – Total number of bytes read on this file.

io_stall_read_ms – Total time, in milliseconds, that the users waited for reads issued on the file.

num_of_writes – Number of writes made on this file.

num_of_bytes_written – Total number of bytes written to the file.

io_stall_write_ms – Total time, in milliseconds, that users waited for writes to be completed on the file.

io_stall – Total time, in milliseconds, that users waited for I/O to be completed on the file.

However, the database_id and file_id columns in the result set don’t provide a direct link from the read/write statistics to the file name or path. We need to join the table returned by sys.dm_io_virtual_file_stats with the sys.master_files catalog view to find out on which drive the files reside.

select   db_name(mf.database_id) as database_name,
         mf.physical_name, 
         UPPER(left(mf.physical_name, 1)) as drive_letter, 
         vfs.num_of_writes, 
         vfs.num_of_bytes_written, 
         CAST(io_stall_write_ms / ( 1.0 + num_of_writes ) AS NUMERIC(10, 1)) AS [avg_write_stall_ms] ,
         vfs.io_stall_write_ms, 
         mf.type_desc, 
         vfs.num_of_reads, 
         vfs.num_of_bytes_read, 
         CAST(io_stall_read_ms / ( 1.0 + num_of_reads ) AS NUMERIC(10, 1)) AS [avg_read_stall_ms] ,
         vfs.io_stall_read_ms,
         vfs.io_stall, 
         vfs.size_on_disk_bytes
from sys.master_files mf join 
     sys.dm_io_virtual_file_stats(NULL, NULL) vfs
     on mf.database_id=vfs.database_id 
     and mf.file_id=vfs.file_id
order by avg_write_stall_ms desc

In SQL Server 2000, the sys.dm_io_virtual_file_stats function doesn’t exist. You can use the fn_virtualfilestats function instead to get similar results. This function doesn’t accept NULL as a valid parameter value. You can use a cursor to iterate through all database files and get similar statistics to the query above.

DECLARE @DBID smallint
DECLARE @FILEID smallint
DECLARE @DBNAME sysname
DECLARE @FILENAME nvarchar(260)

CREATE TABLE #FileIOStats 
   (name sysname,
   filename nvarchar(260),
   drive_letter char(1),
   NumberReads bigint, 
   NumberWrites bigint, 
   BytesRead bigint, 
   BytesWritten bigint, 
   IoStallMS bigint)  --IoStallMS is the cumulative number of milliseconds of I/O waits for a particular file.

DECLARE ALLFILES CURSOR FOR
SELECT dbid, fileid, [name], [filename] FROM [master].[dbo].[sysaltfiles] 

OPEN ALLFILES
FETCH NEXT FROM ALLFILES INTO @DBID, @FILEID, @DBNAME, @FILENAME

WHILE (@@FETCH_STATUS = 0)
BEGIN

                INSERT INTO #FileIOStats
                SELECT @DBNAME, @FILENAME, UPPER(left(@FILENAME, 1)), NumberReads, NumberWrites, BytesRead, BytesWritten, IoStallMS 
                FROM ::fn_virtualfilestats(@DBID, @FILEID) 

                FETCH NEXT FROM ALLFILES INTO @DBID, @FILEID, @DBNAME, @FILENAME
END

CLOSE ALLFILES
DEALLOCATE ALLFILES

SELECT * FROM #FileIOStats
DROP TABLE #FileIOStats

You can get more information from TechNet. You could also read this article, or this article, or this article.

Advertisements

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