By: Ray Barley | Comments (19) | Related: > Performance Tuning
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.
About the author
Ray Barley is a Principal Architect at IT Resource Partners and a MSSQLTips.com BI Expert.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
