For the most reliable results when using Performance Monitor to track SQL Server and other related server counters, use Performance Monitor’s Log Mode. This allows you to collect counter data over a period of time. If you are monitoring for periods of less than eight hours, begin by collecting information every 15 to 60 seconds. What SQL Server will do is to average the readings of the time period you select. So the longer the time interval you choose, the more “average” the data becomes. This is not normally a problem when collecting log data over time as your goal here is to identify trends. Of course, if you are trying to analyze a very specific issue that occurs in a relatively short period of time (say, less than one hour), then you might want to set the time period of 1 second, which will allow you to see and analyze activity in detail.
If you want to collect data for longer periods (such as a week or month), you may want to try to collect information somewhere between 300 and 600 seconds. If you sample the data too frequently, especially for long periods, the amount of data collected will be huge. On the other hand, if you collect information too infrequently, then you may miss important detail. You may have to experiment with various collection intervals until you come up with one that is best for your circumstances.
*****
When using System Monitor (Performance Monitor) in Windows Server to create performance logs, you have a variety of different file formats in which to store the log data. Instead of taking the default value, you should carefully consider what each of the different formats offer you. Here is a summary of each of the log formats:
- Text File–CSV: This stores counter data in an a comma-delimited ASCII format. This option gives you the most options when it comes to exporting data to other applications for analysis. In most cases, this is the option I choose. Uses a CSV extension.
- Text File–TSV: This stores counter data in a tab-delimited ASCII format, which is best used if you want to import that data into a spreadsheet program. Uses a TSV extension.
- Binary File (the default): This stores counter data in a sequential, binary-format that cannot be exported into any other format. This option should only be used if you want to be able stop and start the log and include all of the data collected into a single physical file, and if you don’t ever want to export the data. Uses a BLG extension.
- Binary Circular File: This stored counter data in a binary-format that records data continuously to the same physical log file, overwriting previous data. This data cannot be exported. Uses a BLG extension.
In most cases, choose the Text–CSV or the Text–TSV options for the greatest flexibility as the data can be moved into a SQL Server table or into an Excel spreadsheet for analysis.
If you stored the data in one format, but now want it in another format, you can change the files format by going to the Properties screen of the counter log, go to the Log Files tab, and then Log File Type to the one of your choosing.
*****
System Monitor (Performance Monitor) in Windows 2000 allows you to export graphs you create to HTML, which allows you to more easily share results with others. Here’s how:
- First, create the graph in System Monitor using System Monitors graphing options to “dress” it up anyway you like.
- Next, once the graph is done, right-click on the graph and select “Save As”.
- Next, when the dialogue box appears, select a path and name for the html file, and save it.
- Once the HTML graph is saved, it can be viewed by any web browser.
*****
Performance Monitor allows you to graph counter activity of more than a single SQL Server at the same time. This can come in handy is certain cases. For example, let’s say that you have two identical servers, both running SQL Server, but it appears that one of the servers is much slower than the other. Believe it or not, this is a common issue I have seen brought up on this website’s forum. One way to help determine which two identical servers may be performing differently is to profile both of them at the same time in the same graph window of Performance Monitor. Of course, the performance problem may not be hardware related, but the information provided should be helpful in narrowing down where the problem lies.
*****
If you want to access SQL Server Performance Monitor data, but you don’t want to use Performance Monitor, you can by querying the sysperfinfo table in the master database.
All of the SQL Server performance counters can be gathered from this table. This might come in handy if you want to create a SP to gather the data you want to monitor at regular periods and to store the data in a SQL Server database. Keep in mind that the structure of this table may vary between SQL Server versions.
*****
Under SQL Server 2000, System Monitor (Performance Monitor) runs under the Microsoft Management Console (MMC). Because of this, you can customize the MMC a lot of different ways. For example, you can have multiple instances of System Monitor in the same MMC, which means you can watch monitor different SQL Servers from the same screen. You can also add System Monitor to any MMC, including the one you use to administer your servers.
If you want to create your own, fresh MMC, you can by typing “mmc.exe” and running it. This will bring up a blank MMC screen that you can customize to meet your needs.
*****
Don’t run Performance Monitor from a Terminal Services or Citrix client. In effect, this is the same as running Performance Monitor on the same server that you are monitoring, which can create unnecessary overhead on the server being monitored. And what is worse, not only is the server experiencing the overhead of Performance Monitor, it also is experience the additional overhead of the Terminal Services or Citrix client. Instead, run Performance Monitor from a Windows NT or Windows 2000 Workstation, and connect to the server remotely.
*****
Don’t publish Performance Monitor (System Monitor) as a published application in Citrix. If you do, the results you get back from the published version of Performance Monitor may not be accurate. Instead, run Performance Monitor on a workstation, and connect to the server you are monitoring remotely.
*****
To collect counter information from the I/O subsystem under NT Server 4.0, you must turn on the disk counters entering this command at the NT Server command prompt, “diskperf -y”, and then reboot the server. Turning these counters on can use up to 2 to 3 percent of the CPU utilization of the server. If your server is not CPU bound, then you should not be worried by this as it will not negatively affect SQL Server’s performance.
If you are CPU bound, but you do not want to turn the counters off, Microsoft has a suggested registry change you can make that will alleviate most of the CPU hit. To find out more about this registry change, search www.microsoft.com for this article, “MS Windows NT Server 4.0 Enterprise File Server Scalability and Performance”. This article covers how to make the necessary registry change by adding the “LargeIrpStackLocations” registry key.
*****
Don’t use the “diskperf -ye” command for hardware-based RAID when running NT Server 4.0. The “-ye” is designed for NT Server’s software-based RAID.
*****
In Windows 2000, disk counters work differently than they do under Windows NT Server 4.0. Unlike NT Server 4.0, the physical disk I/O counters are turned on by default, but not the counters for logical I/O. In most cases, the physical I/O counters are the only ones you really need to monitor for performance. The following diskperf options are available under Windows 2000:
- diskperf: When run by itself, it lists which disk counters are turned off or on.
- diskperf -y: Turns both physical and logical disk counters on.
- diskperf -yd: Turns the physical disk counters on.
- diskperf -yv: Turns the logical disk counters on.
- diskperf -n: Turns off all disk counters.
- diskperf -nd: Turn off the physical disk counters.
- diskperf -nv: Turns off the logical disk counters.
- diskperf \computername: Specifies on which computer, local or remote, on which diskperf runs. If not specified, then the local computer is affected.
Once any change is made using diskperf, it won’t take affect until the computer is rebooted.
*****
If your server is performance bound and you have done everything you can think of to boost performance, and you still are having performance problems, you can gain a “little” more performance by starting SQL Server by using the -x startup parameter. What this option does is to turn off CPU time and cache-hit ratio statistics, reducing overhead just a little. This option, of course, will not let you use Performance Monitor to its full potential.
*****
In virtually all cases, use the Physical Disk counters, not the Logical Disk counters, when monitoring I/O activity. The Logical Disk counters will not always provide accurate data, especially when you are using RAID arrays.
*****
Periodically, collect performance data on your SQL Servers, and then save this data in a spreadsheet or database so that you can determine trends in your server’s performance.
For example, you may consider collecting data on your servers on a daily basis, collecting data every 600 or 900 seconds, then each day dumping this data into a database for trend analysis. While this can be a lot of work, it will provide solid data you can use to plan future hardware expansions.
*****
As mentioned elsewhere on this website, performance monitor baselines are valuable tools. One little used benefit of performing baselines on your SQL Servers is to identify periods of low activity that you, as the DBA, can take advantage of in order to perform routine system task, such as backups, index rebuilds, etc. In some cases, such periods of low activity may be obvious, but in other cases, the low activity periods may not be so obvious. This is where a Performance Monitor baseline comes in handy. By checking activity over a time period, such as a day, or a week, you can easily identify those time periods when activity is at a minimum.
*****
Take advantage of SQL Server’s ability to create SQL Server Performance Condition Alerts. You can create alerts that are fired when performance monitor conditions, that you set, are reached. For example, if you want to know if the number of SQL Server user connections exceeds 100, you can create the alert, and when it is fired, SQL Server can e-mail you with the alert message. Create these alerts using SQL Server Enterprise Manger.
In many ways, these alerts are similar to the alerts that you can create with the Performance Monitor. But you will find that the ones you create using Enterprise Manager are easier to set up and are more robust.