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