Platform Monitoring Tools

As mentioned earlier, Windows ships with two primary monitoring tools that can be used to monitor a SQL Server platform-the Performance Console and Task Manager. Task Manager provides an instant view of systems activity such as memory usage, processor activity and process activity, and resource consumption. It is also very helpful when you need immediate detection of a system problems. On the other hand, Performance Console is used to provide performance analysis and information that can be used for troubleshooting and bottleneck analysis. Performance Console is the tool you would use to establish regular monitoring regimens and continuous server health analysis.

Performance Console comes equipped with two important tools: System Monitor and Performance Logs and Alerts. We will talk about them some more a little later in this chapter. The first tool to whip out, because of its immediacy and as a troubleshooting-cum-information utility, is Task Manager.

Task Manager

The Task Manager is useful for providing quick access to information on applications and services that are currently running on a server. This tool provides information such as processor usage in percentage terms, memory usage, task priority, response, and some statistics about memory and processor performance.

The Task Manager is thus very useful for quick system status check. It is usually started up in response to slow response times, lockups or errors, or messages pointing to lack of systems resources and so forth.

Task Manager, illustrated in Figure 18–1, is started in any of several ways:

• Right-click in the taskbar (right-bottom area where the time is usually displayed) and select Task Manager from the context menu.

• Select CTRL+SHIFT and press the esc key.

• Select CTRL+ALT and press the DELETE key. The Windows Security dialog box loads. Click Task Manager.

Figure 18–1: Task Manager

When the Task Manager loads, you will notice that the dialog box it comes in has three tabs: Applications, Processes, and Performance. There are a number of ways you can display the Task Manager:

• When the Task Manager is running, a CPU gauge icon displaying CPU information is placed into the taskbar on the right-bottom of the screen. If you drag your mouse cursor over this area, you will obtain a pop-up of current accurate CPU usage.

• You can also keep the Task Manager button off the taskbar if you use it a lot. This is done by selecting the Options menu and then checking the Hide When Minimized option. The CPU icon next to the system time remains in place.

• Keep the Manager visible all the time by selecting Always on Top from the Options menu.

• Press CTRL+TAB to switch between the tabs.

• Sort the columns in ascending or descending order by clicking the column heads.

• Columns can also be resized.

You can control the rate of refresh or update from the View | Update Speed menu. You can also pause the update to preserve resources and click Refresh Now to update the display at any time.

Monitoring Processes

The Processes page is the most useful on the Task Manager; it provides a list of running processes on the system and it measures their performance in simple data terms. These include CPU percent used, the CPU Time allocated to a resource, and memory usage.

A number of additional performance or process measures can be added to or removed from the list on the Processes page. Go to the View menu and click the Select Columns option. This will show the Select Columns dialog box, which will allow you to add or subtract Process measures to the Processes list. By the way, a description of each Process Counter is available in Windows Online Help.

You can also terminate a process by selecting the process in the list and then clicking the End Process button. Some processes, however, are protected, but you can terminate them using the kill or remote kill utilities that are included in the operating system (see the Windows Server 2003 operations guides for more information on kill and rkill).

The Performance page allows you to graph the percentage of processor time in kernel mode. To show this, select the view menu and check the Show Kernel Times option. The Kernel Times is the measure of time that applications and services are using operating system services.

If your server sports multiple processors, you can select CPU History on the View menu and graph each processor in a single graph pane or in separate graph panes. The Application page also lists all running applications. It lets you to terminate an application that has become unresponsive. Task Manager thus makes it easy to determine what is in trouble or the cause of trouble on your server.

The Performance Console

The Performance Console comes equipped with the System Monitor, which I will discuss first, and Performance Logs and Alerts. Performance Monitor-often referred to as “perfmon”-is usually found on the Administrative Tools menu as Performance. You can also load it like all MMC snap-ins from the Run console, Task Manager, or the command line as perfmon.msc. When perfmon starts, it loads a blank System Monitor graph into the console tree, as shown in Figure 18–2.

Figure 18–2: System Monitor

System Monitor

The System Monitor allows you to analyze system data and research performance and bottlenecks. The utility, which is also hosted in the MMC, allows you to create graphs, histograms (bar charts), and textual reports of performance counter data. The system monitor is ideal for short-term viewing of data and for diagnostics. It includes the following features:

