SQL Server Performance Monitor

Friday May 27th 2005 by Steven Warren

When you install Microsoft SQL Server, Performance monitor objects and counters are automatically installed. While you must have administrative access to your SQL Server to use these objects, SQL Server admins should find them invaluable in monitoring and tuning the database server.

You can monitor the system performance by using the Performance monitor console and its related counters in Windows 2000. These counters allow you to view or save information about the overall performance of your server. When you install Microsoft SQL Server, additional Performance monitor objects and counters are automatically installed. While you must have administrative access to your SQL Server to use these objects, SQL Server admins should find them invaluable in monitoring and tuning the database server. Furthermore, the Performance monitor can be used either locally or remotely, which allows admins greater control in monitoring SQL Server. I am going to show you how to use the Performance monitor to keep a close watch over your SQL Server systems.

To open the Performance monitor in Win2K, go to Start | Programs | Administrative Tools | Performance. When viewing your performance data in real time, you can view it as a report, a chart, or a histogram. Figure A illustrates each of these views.

Click for larger image

Figure A.

To monitor your SQL Server successfully, you must add the counters in the Performance monitor. To add counters:

1.  Click the plus-sign button to open the dialog box.

2.  Select an object from the Performance Object list.

3.  Choose either All Counters or Select Counters From List. If you opt to select individual counters, click the Explain button for a description of each one. You can also choose Select Instances From List. For example, if you added a PhysicalDisk counter, you could then select an instance of either C: or D:, as shown in Figure B.

Figure B.

1.  After you select the counter(s), click Add. You can then repeat the process for any additional objects you would like to use.

2.  Click Close when you have added all of your counters.

If you are new to performance monitoring, you probably need some guidance on which counters to use. You can't go wrong if you monitor CPU activity, memory, paging, and/or disk I/O. These are a few of the most common counters. On most systems, you should also track the % Processor Time (under the Processor counters). On occasion, you will see spikes over 80 percent. This is normal unless the sustained % Processor Time is at 80 percent or higher for long periods. If that is the case, you could have a CPU bottleneck. To remedy the situation, you might have to get a fast processor, add more processors, and/or change disk configurations.

In addition, I recommend that you monitor the following:

  • Processor %Privileged Time: This is the amount of time the processor spent performing operating system processes.
  • System Processor Queue Length: This equates to CPU activity.
  • SQL Server Buffer Cache Hit Ratio: This is the percentage of requests that reference a page in the buffer cache. You always want to have a ratio of 90 percent or more. If you have allocated as much memory as you can to SQL Server and have not met the 90 percent ratio, add more physical memory.
  • SQL Server: General Statistics User connections: This shows the number of users connected to the system.
  • Physical Disk %Disk Time: This is the amount of time a selected disk is busy.
  • Memory Pages/Sec: This is the rate at which pages are read from or written to disk, to resolve hard page faults.

I also recommend that you experiment with choosing your own counters. Remember: You can hit the Explain button for information on any counter you select. In addition to real-time monitoring, the Performance monitor can capture data to a file. This is useful for analyzing performance data and making upgrade recommendations. However, when logging data, it is best to do this locally and not over your network. If you have to log the data over the network, you should keep the counters to a bare minimum.

To start logging information:

1.  Expand Performance Logs And Alerts.

2.  Highlight Counter Logs.

3.  Right-click on Counter Logs and select New Log Settings, as shown in Figure C.

Click for larger image

Figure C.

1.  Enter a name and click OK.

2.  In the General tab, add your counters, as shown in Figure D.

Click for larger image

Figure D.

1.  Click the Log Files tab to set the specific log file information. This could include location and/or file size limit.

2.  Click the Schedule tab to schedule your Performance monitoring. If you do not configure a time, the log file will continue to record information until you manually stop it.

3.  Click OK.

To analyze the data you have logged, you must open the log file and specify the appropriate attributes. To load your logged data:

1.  Open System Monitor.

2.  Click the View Log File Data button to open the dialog box shown in Figure E.

Figure E.

1.  Click the Add button to add your log file. This file is located in the Perflogs folder in the root directory (usually C:\Perflogs).

2.  Use the Time Range slider to set your Performance monitor to a specific time.

3.  In the Data tab, choose the counters you want to view. (You can choose only counters that you recorded in the logged operation.)

4.  In the Graph and Appearance tabs, choose your type of grid and colors. Click OK.

After loading the data, you can view a report of your findings or adjust the Time Range to fit your needs. You can even open the data in Excel and create graphs to track database growth or to produce any other report you need.

The Performance monitor gives you full control of your system by allowing you to monitor and forecast SQL Server performance. By using this tool on a regular basis, you will ensure that your database server is tuned, lubed, and ready to go.

» See All Articles by Columnist Steven S. Warren

Mobile Site | Full Site