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 System Monitor, 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 <SQL Server Path>\BINN\sqlstr.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.

Figure 5-1: Adding counters in System Monitor

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

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

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

Performance Counters to Watch

Most of the indicators that provide the information needed are operating system counters, especially those that deal with CPU activity, memory, paging, and the network interface. In SQL Server, you should monitor connections, transactions, and locks.

Hardware Counters

I would recommend watching the following counters regularly:

• Memory \ Available Bytes Shows the available amount of physical memory on the server. An acceptable output for this may vary widely based on how much physical memory is in the machine. If you have 2GB of RAM installed on the machine, it is common to see SQL Server use 1.7GB of RAM. If no other processes are running on your SQL Server, make sure you have at least 80MB available for Windows at any given time. If you see this counter below that amount, I would recommend buying additional RAM immediately.

• Memory \ Pages/sec Shows the number of pages that are read from or written to disk. This causes hard page faults, which cause SQL Server to go to page file versus memory. If this counter averages 20, you may want to add additional RAM to stop the paging.

• Network Interface \ Bytes total/sec This counter shows the amount of traffic through your network interface in bytes per second. Once you do your baseline (I'll discuss this in a moment), you'll know you have a problem when this number drops or rises a huge amount.

• Paging File \ % Usage Similar to the Memory \ Pages/sec counter, this shows the percentage of the page file that is being utilized. If you see more than 70 percent of your page file being utilized, look into more RAM for your server.

• Physical Disk \ % Disk Time This counter shows how active your disk is in percentage form. If this counter sustains an average above 70 percent, you may have contention with your drive or RAM.

• Processor \ % Processor Time This is one of the most important counters. It shows how active the processor is in percentage form. While the threshold to be concerned with is 85 percent on average, it may be too late if you wait that long. I generally look at either improving the performance of the queries or adding additional processors when the counter averages above 60 percent.

SQL Server Counters

Here are the core counters that I watch for SQL Server:

• SQLServer:Access Methods \ Full Scans/sec This shows the DBA how many full table or index scans are occurring per second. If this number is significantly higher than your baseline, the performance of your application may be slow.

• SQLServer:Buffer Manager \ Buffer Cache Hit Ratio This shows the ratio of how many pages are going to memory versus disk. I like to see this number as close to 100 percent as possible, but generally 90 percent is very respectable. If you see this number as low, it may mean that SQL Server is not obtaining enough memory from the operating system.

• SQLServer:Database Application Database \ Transactions/sec Shows the amount of transactions on a given database or on the entire SQL Server per second. This number is more for your baseline and to help you troubleshoot issues. For example, if you normally show 120 transactions per second as your baseline and you come to work one Monday and see your server at 5,000 transactions per second, you will want to question the activity on your server.

• SQLServer:General Statistics \ User Connections Like the transactions per second, this counter is merely used for creating a baseline on a server and in the troubleshooting process. This counter shows the amount of user connections on your SQL Server. If you see this number jump by 500 percent from your baseline, you may be seeing a slowdown in your activity due to a good response from your marketing campaign.

• SQLServer:Latches \ Average Latch Wait Time (ms) Shows the average time for a latch to wait before the request is met. If you see this number jump high above your baseline, you may have contention for your server's resources.

• SQLServer:Locks \ Lock Waits/sec Shows the number of locks per second that could not be satisfied immediately and had to wait for resources.

• SQLServer:Locks \ Lock Timeouts/sec This counter shows the number of locks per second that timed out. If you see anything above 0 for this counter, your users will experience problems as their queries are not completing.

• SQLServer:Locks \ Number of Deadlocks/sec This counter shows the number of deadlocks on the SQL Server per second. Again, if you see anything above 0, your users and applications will experience problems. Their queries will abort and the applications may fail.

• SQLServer:Memory Manager \ Total Server Memory Shows the amount of memory that SQL Server has allocated to it. If this memory is equal to the amount of total physical memory on the machine, you could be experiencing contention since you're not leaving Windows any RAM to perform its normal operations.

• SQLServer:SQL Statistics \ SQL Re-Compilations/sec This counter shows the amount of SQL recompiles per second. If this number is high, stored procedure execution plans may not be caching appropriately. Like other counters, this needs to be placed into a baseline and watched to make sure it's not moving radically from that baseline.

• SQLServer:User Settable \ Query This counter is a slightly tricky one to implement and is optional. There are up to ten customizable counters that you can implement using the sp_user_counterX stored procedure (where X is a number between 1 and 10).

Note 

Data for the SQL Server counters is stored in the sysperfinfo system table in the master database. Only the first 99 databases are stored in the table.

Setting the SQLServer:User Settable \ Query counter can be useful for tracking very customized tasks. For example, if you have a table that holds items in a queue to be worked on (such as for a call center), you can set this counter when the count in the queue table becomes extremely high. This can trigger given alerts to either e-mail the administrator or self-correct itself. To set this counter, use the sp_user_counter1 stored procedure. You can set this in a SQL Server agent job or by manually executing a query as shown here:

DECLARE @TOTAL_ROWS int SET @TOTAL_ROWS = (SELECT COUNT(*) FROM CALL_QUEUE) EXEC sp_user_counter1 @TOTAL_ROWS

This query will set the User Settable 1 counter. You can set up to ten of these by incrementing the sp_user_counterX stored procedure. For example, to set the second counter, simply use the sp_user_counter2 stored procedure.

Caution 

Be careful how often you update this counter. If you update it too often, you risk slowing down your server. If you want to update it regularly, make sure the query you're using is simple and that it runs fast under high utilized times. It is for that reason that this counter makes a nice supplement to any monitoring regiment but not the primary staple.

Other counters are important for specific types of troubleshooting or monitoring issues. Every DBA has a list of counter preferences. Experiment with System Monitor to come up with your own; the time you invest will pay a huge return.

System Monitor Logging

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.

Figure 5-3: Setting up a log file

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:\Utils\NetSendMessage.Bat, which sends a popup message. The contents of the batch file are similar to the following:

Figure 5-4: Scheduling logs

net send computername message

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 reports