SQL Server 2000 for experienced DBAs: Chapter 5, 'Monitoring and Tuning SQL Server' The following excerpt, courtesy of McGraw-Hill Osborne Media, is from Chapter 5 of the book "SQL Server 2000 for experienced DBAs" written by Brian Knight. Click for the complete book excerpt series or purchase the book.



System Monitor

Windows 2000 System Monitor (called Performance Monitor in Windows NT 4.0) is a tool that allows you to monitor certain performance measures called counters. System Monitor lets you achieve instantaneous polling of your server or capture performance snapshots of your server. The tool provides a complete picture of your server performance.

Monitoring your SQL Server and Windows server is not a one-time prospect. You must be ahead of the performance curve, or else you'll have your boss in your office asking why the Web page takes five minutes to load. Monitoring your SQL Server also means monitoring your Windows 2000 server closely because any adverse performance in your server could lead to your SQL Server performance suffering. Inside System Monitor, you'll find hundreds of counters for both Windows 2000 and SQL Server. Of these counters, you'll find yourself regularly using only a handful of them. The other counters are still useful, but only for specific types of monitoring, such as database growth.

To use SystemMonitor, you need administrative access to the SQL Server. sysadmin rights are not enough to use System Monitor. System Monitor is a separate tool that installs with Windows NT or 2000. Additional SQL Server counters are installed during the SQL Server installation. You can use System Monitor remotely from your workstation or locally on the SQL Server. In Windows 2000, you can access the tool under the Control Panel | Administrative Tools | Performance.


CAUTION
Sometimes, after installing the SQL Server client, you will not see the SQL Server counters in System Monitor. In that case, you can load the counters manually with the command-line tool lodctr.exe. You first need to unload the counters using the command:

unlodctr.exe MSSQLServer.

Then to load them again, using the command:

lodctr.exe BINNsqlstr.ini.

For more information, check the Microsoft Knowledge Base Article Q137899.


Adding Counters
Once you have System Monitor up, you will need to add counters. As I mentioned before, there are only a few counters that require regular monitoring. To view real-time data, select System Monitor and click the View Current Activity button in the right pane. You can then click the plus icon to add counters to your chart.


NOTE
The more counters you add, the slower your server runs. System Monitor should be run from a separate server or workstation. Its footprint on the server is small but still will have an adverse effect.



In the Trenches
There is much debate on whether to run System Monitor on the server locally or remotely. The reality is that in Windows 2000, there is very little performance hit between either. The difference does lie in the amount of network traffic (although minimal) you generate by running System Monitor remotely. If this is a concern, run System Monitor locally. I personally prefer to run it remotely from a single machine on the same domain as the servers I'm monitoring. I can then receive a consolidated view of the entire production server room in one System Monitor, so I don't have to go to each machine to pick up logs each morning. The downside is that it does make for some rather large logs.

You have the option to monitor a remote system or the local system. If you want to monitor a remote system, simply choose the option Select Counters From A Remote System, and type the computer name in the text box below the option (see Figure 5-1). System Monitor is also cluster aware, meaning that it will monitor the active node in a cluster. If you're monitoring a clustered environment, choose the SQL Server's virtual server name. This way, if the server fails over, you can continue to monitor the surviving node.

The next step in adding a counter is to select an object from the Performance Object drop-down box. You can either select all counters in the object by choosing the All Counters option or select individual counters from the list. Each counter has an Explain button that provides a detailed explanation of its task. (You can select multiple counters by pressing the CTRL key while selecting counters.)

You can also use the instances list to monitor individual subsets of the counter. For example, for the % Processor Time option, you can specify which processor you'd like to monitor. When you've selected the appropriate counters, click Add.

As you can see in Figure 5-2, the number in the Last option box shows you how busy your computer is now. By default, the Last option (and the chart) is updated every second. You can change this setting in the Properties dialog box, and the setting displays in the Duration box. Increasing the setting usually provides a more symmetric picture of performance (although increasing it too much may distort the report).

The average, minimum, and maximum settings each represent their appropriate settings for each graph cycle. By default, it takes 1 minute 20 seconds on average


Figure 5-1 Adding counters in System Monitor


Figure 5-2 System Monitor uses color codes, which you can't see here, for tracking individual counters.

dependent on the video display settings) for the graph to cycle through if it's refreshing every second. Keep in mind that if you're refreshing every second, your server is being interrogated 60 times a minute and performance suffers.

Creating a Server Baseline

Friday at 4:55 in the afternoon, you receive a call from the application group complaining about slow performance. You open System Monitor and notice that the CPU is at 60 percent utilization and your memory is only 50 percent utilized. Is this normal? Without a good performance baseline, you may never know. Baselines tell you what your server should look like under a normal day's conditions. I usually have two baselines: one for peak system usage and one during normal system usage. A baseline usually contains a compilation of the following:

  • System Monitor logs with the counters mentioned in the next few sections (I compile mine into a SQL Server table for safekeeping and easy centralized retrieval)
  • Output of the SQLDiag utility
  • Optionally, you can use SQL Profiler to obtain the ten worst performing queries

    A good System Monitor baseline should have a small interval of time between snapshots and stretch over a period of time that is sufficient to get a good benchmark of your system. I generally keep the interval at 30 seconds for 6 hours. Once you know what the baseline is, you can compare the scenario I mentioned earlier in this section to it to see if the performance of the server is normal. If you see in the baseline that the processor normally averages 60 percent, you can rule out CPU as the culprit.


    NOTE
    Make sure you update and replace your baseline at least once a quarter. As usage increases and more applications are placed on your SQL Server, your baseline will begin to shift.

    Click for the next excerpt in this series: Performance Counters


    Click for the complete book excerpt series.

    This was first published in July 2005

  • There are Comments. Add yours.

     
    TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

    REGISTER or login:

    Forgot Password?
    By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
    Sort by: OldestNewest

    Forgot Password?

    No problem! Submit your e-mail address below. We'll send you an email containing your password.

    Your password has been sent to: