Gathering I/O statistics down to the SQL Server database file level

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.

How To Collect Performance Data With TYPEPERF.EXE

Problem
As a DBA I like to take advantage of command line tools when I'm working on performance tuning and optimizing my SQL Server databases. One of the things I typically need to do is to collect performance data on the server which includes CPU, memory and disk utilization as well as SQL Server-specific data. What command line tools are available to do this?

Solution

TYPEPERF.EXE is a command line tool included with the Windows operating system that writes performance data to the command window or to a file. It is necessary to capture performance data whenever you are trying to diagnose performance issues on a server. Performance data provides information on the server's utilization of the processor, memory, and disk, as well as SQL Server-specific performance data.

The Windows operating system supplies a plethora of performance data in the form of objects which have associated counters. As an example SQL Server provides the SQLServer:General Statistics object which reports the details on logins, logouts, database connections, etc. Some objects break out the counters into specific instances. As an example the SQLServer:Databases object provides details on data file and transaction log file sizes, the percentage of the transaction log in use, active transactions, etc. for each database. You can specify a single database or all databases combined together as the instance. Unfortunately the term "instance" has a different connotation in SQL Server; i.e. a named instance.

As is typical with command line tools, there are many options available which allow you to fine-tune how you would like to use the tool. Open a command prompt and enter TYPEPERF -? and you will see the following output:

Usage: typeperf { <counter [counter ...]> | -cf <filename> | -q [object] | -qx [object] } [options] Parameters: <counter [counter ...]> Performance counters to monitor. Options: -? Displays context sensitive help. -f <CSV|TSV|BIN|SQL> Output file format. Default is CSV. -cf <filename> File containing performance counters to monitor, one per line. -si <[[hh:]mm:]ss> Time between samples. Default is 1 second. -o <filename> Path of output file or SQL database. Default is STDOUT. -q [object] List installed counters (no instances). To list counters for one object, include the object name, such as Processor. -qx [object] List installed counters with instances. To list counters for one object, include the object name, such as Processor. -sc <samples> Number of samples to collect. Default is to sample until CTRL+C. -config <filename> Settings file containing command options. -s <computer_name> Server to monitor if no server is specified in the counter path. -y Answer yes to all questions without prompting.

The ultimate goal of using TYPEPERF is to capture performance data in a repeatable way; e.g. specify your options in a batch file that you can execute as required. The default is to display the performance data in the command window; alternatively you can use the -f option to specify a CSV file (comma separated values), TSV file (tab separated values), etc.

To get started let's figure out what performance objects are available then setup TYPEPERF to capture some performance data. There are two options that you can use to get the list of performance objects on a particular machine:

• -q [object] lists the installed counters without the instances
  
• -qx [object] list the counters including the instances

In both cases [object] is an optional parameter which filters the list to just that object. The default is to query the performance objects on your current machine; you can include -s <computer name> to specify another machine. To get the list of counters for the SQL Server Buffer Manager object enter the following command:

TYPEPERF -q "SQLServer:Buffer Manager"

You will see output similar to the following:

\SQLServer:Buffer Manager\Buffer cache hit ratio \SQLServer:Buffer Manager\Page lookups/sec \SQLServer:Buffer Manager\Free list stalls/sec \SQLServer:Buffer Manager\Free pages \SQLServer:Buffer Manager\Total pages \SQLServer:Buffer Manager\Target pages \SQLServer:Buffer Manager\Database pages \SQLServer:Buffer Manager\Reserved pages \SQLServer:Buffer Manager\Stolen pages \SQLServer:Buffer Manager\Lazy writes/sec \SQLServer:Buffer Manager\Readahead pages/sec \SQLServer:Buffer Manager\Page reads/sec \SQLServer:Buffer Manager\Page writes/sec \SQLServer:Buffer Manager\Checkpoint pages/sec \SQLServer:Buffer Manager\AWE lookup maps/sec \SQLServer:Buffer Manager\AWE stolen maps/sec \SQLServer:Buffer Manager\AWE write maps/sec \SQLServer:Buffer Manager\AWE unmap calls/sec \SQLServer:Buffer Manager\AWE unmap pages/sec \SQLServer:Buffer Manager\Page life expectancy

