System monitor logging

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 lets you either monitor performance in real time or save the information to a log for later viewing. Using the log method, you can take a system snapshot of key performance indicators at a specific time interval and then read the results from the log. This gives you a more realistic picture of your server's performance, because the data is collected over time (avoiding the risk that your performance report was skewed by a single query). To create a log, go to the Counter Logs section of System Monitor. Right-click on Counter Logs and select New Log Settings. In the General tab, specify all the counters you want to monitor and set the monitoring interval you want to use. By default, a snapshot of the counters you specify is performed every 15 seconds, and on a busy system, this may be too often.

Tip
Set the interval so you can capture system spikes without hindering the performance of your system. I like to capture once every five minutes for an entire day, one day each month. This provides a good profile of this system (assuming the system is equally busy every day, which may be a poor assumption).

In the Log Files tab (see Figure 5-3), you can specify the location of your log files. Each time the log is stopped and started, a new log file is created. Under the Log File Type drop-down box, select Text File – CSV. That way, you can easily import the file into SQL Server or Excel. You may also want to set a limit on the file size. In the Schedule tab, specify when the job starts and stops. You can also configure what happens when the job stops. For example, in Figure 5-4, you can see that the log stops when it reaches 1000KB, and the system creates a new log file. At that point, the system automatically executes c:UtilsNetSendMessage.Bat, which sends a popup message. The contents of the batch file are similar to the following:

net send computername message

Figure 5-3 Setting up a log file

Figure 5-4 Scheduling logs

The net send command could also be directed to the entire domain. You can use this method to state that the log has rolled over and can now be archived. After you create the log, ensure it is started (green). If it is not, right-click the log name listing and select Start.

Viewing Performance Logs

To view the logs, open System Monitor and click the Select Log File Data icon in the right pane. Specify the counters you'd like to view (only the counters you specified in the log settings are available).

After you've selected the counters, you can see a chart of the server's activity. You can also view the average activity by clicking the View Report icon (see Figure 5-5). This report shows you the average server activity throughout the captured time. Since you saved your log file as a CSV, you can also view the line-item data in Excel and perform calculations on the data. Excel is also handy for performing " what if " scenarios.

Figure 5-5 Click View Report to see average activity report

Click for the next excerpt in this series: System Monitor Alerts