• System Monitor is hosted in MMC, so it is portable. The snap-in can be aimed at any server and remotely monitor the processing on that computer.

• It also provides a toolbar that can be used to copy and paste counters, purge or clear counters, add counters, and so forth.

• It allows you a wide berth on how counter values are displayed. For example, you can change the line style and width to suit your viewing needs. You can change the color of the lines for clarity or to make it easier to read. You can also change the color of the chart and then manipulate the chart window as you deem fit.

• You can use the legends to indicate selected counters and associated data such as the name of the computer, the objects, and object instances.

By the way, the System Monitor is an ActiveX control named sysmon.ocx. This means that you can load the OCX into any OLE-compliant application, such as Microsoft Word or Visio, or even an HTML page on a Web site. The OCX is also useful in applications that can be specifically created for performance monitoring and analysis. You can load several into a browser and point them at your “stable” of SQL Server machines. This would enable you to at a glance detect a server that is in trouble.

How to Use System Monitor

First, you can configure the monitor using the toolbar or the shortcut menu. The shortcut menu is loaded by right-clicking in the blank-graph area and selecting the appropriate option. The toolbar is available by default.

With the toolbar, you can configure the type of display you want to view by clicking the View Chart, View Histogram, or View Report button. In other words, the same information can be viewed in either chart, histogram, or report format.

There are differences in the view formats which should be noted. The histograms and charts can be used to view multiple counters. However, each counter only displays a single value. You would use these to track current activity, viewing the graphs as they change. The report is better suited to multiple values.

To obtain a real-time data source, click the View Current Activity button. You can also select the View Log Data button. This option lets you to obtain data from a completed set of running logs.

You first have to select the counters. The counters buttons in the middle of the toolbar includes Add, Delete, and New Counter Set. The last button mentioned resets the display and allows you to select new counters. When you click the Add Counters button, the dialog box illustrated in Figure 18–3 loads.

Figure 18–3: Add Counters

The Add Counters Dialog Box

This dialog box lets you select the server to monitor, and lets you select performance objects and counters. You should also take notice of the Explain button. This is a useful feature that lets you learn more about the individual counters you select with a single click.

You can also update the display with the Clear Display option. And you can freeze the display with the Freeze Display button, which suspends data collection. Click the Update Data button to resume collection.

When you click the Highlight button, you can select chart or histogram data. This serves the purpose of highlighting the line or bar for a selected counter that is positioned against a white or black background. Noticed that the display can also be exported. It is possible to save it to the Clipboard. Conversely, you can also import the display into another console.

The Properties button gives you access to settings that control fonts, colors, and so forth. Clicking it loads the System Monitor Properties dialog box, as shown in Figure 18–4.

Figure 18–4: System Monitor Properties

There are also several ways you can save data from the monitor. You have the option of the Clipboard, and you can add the control, as discussed earlier, to a host application. You can also easily preserve the look and feel of the display by saving it as an HTML file. If you right-click in the pane you are presented the option of saving the display as an HTML file.

You can also import the log file in comma-separated (CSV) or tab-separated (TSV) format and then import the data into a spreadsheet, a SQL Server table, or a report program such as Crystal Reports. Naturally once the data is in a SQL Server database, you can report against it or use it for analysis.

The Add Counters dialog box lets you select all counters and instances to monitor, or specific counters and instances from the list. Understand, however, that the more you monitor, the more system resources you will use. If you use a large number of monitors and counters, consider redirecting the data to log files and then reading the log file data in the display. It makes more sense to work with fewer counters and instances.

Tip 

It is possible to run two instances of System Monitor (in two Performance consoles). This may make it easier to compare data from different sources.

If you have a look at the Instances list box, the first value, _Total, allows you sum all the instance values and report them in the display. And the lines in the display can be matched with their respective counters. You just have to select them.

The SQL Server Objects

SQL Server comes equipped with objects and counters that can be used by System Monitor to monitor activity in computers running an instance of SQL Server. The object is any Windows or SQL Server resource, such as a SQL Server lock or Windows process. Each object contains one or more counters that determine various aspects of the objects to monitor. For example, the SQL Server Locks object contains counters called Number of Deadlocks/sec or Lock Timeouts/sec.

