Monitoring SQL Server Performance

In Windows, you can monitor your system performance by using the Performance monitor console and its related counters. These counters allow you to view or save information about the overall performance of your server(s). The Performance monitor has access to hundreds of counters that are automatically loaded when you install Win2K, and these are effective in helping you capture performance snapshots of basic server data in real time or to disk.

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'm going to show you how to use the Performance monitor to keep a close watch over your SQL Server systems.Viewing real-time performance data.

To open the Performance monitor in Windows, 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 describes each of these views.

Adding counters

To successfully monitor your SQL Server, 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
  4. After you select the counter(s), click Add. You can then repeat the process for any additional objects you would like to use.
  5. Click Close when you have added all of your counters.

Recommendation In most companies, a DBA has at least one SQL Server machine that should be constantly monitored. I recommend that you configure real-time monitoring of that SQL Server box from a remote machine. That way, whenever you want to quickly monitor the box, all you have to do is load your preconfigured counters. You can also choose to run the Performance monitor in the background.

What to monitor

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's 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.

Logging performance data

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.
  4. Enter a name and click OK.
  5. In the General tab, add your counters, as shown in Figure D.
  6. Click the Log Files tab to set the specific log file information. This could include location and/or file size limit.
  7. 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.
  8. Click OK.

Analyzing logged data

To analyze the data you've 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.
  3. Click the Add button to add your log file. This file is located in the Perflogs folder in the root directory (usually C:Perflogs).
  4. Use the Time Range slider to set your Performance monitor to a specific time.
  5. In the Data tab, choose the counters you want to view. (You can choose only counters that you recorded in the logged operation.)
  6. 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.

In addition to the Performance monitor, I recommend that you take a look at the Spotlight on SQL Server utility, shown in Figure F. With this software, you can display all of your real-time activity in one place and drill down further for more information, as you can see in Figure G. Furthermore, the software enables you to create alerts and provides you with a wealth of knowledge to properly maintain your SQL Server databases. You can download a trial copy to experiment with it.