To get a list of counters with instances enter the following command:

TYPEPERF -qx "SQLServer:Databases" | FIND "tempdb"

You will see output similar to the following:

\SQLServer:Databases(tempdb)\Data File(s) Size (KB) \SQLServer:Databases(tempdb)\Log File(s) Size (KB) \SQLServer:Databases(tempdb)\Log File(s) Used Size (KB) \SQLServer:Databases(tempdb)\Percent Log Used \SQLServer:Databases(tempdb)\Active Transactions \SQLServer:Databases(tempdb)\Transactions/sec \SQLServer:Databases(tempdb)\Repl. Pending Xacts \SQLServer:Databases(tempdb)\Repl. Trans. Rate \SQLServer:Databases(tempdb)\Log Cache Reads/sec \SQLServer:Databases(tempdb)\Log Cache Hit Ratio \SQLServer:Databases(tempdb)\Bulk Copy Rows/sec \SQLServer:Databases(tempdb)\Bulk Copy Throughput/sec \SQLServer:Databases(tempdb)\Backup/Restore Throughput/sec \SQLServer:Databases(tempdb)\DBCC Logical Scan Bytes/sec \SQLServer:Databases(tempdb)\Shrink Data Movement Bytes/sec \SQLServer:Databases(tempdb)\Log Flushes/sec \SQLServer:Databases(tempdb)\Log Bytes Flushed/sec \SQLServer:Databases(tempdb)\Log Flush Waits/sec \SQLServer:Databases(tempdb)\Log Flush Wait Time \SQLServer:Databases(tempdb)\Log Truncations \SQLServer:Databases(tempdb)\Log Growths \SQLServer:Databases(tempdb)\Log Shrinks

Instances in this case (-x option) report the performance counters for the SQLServer:Databases object for each SQL Server database (there is also a _Total instance which combines all databases). The above output was filtered to include just the tempdb database by piping to the FIND command. When you are working with a named instance of SQL Server, the performance objects will reflect the SQL Server instance name. For example I am running an instance of SQL Server 2000 Enterprise Edition which is named SQL2000EE; the performance objects are named MSSQL$SQL2000EE instead of SQLServer as shown above.

Use the -q or -qx options to get the list of performance counters, redirect the list to a text file, then edit the file as necessary to get just the performance counters that you want to capture. Include the -cf <filename> option on your TYPEPERF command line to get the list of counters to report on from a text file.

Now we are ready to use TYPEPERF to report some performance data. Here is a sample command:

TYPEPERF -cf MyCounters.txt

The above command will display the counters in the text file MyCounters.txt in the command window every second. Hit Ctrl-C to cancel.

Here is another example:

TYPEPERF -f CSV -o MyCounters.csv -si 15 -cf MyCounters.txt -sc 60

The above example writes the counter values to MyCounters.csv every 15 seconds. It stops after writing out the counters 60 times (i.e. 15 minutes).

An example of the output is shown below in Excel 2007:

The first row has the counter names; the columns do not show the full counter names just to conserve space. The list of counters in MyCounters.txt is:

\SQLServer:Databases(_Total)\DBCC Logical Scan Bytes/sec \SQLServer:Databases(tempdb)\Percent Log Used \SQLServer:Buffer Manager\Buffer cache hit ratio \SQLServer:General Statistics\User Connections \SQLServer:Locks(_Total)\Lock Requests/sec \SQLServer:SQL Statistics\Batch Requests/sec

In the above screen shot the custom format used for the Time column is m/d/yyyy h:mm:ss.

Next Steps

• Take a look at our earlier tip Creating SQL Server performance based reports using Excel for some helpful hints on formatting the performance data in Excel. I used these to format the data in Excel shown above.
  
• Add TYPEPERF.EXE to your tool box. It provides a simple, repeatable way to quickly start capturing performance data.

Using Performance Monitor