Some objects have several instances if multiple resources of a given type exist on the computer. For example, the Processor object type will have multiple instances if a system has multiple processors. And the Databases object type has one instance for each database in operation on a single instance of SQL Server. Some object types, such as the Memory Manager object, have only one instance. If an object type has multiple instances, you can add counters to track statistics for each instance. You may also make a case for monitoring all instances at once.

The performance condition alerts are only available for the first 99 databases. Thus any databases you create after the first 99 will not be included in the sysperfinfo system table in SQL Server. If you use the sp_add_alert stored procedure, you will get an error.

When you add counters to the chart, or remove them, and then save the chart settings, you can specify which of the SQL Server objects and counters are monitored when System Monitor is started up.

Table 18–2 provides a list of SQL Server objects and their respective counters.

Table 18–2: Server Objects and Counters

SQL Server Object	Counter
Buffer Manager	Buffer Cache Hit Ratio
General Statistics	User Connections
Memory Manager	Total Server Memory (KB)
SQL Statistics	SQL Compilations/sec
Buffer Manager	Page Reads/sec
Buffer Manager	Page Writes/sec

You can configure System Monitor to display statistics from any SQL Server counter. You can also set a threshold value for any SQL Server counter and then generate an alert when a counter exceeds a threshold. Tie that into the Agent service and you have a system that can make your life as a DBA a lot easier to live.

The SQL Server statistics are displayed only when an instance of SQL Server is running. So if you stop the instance of SQL Server, the display of statistics stops. When you start the instance again, then the statistics reporting resumes. The SQL Server objects are listed in Table 18–3.

Table 18–3: Objects and What They Are Used For

SQL Server Object	Description
Access Methods	This object searches through and measures allocation of SQL Server database objects. It can also handle the number of index searches or number of pages that are allocated to indexes and data.
Backup Device	This object provides information about backup devices used by backup and restore facility. With it you can monitor the throughput of the backup device, which is key to maintaining a high-end OLTP system.
Buffer Manager	This object provides information about the memory buffers used by an instance of SQL Server, such as free memory and the buffer cache hit ratio.
Cache Manager	This object provides information about the SQL Server cache which is used to store objects such as stored procedures, triggers, and query plans.
Databases	This object provides information about a SQL Server database, such as the amount of free log space available or the number of active transactions in the database. There can be multiple instances of this object, but only up to 99.
General Statistics	This object provides information about general server-wide activity, such as the number of users who are connected to an instance of SQL Server.
Latches	This object provides information about the latches on internal resources, such as database pages, that are used by SQL Server.
Locks	This object provides information about the individual lock requests made by SQL Server, such as lock time-outs and deadlocks. There can also be multiple instances of this object.
Memory Manager	This object provides information about SQL Server memory usage, such as the total number of lock structures currently allocated.
Replication Agents	Provides information about the SQL Server replication agents currently running.
Replication Dist	This object measures the number of commands and transactions read from the distribution database and delivered to the Subscriber databases by the Distribution Agent.
Replication Logreader	This object measures the number of commands and transactions read from the published databases and delivered to the distribution database by the Log Reader Agent.
Replication Merge	This object provides information about SQL Server merge replication, such as errors generated or the number of replicated rows that are merged from the Subscriber to the Publisher (see Chapter 10).
Replication Snapshot	This object provides information about SQL Server snapshot replication, such as the number of rows that are bulk-copied from the publishing database.
SQL Statistics	This object provides information about aspects of SQL queries, such as the number of batches of T-SQL statements received by SQL Server.
User settable object	This object performs custom monitoring. Each counter can be a custom stored procedure or any T-SQL statement that returns a value to be monitored. This is a very valuable resource that can be used to monitor the resources consumed by a complex query.

Monitoring with Transact-SQL Statements

SQL Server provides several T-SQL statements and system stored procedures that allow you to perform ad hoc monitoring of an instance of SQL Server. You can use these resources when you need to gather, at a glance, information about server performance and activity. You can obtain information, in the form of result sets, of the following:

• Current locks

• Current user activity

