Problem
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?
Solution
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
| SELECT * FROM fn_virtualfilestats(NULL,NULL); |
SQL 2000
| SELECT * FROM :: fn_virtualfilestats(-1, -1) |
The output for SQL 2000 and 2005 is pretty much the same, but some additional columns have been added for SQL Server 2005.
| Column Name | Notes | Description |
|---|---|---|
| DbId | Database ID. | |
| FileId | File ID. | |
| TimeStamp | Database timestamp at which the data was taken. | |
| NumberReads | Number of reads issued on the file. | |
| BytesRead | Number of bytes read issued on the file. | |
| IoStallReadMS | SQL2005 only | Total amount of time, in milliseconds, that users waited for the read I/Os to complete on the file. |
| NumberWrites | Number of writes made on the file. | |
| BytesWritten | Number of bytes written made on the file. | |
| IoStallWriteMS | SQL2005 only | Total amount of time, in milliseconds, that users waited for the write I/Os to complete on the file. |
| IoStallMS | Sum of IoStallReadMS and IoStallWriteMS. | |
| FileHandle | Value of the file handle. | |
| BytesOnDisk | SQL2005 only | 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 2005 Books Online)
Sample Output
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
| SELECT dbid, fileid, filename FROM sys.sysaltfiles WHERE dbid = 5 and fileid in (1,2) |
SQL 2000
| 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, but can easily be changed for SQL 2000.
USE master |
Summary
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
Next Steps
- 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