As you probably already know, SQL Server is very good at tuning itself. It has the ability to monitor itself, and through a feedback loop, it knows how to internally adjust and tune itself so that it keeps running efficiently, even when external events, such as the number of user connections or the amount of available RAM, change over time.

But as we all know, SQL Server's ability to self-tune is not perfect and does not take into consideration every possible aspect that affects its performance. As a DBA, we need to help SQL Server along, providing it the resources it needs for it to do a good job serving up data.

As a good DBA, we don't want to find out from our users that SQL Server is having a performance problem. Instead, we want to be proactive and catch performance problems before they arise. That is what Window's Performance Monitor can help us do. It is a tool that allows us to monitor what is going on with our SQL Server, and to provide us the information we need to make decisions on how to best tune our SQL Servers.

Performance Monitor is an important tool, because it not only provides us with information on how SQL Server is performing, but it also lets us know how Windows Server is doing, which of course directly affects SQL Server's performance. [6.5, 7.0, 2000] Updated 7-10-2006

The "Performance Monitor" under the "Microsoft SQL Server" entry under your Start Menu is the same "Performance Monitor" under the "Administrative Tools" entry under your Start Menu. They are the same programs. What is different is that when you bring up Performance Monitor from under the "Microsoft SQL Server" entry, is that it comes up already running several pre-configured SQL Server performance counters.

The Performance Monitor under the "Administrative Tools" entry does not come with any pre-configured counters loaded. Personally, I dislike the "Microsoft SQL Server" option and always choose the Performance Monitor option under "Administrative Tools." This way, I always get to choose the SQL Server Performance Monitor counters I prefer to use. [6.5, 7.0] Updated 7-10-2006

*****

If you are like me, you have one or two SQL Server production servers that are very important to monitor. To help me keep tabs on these "high-visibility" SQL Servers, I always run an instance of Performance Monitor in the background on my Windows NT 4.0 or Windows 2000 Workstation desktop pointing to these servers. I don't log this data, but I like the ability to very quickly take a look at key performance counters (in chart mode) throughout the day.

Since Performance Monitor is always running, I don't have any excuse not to take a peek at my SQL Server's performance at various times throughout the day. You would be surprised at the things you find, including bottlenecks you may not know you had. In addition, after some time, you begin to better learn how your server's perform, which makes it easier to diagnose potential problems as they arise.

In order to minimize the affect of this constant monitoring on your SQL Servers, you will not want to monitor too many counters. Here are the key counters I like to watch on a regular basis:

• Memory — Pages/Sec: To see how much paging my server is doing. This should be close to zero on a dedicated SQL Server. You will see spikes during backups and restores, but this is normal.
• Network Interface — Bytes Total/sec: To see how much network activity is going on.
• PhysicalDisk — % Disk Time — _Total: To see how busy all the disk drives are.
• PhysicalDisk — Current Disk Queue Length — _Total: Also to see how busy the drives are.
• System — % Total Processor Time: To see how busy all the CPUs are as a whole.
• System — Processor Queue Length: Also see how busy the CPUs are.
• SQLServer: General Statistics — User Connections: To see how many connections (and users) are using the server. Keep in mind that one connection does not equal one user. A single user can have more than one connection, and a single connection can have more than one user.
• SQLServer: Access Methods — Page Splits/sec: Lets me know if page splits are an issue or not. If so, then that means I need either to increase the fill factor of my indexes, or to rebuild the indexes more often.
• SQLServer: Buffer Manager — Buffer Cache Hit Ratio: To find out if I have enough memory in the server. Keep in mind that this ratio is based on the average of the buffer hit cache ratio since the SQL Server service was last restarted, and is not a reflection of the current buffer cache hit ratio.
• SQLServer: Memory Manager — Target Server Memory (KB): To see how much memory SQL Server wants. If this is the same as the SQLServer: Memory Manager — Total Server Memory (KB) counter, then I know that SQL Server has all the memory that it wants.
• SQLServer: Memory Manager — Total Server Memory (KB): To see how much memory SQL Server actual is using. If this is the same as SQLServer: Memory Manager — Target Server Memory (KB), then I know that SQL Server has all the memory that it wants. But if this is smaller, then SQL Server needs more available memory in order to run at its optimum performance.

