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?
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: |
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 |
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) |
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 |
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.
No comments:
Post a Comment