Using Performance Monitor to Monitor SQL Server

In the March issue of Windows NT Magazine, I explored the options available for integrating SQL Server with Windows NT's Performance Monitor, a tool that can provide a comprehensive overview of your SQL Server system's behavior (see "7 Tips for Highly Effective Monitoring"). I examined five predefined counters that track information when you start Performance Monitor from the SQL Server folder or group, and I looked at some common misunderstandings about using them.

You can also use Performance Monitor to track the size of your SQL Server database transaction logs and set up alerts to notify the server before the transaction log fills, thus averting problems that might otherwise seriously affect users and applications. But before you can monitor logs and set up alerts, you need to understand how transaction logs work.

SQL Server Transaction Logs
Every SQL Server database has a transaction log, in which SQL Server records every database change. SQL Server writes every row that you insert or delete to the log. When you update a row, SQL Server logs both the old and new values. SQL Server also records transactions such as creating new objects, changing permissions, adding users, and allocating space for tables. After a transaction log fills completely, you can't make any more changes to the database. As a result, available space in the transaction log is a critical resource.

You determine the size of your transaction log when you create your database. You can create a database that puts the data and the log on separate devices or on the same device. When the data and log are on one device, they compete for the same space. For example, suppose you are creating a 100MB database. You can create the database so that it reserves 80MB for data (including any indexes on the data) and 20MB for transaction log records. Or, you can create the database so that it reserves 100MB for either data or log records.

Most production application databases separate the transaction log from the data. Separating the data and the log provides several advantages. SQL Server can perform incremental backups that capture only those transactions processed since the last backup, instead of backing up the entire database. In addition, SQL Server can back up the transaction log even if the device containing the data has failed, providing up-to-the-minute recovery. This setup improves performance and lets you manage space more efficiently. If the database runs out of space, the generated error message will tell you whether the data storage area or the transaction log is full. If you combine the data and log, you won't know whether the data storage area or the transaction log is full.

When you keep the data and log separate and you run out of space for data, you'll get a Msg 1105, Level 17, State 1 error message stating that SQL Server can't allocate space because the default segment is full. When you run out of space for the transaction log, you'll get a Msg 1105, Level 17, State 2 error message stating that SQL Server can't allocate space because the log segment is full.

These two situations require different responses. If your data space is full, you might have acquired more than the anticipated number of product orders or new customers. You can archive the older data or increase the size of your database. If your log space is full, you can simply back up the transaction log using the NO_LOG option of the DUMP TRAN command. You can read about these features in Microsoft's SQL Server Books Online (BOL) documentation.

If the log fills up too quickly or too often, you need to tune SQL Server. For example, you can write queries, particularly update operations, to use less log space. You can use the SELECT INTO operation to move data from one table to another or the bulk copy program (bcp) utility to bulk-load data from an external file without excessive logging. Microsoft's BOL documentation and Knowledge Base articles can provide more information about performing these tasks.

Ideally, you need to clear the transaction log often enough to prevent it from filling completely. How fast the log fills depends on the size and frequency of your transactions. During the development and testing cycle of an application, you can determine a manageable log size. After you determine the ideal interval for backing up and clearing the transaction log, you can define a SQL Executive task to automatically run the DUMP TRAN command on a predefined schedule (e.g., once a day or every hour).

Monitoring Transaction Log Space
Performance Monitor can help you determine the ideal frequency of transaction log dumps by tracking transaction log growth. As Screen 1 shows, you can use four counters to monitor SQL Server logs. The Instance box lists the databases that have a separate transaction log.

One of the most valuable counters is Max Log Space Used. The Max Log Space Used counter retains the highest percent of allocated log space the transaction log uses. You can determine the ideal transaction log size by monitoring the Max Log Space Used counter as you adjust the size of the transaction log. Choose a transaction log size for which the Max Log Space Used counter never exceeds 75 percent or 80 percent during normal expected usage.

Setting Alerts
You use two types of alerts to alleviate potential problems before they affect users and applications. You set up Performance Monitor to alert SQL Server when a counter's value crosses a specific threshold. You use Performance Monitor's Alert View to define Performance Monitor alerts. You set up SQL Executive alerts to define the actions SQL Server takes to correct the problem. You use SQL Enterprise Manager's (EM's) Alert Manager to define SQL Executive alerts. You can use both types of alerts, for example, to set up a mechanism that will automatically truncate the transaction log if it fills more than 80 percent of its allocated space. Setting up this mechanism requires five steps.

1. Create a SQL Executive task. For the SQL Executive to clear out the transaction log, you must define a task to execute the DUMP TRAN command. Using the SQL EM, right-click the SQL Executive icon and select Manage Scheduled Tasks (or select Server, Scheduled Tasks from the SQL EM main menu). Then select New Task. Screen 2 shows the New Task dialog box.