Based on my experiences and preferences, these are the counters I like to watch regularly. If I see something interesting in these counters, I often add additional counters as necessary to get a more detailed look at what is going on. I run Performance Monitor from my desktop, not the server I am monitoring in order to minimize overhead on the SQL Server.

By default, readings will appear every second and less than two minutes at a time will appear on the graph. I don't find this time frame all that useful, so I change it to 36 seconds, which displays an hour on the screen of activity. This gives me a good feel of the health of my critical SQL Servers without putting any undue overhead on the server.

If you don't already check your SQL Server's key performance counters throughout the day, you need to start this important habit. The more you learn about how your servers run, the better DBA you will be. [6.5, 7.0, 2000] Updated 7-10-2006

*****

Once you have identified the Performance Monitor counters you like to use, you can save them in a file and then later reload them when you want to see them again. This way, you won't have to re-add the counters to Performance Monitor each time you use it. In fact, you can create different sets of counters, with different names, so you can track different types of counters at a time. In addition, each different type of Performance Monitor's modes, such as "Chart" and "Log," allows you to store its own set of counters.

How you use Performance Monitor to do this depends on if you are using Windows NT 4.0 or Windows 2000.

If you are using Windows NT 4.0, then use Performance Monitor's "File" menu option to save and load your counter files.

If you are using SQL Server 2000, then you will use the "Console" menu option save and load counter files. [6.5, 7.0, 2000] Updated 7-10-2006

*****

When monitoring your server using NT Server 4.0's "Performance Monitor," or Windows 2000's "System Monitor" tool, keep in mind that the more counters you monitor the more overhead that is required to perform the monitoring. This is true whether you are viewing a performance chart, logging counters, or creating alerts based on counters. Because of this, don't monitor counters you don't need to monitor. If you are using multiple counters for your monitoring, but soon realize that one or more of them are of little value to the task at hand, then remove these counters from your monitoring.

One difference between NT Server 4.0's Performance Monitor and Windows 2000's System Monitor is how logging is done. In Performance Monitor, you must log entire performance objects, you can't just log individual counters. This can lead to very large log files with a lot of data you don't need. In System Monitor, you are now able to log individual counters, not just entire objects. This makes these logs much smaller in size, making it more practical to log over long periods of time. [6.5, 7.0, 2000] Updated 7-10-2006

*****

When monitoring your server using NT Server 4.0's "Performance Monitor," or Windows 2000's "Performance" tool, keep in mind that how often you set these tools to collect counter data affects the amount of overhead experienced by your server. For example, the default counter collection interval for displaying near real-time charts of performance counters for these two tools is 1 second. If you increase this to .5 seconds, then overhead is essentially doubled. But if you decrease it to 5 seconds, then overhead is substantially reduced).

So how often should you collect performance counter data? It depends on what your goals are. In some cases, you need to collect data in near real-time (every second), and it other cases, collecting it every 5, 15, 30 seconds is adequate. The key is to not collect data more often than you really need to. Personally, when I collect performance counter data to display as a chart, I use 36-second intervals. Experience has proven to me that this time interval best meets my needs for monitor SQL Server's performance, and it also allows exactly one hour of chart data to be on the screen at one time. Of course, your mileage may vary. [6.5, 7.0, 2000] Updated 7-10-2006

*****

When monitoring a SQL Server using Performance Monitor, don't run Performance Monitor on the same server you are monitoring. Instead, run it on a different server or workstation and remotely monitor the SQL Server. Running Performance Monitor on the same server you are monitoring will skew the results.

Along the same train of thought, don't run both Performance Monitor and Profiler at the same time, even if you are running them remotely. This is too much overhead and will cause your SQL Server to suffer some performance degradation. In addition, running both together can cause Performance Monitor to produce less than accurate data because of the overhead of Profiler running at the same time. [6.5, 7.0, 2000]