SQL Server offers a system table-valued function that provides statistical information on the I/O activity of specific database files. It is called fn_virtualfilestats. What is interesting about this function is that it is specific to a database file. For example, if you want to see the I/O activity of a particular user database, you can. Keep in mind that all SQL Server databases have at least two physical files (for the database and log), and can have many more, depending on how the database was created. When using this function, you have to not only specify the database, but the file within the database. This is very specific I/O statistics indeed. To run this function, use the syntax found below:
SELECT * FROM :: fn_virtualfilestats(dbid, fileid)
Where:
dbid: The database ID of the database you want to examine.
fileid: The file ID of the physical files that make up your database. At a minimum, each database has at least two files: the database file (MDF file) and a log file (LDF). A database can have many files, and the file number refers to the number of the physical file that you want to examine.
To identify the dbid and fileid you want to use in the above statement, run the following SELECT statement. It will provide you with a list of all of the database names, database ids, and file ids (for each database) on your server.
SELECT sysdatabases.name AS Database_Name,
sysdatabases.dbid AS Database_ID,
sysaltfiles.fileid AS File_ID
FROM sysaltfiles INNER JOIN sysdatabases
ON sysaltfiles.dbid = sysdatabases.dbid
ORDER BY sysdatabases.name, sysaltfiles.fileid
Once you run this function, a wide variety of information is available, including:
NumberReads: The number of physical reads made against this file since the last time SQL Server was restarted.
NumberWrites: The number of physical writes made against this file since the last time SQL Server was restarted.
BytesRead: The number of bytes read from this file since the last time SQL Server was restarted.
BytesWritten: The number of writes to this file since the last time SQL Server was restarted.
IoStallMS: The total amount of time that users have waited for I/Os to complete for this file (in milliseconds).
The first four statistics can give you a feel for how busy a particular file is. This can come in handy when comparing multiple filegroups in a database and to see how balanced the I/O is to each file. To make the most of filegroups, I/O should be spread out among the various files for best overall performance. The last statistic, IoStallMS, is best used to find out if you have a bottleneck in your transaction log, as demonstrated below:
SELECT IoStallMS / (NumberReads+NumberWrites) as IsStall
FROM :: fn_virtualfilestats(dbid, fileid)
Where:
dbid: The database ID of the database you want to examine.
fileid: The file ID of the transaction log of the database being examined.
Essentially, if IsStall is > 20ms, then this indicates that the I/O to the transaction log is becoming a bottleneck, which in turn can lead to major concurrently problems in the database.
To help optimize the transaction log, assuming it has become a bottleneck, consider doing the following:
• Place the transaction log on a faster disk array.
• Place the transaction log on a dedicated disk array (no other files other than the transaction log). This allows sequential writes to occur as fast as possible, helping to boost I/O performance.
• Turn on write-back caching on your disk controller, but only if it is backed up with a battery and has been tested to work with SQL Server. Normally, write-back caching is turned off because it can cause database corruption should the server crash.
*****
Sometimes, a user thread has to wait until the resources it needs are available. Hopefully, this won’t happen often, but it is a fact of life. But sometimes, long waits can indicate potential performance problems that can be corrected, if you know where to look. Long waits can be caused by blocking locks, slow I/O, and other factors.
Fortunately, you can access the amount of time a user thread has to wait, which can tell you which user thread, if any, is taking more time that it should. For example, the query below can be run to identify any user threads that have to wait more than one second:
SELECT spid, waittime, lastwaittype
FROM master..sysprocesses
WHERE waittime > 1000
When you run the above query, all of the processes that have been waiting for greater than 1 second (1000 milliseconds) — both system and user — will be displayed. You will want to ignore system processes, focusing your efforts on spids that represent specific user threads. Spid will give you the user ID (you will have to match the spid to the appropriate user), waittime is the number of milliseconds that this user thread has been waiting, and lastwaittype will give you a clue as to what is waiting to occur.
In the above query, I have used 1000 milliseconds as a cutoff point, but you can use any amount of time you want. Generally speaking, any user thread that has been waiting for five seconds or more should definitely be evaluated for potential performance issues. Of course, you can choose any threshold you want.