Gathering I/O statistics down to the SQL Server database file level
Written By: Greg Robidoux -- 1/21/2008
-- read/post comments
-- print --
Rating:
(not rated yet)
Rate
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
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 - 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
|
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.
Readers Who Read This Tip Also Read
Free Live Webcast
Comment or Ask Questions About This Tip
|