• Last command batch submitted by a user

• Data space used by a table or database

• Space used by a transaction log

• Oldest active transaction (including replicated transactions) in the database

• Performance information relating to I/O, memory, and network throughput

• Procedure cache usage

• General statistics about SQL Server activity and usage, such as the amount of time the CPU has been performing SQL Server operations or the amount of time SQL Server has spent performing I/O operations.

Most of this information, however, can also be monitored in SQL Server Management Studio, using the SQL-SMO, or System Monitor as earlier discussed.

Performance Logs and Alerts

Performance Monitor includes two types of performance-related logs: counter logs and trace logs. These logs are useful when you want to perform advanced performance analysis and include record keeping that can be done over a period of time. There is also an alerting mechanism built in. The Performance Logs and Alerts tree is shown in Figure 18–5. You can find the tool in the Performance console snap-in, and it is thus started as described earlier.

Figure 18–5: Performance Console Logs and Alerts

The counter logs record sampled data about hardware resources and system services based on the performance objects. They also work with counters in the same manner as does the System Monitor. The Performance Logs and Alert Service obtains the data from the operating system as soon as the update interval has elapsed.

The trace logs collect the event traces. Trace logs let you measure performance associated with events related to memory, storage file I/O, and so on. As soon as the event occurs, the data is transmitted to the logs. The data is measured continuously from the start of an event to the end of an event. This is different from the sampling that is performed by the system monitor.

The Performance Logs data is obtained by the default Windows kernel trace provider. You can analyze the data using a data-parsing tool, or store it in a SQL Server database for later analysis.

With the alerting function you can define a counter value that will trigger an alert that can send a network message, execute a program, or start a log. This is useful for maintaining a close watch on systems, and when it is coupled with the SQL Server Agent, you can conjure up a sophisticated reporting and alerting storm. You can, for example, monitor unusual activity that does not occur consistently and define an alert to let you know when the event has been triggered. Security-related events are good candidates for the alert service. When you are trying to catch a hacker, there is no better time than when he or she is in the act, trying to break into your accounts database.

You can also configure the alert service to notify you when a particular resource drops below or exceeds certain values or thresholds or baselines that you have established. Counter logs, for example, can also be viewed in System Monitor, and the counter log data can be saved to CSV format and viewed in a spreadsheet, through report software, or in a special SQL Server database that can hold the data for later analysis. You can configure the logs as circular, which means that when the log file reaches a predetermined size, it will be overwritten. The logs can be linear, and you can collect data for predefined lengths of time. The logs can also be stopped and restarted according to parameters you specify.

As with the System Monitor, you can save files to various formats, such as HTML, or import the entire control OCX into an OLE container.

Using Logs and Alerts with SQL Server 2005

You can get started using Logs and Alerts by right-clicking the Details pane and select the New Log Settings option. You will first be asked to name the log or alert before you can define any of its properties.

Before you can start using Logs and Alerts, you need to check that you have Full Control access to the following subkey: HKEY_CURRENT_MACHINE\SYSTEM\ CurrentControlSet\Services\SysmonLog\Log_Queries. This key provides access to administrators by default, but access can also be bestowed on a user in the usual fashion through group membership. To do this, open the registry editor (Regedit) and access the Security menu in. In addition in order to run or configure the service, you will need the right to start or configure services on the server. Administrators also have this right by default. It can also be given through security group membership and group policy.

When you choose the Properties option, the Counter Log Properties or Trace Log Properties dialog box loads. The Log and Alert properties are now configured as discussed here. To configure alerts, you first need to configure counters for the alerts, the sample interval, and the alert threshold. The next step requires you to configure an action to take when the event occurs. These can include running a program, sending a message, triggering a counter log, or writing to the event log. You can also configured Alert startup by providing the Start and Stop parameters.

In order to configure counter logs, set the counter log counters and provide a sample interval. To write to log files, provide a file type, size, and path, as well as any automatic naming parameters that might be needed. The counter logs should be defined as either CSV files or TSV files, text files, binary linear, or binary circular files.

The counter logs can also be scheduled to start automatically. However, you cannot configure the service to automatically restart if a log file is configured to stop manually. This also applies to Trace Logs.