When managing your SQL Server environment there are many aspects that need to be reviewed to determine where the bottlenecks are occurring to ensure you are getting the best performance possible. SQL Server offers many great tools and functions to determine issues with locking, blocking, fragmentation, missing indexes, deadlocks, etc... In addition, to looking at all of these areas another area of concern is I/O. Disk I/O can be tracked at the OS level by using counters in Performance Monitor, but these counters give you an overall picture of what is occurring on the server. What other options are available to look at I/O related information down to the file level for each database?
As mentioned above, SQL Server offers many great little functions and utility programs to help you gain an insight as to what is occurring on the server. One of these tools is fn_virtualfilestats.
This function, fn_virtualfilestats allows you to get information for each physical file that is being used to hold your data including both the data and log files. The function returns read and write information as well as stall information, which is the time users had to wait for an I/O operation to complete. Each time this function is called it returns the overall numbers that SQL Server has collected since the last time the database engine was started, so to use this effectively you need to gather data from two different points of time and then do a comparison.
To run this function to get data for all databases and all files this can be done as easily as this:
SQL 2005, 2008, 2008R2, 2012
SELECT * FROM fn_virtualfilestats(NULL,NULL); --or you can use this DMV SELECT * FROM sys.dm_io_virtual_file_stats(NULL,NULL);
SELECT * FROM :: fn_virtualfilestats(-1, -1)
The output for SQL 2000 and the other editions is pretty much the same, but some additional columns have been added after SQL Server 2000.
Database timestamp at which the data was taken.
Number of reads issued on the file.
Number of bytes read issued on the file.
not in 2000
Total amount of time, in milliseconds, that users waited for the read I/Os to complete on the file.
Number of writes made on the file.
Number of bytes written made on the file.
not in 2000
Total amount of time, in milliseconds, that users waited for the write I/Os to complete on the file.
Sum of IoStallReadMS and IoStallWriteMS.
Value of the file handle.
not in 2000
Physical file size (count of bytes) on disk.
For database files, this is the same value as size in sys.database_files, but is expressed in bytes rather than pages.
For database snapshot spare files, this is the space the operating system is using for the file.
(Source SQL Server Books Online)
As you can see from the sample output, the Dbid and FileId columns are pretty cryptic. The Dbid can be be translated to the database name pretty easily by using the DB_NAME() function, but the fileId needs to be looked up from one of the system tables.
To lookup the filename from the system tables you can use these queries.
SQL 2005, 2008, 2008R2, 2012
SELECT dbid, fileid, filename FROM sys.sysaltfiles WHERE dbid = 5 and fileid in (1,2)
SELECT dbid, fileid, filename FROM dbo.sysaltfiles WHERE dbid = 5 and fileid in (1,2)
Here is sample output.
From just using this function directly you can gather data from two different points in time and then do a comparison to determine the change that has occurred between these two periods of time. Here is a sample query that gathers data, waits for a period of time and then gathers data again to show you a comparison.
This example is written for SQL Server 2005 and forward, but can easily be changed for SQL 2000.
USE master GO
-- create table IF NOT EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[filestats]') AND type IN (N'U')) BEGIN CREATE TABLE filestats (dbname VARCHAR(128), fName VARCHAR(2048), timeStart datetime, timeEnd datetime, timeDiff bigint, readsNum1 bigint, readsNum2 bigint, readsBytes1 bigint, readsBytes2 bigint, readsIoStall1 bigint, readsIoStall2 bigint, writesNum1 bigint, writesNum2 bigint, writesBytes1 bigint, writesBytes2 bigint, writesIoStall1 bigint, writesIoStall2 bigint, ioStall1 bigint, ioStall2 bigint ) END
-- clear data TRUNCATE TABLE dbo.filestats
-- insert first segment counters INSERT INTO dbo.filestats (dbname, fName, TimeStart, readsNum1, readsBytes1, readsIoStall1, writesNum1, writesBytes1, writesIoStall1, IoStall1 ) SELECT DB_NAME(a.dbid) AS Database_name, b.filename, GETDATE(), numberReads, BytesRead, IoStallReadMS, NumberWrites, BytesWritten, IoStallWriteMS, IoStallMS FROM fn_virtualfilestats(NULL,NULL) a INNER JOIN sysaltfiles b ON a.dbid = b.dbid AND a.fileid = b.fileid ORDER BY Database_Name
/*Delay second read */ WAITFOR DELAY '000:01:00'
-- add second segment counters UPDATE dbo.filestats SET timeEnd = GETDATE(), readsNum2 = a.numberReads, readsBytes2 = a.BytesRead, readsIoStall2 = a.IoStallReadMS , writesNum2 = a.NumberWrites, writesBytes2 = a.BytesWritten, writesIoStall2 = a.IoStallWriteMS, IoStall2 = a.IoStallMS, timeDiff = DATEDIFF(s,timeStart,GETDATE()) FROM fn_virtualfilestats(NULL,NULL) a INNER JOIN sysaltfiles b ON a.dbid = b.dbid AND a.fileid = b.fileid WHERE fName= b.filename AND dbname=DB_NAME(a.dbid)
-- select data SELECT dbname, fName, timeDiff, readsNum2 - readsNum1 AS readsNumDiff, readsBytes2 - readsBytes1 AS readsBytesDiff, readsIoStall2 - readsIOStall1 AS readsIOStallDiff, writesNum2 - writesNum1 AS writesNumDiff, writesBytes2 - writesBytes1 AS writesBytesDiff, writesIoStall2 - writesIOStall1 AS writesIOStallDiff, ioStall2 - ioStall1 AS ioStallDiff FROM dbo.filestats
One problem that you may be faced with though is that not all files are stored on their own physical disks, so you may have a case where you want to look at things from a drive perspective vs. at an individual file level. Here is a previous article written by Andy Novick that has the entire process broken down into functions, so you can aggregate things to a drive perspective. The article can be found here, Examining SQL Server's I/O Statistics
When researching performance problems, don't forget to look at I/O stats as well. This handy little function could give you big insight into some of your performance issues.
Stay tuned for more performance related tips, but for now check out these other tips.
Last Update: 6/10/2013
About the author
Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.
--mistyping in final select SELECT dbname, fName, timeDiff, readsNum2 - readsNum1 AS readsNumDiff, readsBytes2 - readsBytes2 AS readsBytesDiff, readsIoStall2 - readsIOStall1 AS readsIOStallDiff, writesNum2 - writesNum1 AS writesNumDiff, writesBytes2 - writesBytes2 AS writesBytesDiff, writesIoStall2 - writesIOStall1 AS writesIOStallDiff, ioStall2 - ioStall1 AS ioStallDiff FROM dbo.filestats
readsBytesDiff & writesBytesDiff always 0
Monday, June 10, 2013 - 9:50:03 AM - Greg Robidoux
Select DB_NAME(mf.database_id) as DBName,name as Logical_Name ,physical_name ,io_stall_read_ms , io_stall_write_ms,num_of_writes , io_stall_read_ms + io_stall_write_ms as io_stalls ,num_of_reads + num_of_writes as total_io,cast((io_stall_read_ms+io_stall_write_ms)/(1.0+num_of_reads + num_of_writes) as numeric(10,1)) from sys.dm_io_virtual_file_stats(null,null) vfs left join sys.master_files mf on mf.database_id = vfs.database_id and mf.file_id = vfs.file_id order by io_stall_read_ms desc
Run it from a sqlps session, pipe to cvs for review and hand off, edit the path at the end and keep on 1 line in the .ps1 file.
invoke-sqlcmd -ServerInstance sqlshark -Database msdb –Query "Select DB_NAME(mf.database_id) as DBName,name as Logical_Name ,physical_name ,io_stall_read_ms , io_stall_write_ms,num_of_writes , io_stall_read_ms + io_stall_write_ms as io_stalls ,num_of_reads + num_of_writes as total_io,cast((io_stall_read_ms+io_stall_write_ms)/(1.0+num_of_reads + num_of_writes) as numeric(10,1)) from sys.dm_io_virtual_file_stats(null,null) vfs left join sys.master_files mf on mf.database_id = vfs.database_id and mf.file_id = vfs.file_id order by io_stall_read_ms desc" | Export-Csv –path C:\powershell_scripting\dmv_pssss\output\DMV_results_IOReadStalls_$(get-date -f MM_dd_yyyy_HHmm).csv -notype
running the code at a busy time was difficult, but I could add calls to the beginning and ending of long running procedures to see how those procedures were performing. To do that I came up with the code below. Basically it just records the snapshot in the table and then, instead of doing the 'wait' thing, it is just makes a call later in your procedure-- connects the dots. Does this make sense?