The new task, dump_sales_log, is a Transact-SQL (T-SQL) type task, which lets you enter any T-SQL command in the command box. The command for this task is

dump tran sales with truncate_only

You don't have to select the name of the database you are monitoring from the Database drop-down list because the T-SQL command already contains it. You need to enable the task by selecting On Demand in the Task Schedule window. This setting lets the SQL Executive Alert Manager demand that the task run when the error occurs.

2. Create a new error number. Because the system does not recognize an 80 percent capacity database transaction log as an error, no predefined error message exists for that situation. You can create an error message with SQL EM by selecting Server, Messages from the SQL EM menu and clicking New at the bottom of the screen. You can select any unused error number larger than 50,000 and supply a message. Enter a severity setting greater than or equal to 16, because anything below 16 signals a user error and the system rarely fires an alert for such a minor error. As Screen 3 shows, the error number for this example is 55555. Be sure to select the Always Write to Windows NT Eventlog check box because the NT Application Log is the only place where the SQL Executive Alert Manager will look for the error.

3. Define the SQL Executive alert. Using the SQL EM, right-click the SQL Executive icon and select Manage Alerts (or select Server, Alerts, Operators from the SQL EM's main menu). Then click New Alert. Screen 4 shows you SQL EM's New Alert dialog box.

Select the name of the database you are monitoring (i.e., sales in the example) from the Database Name drop-down list and select the enabled check box. You can define the alert's response by selecting a task from the Task to Execute drop-down list, write an email notification for SQLMail to send to the operator, or both.

4. Test the SQL Executive alert. Before integrating the new SQL Executive alert, you can test it from within SQL EM to make sure it fires when the defined error occurs (i.e., the transaction log fills to more than 80 percent capacity) and prompts the correct action (i.e., clears the transaction log). First, check the size of the log by running

DBCC CHECKTABLE(SYSLOGS)

and note the amount of free space available. Next, force SQL Server to generate the new error message by using a SQL query window to execute this command

RAISERROR (55555, 17, 1) WITH LOG

You can verify that this message appears in the NT Application Log by using NT's Event Viewer to open the Manager Alerts and Operators screen in SQL EM. (Click Refresh if you have left this screen open.) You can verify that the task ran by opening the Manage Scheduled Tasks screen and noting the last run date. Finally, you can verify that the program truncated the log by running the command

DBCC CHECKTABLE(SYSLOGS)

If the alert is working, the amount of free space will have increased.

5. Define the Performance Monitor alert. You can configure the Performance Monitor alert to activate when the value of the counter exceeds a certain threshold. In Performance Monitor, select View, Alert, Add. At the bottom of the Add to Alert dialog box, define the threshold and the server's action when the threshold is crossed. Screen 5 shows Performance Monitor's Add to Alert screen.

In this example, the server will run SQL Server's sqlalrtr program. The sqlalrtr program interprets an error number as an argument, which causes SQL Server to generate a RAISERROR WITH LOG statement. The complete command in the Run Program on Alert text box shown in Screen 5 is

c:mssqlbinnsqlalrtr /E 55555 /T /S

in which the name of your SQL Server machine replaces server_name. The /E provides the error number to write to the NT Application Log. If Performance Monitor and SQL Server run on different machines, you must provide the name of the SQL Server machine with the /S flag. Finally, you need to specify a login name and password for SQL Server or use the /T flag to specify you want to use a trusted connection. If your SQL Server is not installed on the C drive's default directory, you must change the path for the sqlalrtr executable. The sqlalrtr program will write the message to the NT Application Log. As soon as the SQL Executive accesses the CPU, it will fire the alert and run the associated task.

Running Performance Monitor as a Service
To detect whether a transaction log is reaching full capacity, you must run Performance Monitor and SQL Server simultaneously. However, because Performance Monitor requires a lot of resources and is usually a foreground program, you must manually start Performance Monitor with SQL Server. This situation might be inconvenient if SQL Server is configured to start automatically when you boot NT. Fortunately, monitor.exe, a utility in the Microsoft Windows NT Server 4.0 Resource Kit, lets Performance Monitor run as a service. You can save your alert definitions in a Performance Monitor Workspace (PMW) file and then specify this file as an argument to monitor.exe. The resource kit documentation provides sufficient detail to help you with this task.

An Important Counter
In addition to the five predefined counters for monitoring SQL Server with Performance Monitor, the counters that let you track the growth of your transaction logs are especially useful. And because you need to do more than just monitor growth of an application, the technique for integrating Performance Monitor and SQL Executive alerts so that SQL Server automatically clears the log when it reaches a defined percentage of its capacity easily solves the transaction log space problem. This technique can help administrators proactively manage SQL Server.