Microsoft SQL Server 7.0 and SQL Server 2000 Management Pack Guide

  • Article
Archived content. No warranty is made as to technical accuracy. Content may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist.

Published: November 1, 2003

Microsoft SQL Server 7.0 and SQL Server 2000 Management Pack Refresh for Microsoft Operations Manager 2000 SP1

Click here to download a copy of this guide

On This Page

Recommended Knowledge and Skills
Overview of the Microsoft SQL Server Management Pack Refresh
Deploying the Microsoft SQL Server Management Pack Refresh
Database Space Analysis and Thresholds
Remote Connectivity Monitoring
Service Pack Compliance
Database Health Monitoring
Database Configuration Monitoring
Excluding Databases from Monitoring
SQL Server Performance Monitoring
Operations Tasks
Recommendations for the Network Operations Desk
Troubleshooting
Reference

To deploy and operate the Microsoft® SQL Server™ Management Pack Refresh, it is recommended that you possess the following knowledge and skills:

  • A working knowledge of the Microsoft Operations Manager 2000 (MOM) monitoring features.

  • Be familiar with the recommended process for deploying Management Packs as described in Chapter 7, “Deploying Management Packs,” in the Microsoft Operations Manager 2000 SP1 Deployment Guide.

  • Have an administrative and working knowledge of Microsoft SQL Server 7.0 and SQL Server 2000.

  • Have a working knowledge of databases and Transact-SQL.

Overview of the Microsoft SQL Server Management Pack Refresh

The Microsoft SQL Server Management Pack Refresh for MOM SP1 provides both proactive and reactive monitoring of Microsoft SQL Server 7.x and Microsoft SQL Server 2000. This Management Pack is provided as a Web download upgrade for existing users of the Microsoft SQL Server Management Pack and provides additional monitoring of SQL Server functionality in the following key areas:

  • Microsoft SQL Server clusters

  • Microsoft SQL Server 2000 named instances

  • Database and database transaction log free space monitoring

  • Microsoft SQL Server service monitoring

  • Local and remote connectivity monitoring

  • Database health monitoring

  • Support for Microsoft SQL Server named instance and clustered databases with MOM SP1 reporting

  • An easy-to-understand and navigate processing rule group hierarchy

  • Public views of key operating system and SQL Server performance metrics

Microsoft SQL Server Management Pack Monitoring Scenarios

The Microsoft SQL Server Management Pack monitors key data points to ensure the availability of SQL Server and related components as well as reliable day-to-day operation of SQL Server.

SQL Server Availability and SQL Server Named Instance Availability

  • SQL Server service monitoring

  • SQL Server local connectivity monitoring

  • Database and transaction log space monitoring

  • Database health monitoring

  • SQL Server remote connectivity monitoring

  • SQL Server port bind errors

  • Collection of SQL Server Windows® event log events

SQL Server Cluster Availability

  • SQL Server clustered SQL service availability

SQL Server Agent Availability

  • SQL Server Agent availability

  • Monitor failed SQL Server agent jobs

MSDTC

  • SQL Server MSDTC errors

Performance

  • Deadlocks and blocks

  • Excessive user connections

  • Pages writes and allocations

  • Active transactions

  • Average wait time

  • DBCC logical scan bytes per second

  • Full scans per second

  • Memory grants pending

Reporting

  • Replication metrics

  • Backup device throughput

  • Lock performance

  • Log performance

  • SQL Server critical errors

Security

  • Trusted and non-trusted security configurations

  • Collection of failed SQL Server login attempts

Backups

  • Failed full database backups

  • Failed incremental database backups

  • Database device throughput

  • Successful database backups

Replication

  • Snapshot replication

  • Merge replication

  • Transactional replication

  • Replication components

  • Replication agents running

Deploying the Microsoft SQL Server Management Pack Refresh

Upgrading the Management Pack

The Microsoft SQL Server Management Pack Refresh is an upgrade of existing versions of the Management Pack. It is essential that previous versions of the Management Pack are installed prior to installing this Management Pack.

The upgrade process replaces the existing SQL Server Management Pack. This updated Management Pack includes significant changes to the structure of the existing Management Pack hierarchy and relocates a large number of rules. Therefore, if you have made customizations to existing rules in a previous version of the SQL Server Management Pack, it is recommended that you copy these customized rules to a dedicated Management Pack for the upgrade process. You should also perform a full database backup prior to upgrading this Management Pack.

Note: No changes are made to existing SQL Server Management Pack reports. All reports continue to work as is.

The Import Management Pack wizard in MOM allows you to perform a backup of the previous version of the SQL Server Management Pack. The best practice is to leave the Backup existing Management Pack on upgrade check box selected. This allows you to revert to the previous Management Pack, if required.

To upgrade the SQL Server Management Pack

  1. In the MOM Administrator console, navigate to Rules, Processing Rule Groups.

  2. Right-click Processing Rule Groups, and then click Import Management Pack.

  3. Enter the path to the Microsoft SQL Server.akm file or browse to and select this file.

  4. Verify that Replace Existing Management Pack is selected.

  5. Verify that the Backup existing Management Pack on upgrade check box is selected.

  6. Click Next.

  7. Select the desired options for backing up the previous version of the SQL Server Management Pack, and then click Next.

  8. Click Finish.

To verify that the upgraded SQL Server Management Pack appears in the MOM Administrator console

  1. In the MOM Administrator console, navigate to Rules, and then click Microsoft SQL Server.

  2. In the right pane, review the Purpose section of the knowledge base content, and then verify that the last sentence in this section lists a build number of 1334 or later.

After upgrading the SQL Server Management Pack, commit the new configuration changes manually to ensure that agents receive the new SQL Server Management Pack rules.

To commit configuration changes

  1. In the MOM Administrator console, navigate to Rules.

  2. Right-click Rules, and then click Commit Configuration Change.

Setting Up Reporting

By default, no data is collected for reporting. This prevents unnecessary database growth. To enable reporting, the processing rules that collect the data for reports must be enabled. This section lists the processing rules that need to be enabled and includes setup for the following reporting scenarios:

  • Reporting for clustered databases

  • Reporting for named instances on SQL Server 2000

For a list of reports that are included in the SQL Server Management Pack Refresh, see the “Reports” section later in this guide.

Enabling Dependent Rules

The processing rules that collect data for reporting are listed in the following locations.

SQL Server 2000

  • \SQL Server 2000\SQL Server 2000 Report Collection Rules\Event Processing Rules

  • \SQL Server 2000\SQL Server 2000 Report Collection Rules\Performance Processing Rules

  • \SQL Server 2000\Server Performance Collection\Replication Performance Collection\Event Processing Rules

  • \SQL Server 2000\Server Performance Collection\Replication Performance Collection\Performance Processing Rules

SQL Server 7.0

  • \SQL Server 7.0\SQL Server 7.0 Report Collection Rules\Event Processing Rules

  • \SQL Server 7.0\SQL Server 7.0 Report Collection Rules\Performance Processing Rules

To collect the data that is used by reports, you must enable the associated processing rules. The following table lists the processing rules that must be enabled for each report.

Note: Not all reporting processing rules are available for both SQL Server 7.0 and SQL Server 2000. Also, report names in some processing rule groups are preceded by Report Collection—.

Table 1 Processing Rules That Gather Data for Capacity Planning Reports

Report

Processing Rule

Capacity Planning — SQL Server Backup Device Capacity Planning by Day

Device Throughput Bytes/sec

Capacity Planning — SQL Server User Connections by Day

User Connections

Capacity Planning — SQL Server User Connections by Peak Hours

User Connections

Operations — SQL Server Critical Events

SQL Server Critical Events

Operations — SQL Server Critical Events Trend by Day

SQL Server Critical Events

Performance Analysis — SQL Server Distribution Replication Performance Analysis

Replication Dist:Delivered Cmds/sec,

Replication Dist:Delivered Trans/sec

Replication Dist:Delivery Latency

Performance Analysis — SQL Server Lock Performance Analysis

Lock Requests/sec

Lock Waits/sec

Number of Deadlocks/sec

Performance Analysis — SQL Server Log Performance Analysis

Log Flushes/sec

Log Flush Wait Time

Log Flush Waits/sec

Performance Analysis — SQL Server Logreader Replication Performance Analysis

Logreader:Delivered Cmds/sec

Logreader:Delivered Trans/sec

Logreader:Delivery Latency

Performance Analysis — SQL Server Memory Performance Analysis

Page Faults/sec - sqlserv

Private Bytes - sqlserv

Total Server Memory

Buffer Cache Hit Ratio

Free Buffers (SQL Server 2000 only)

Lazy Writes/Sec

Readahead Pages/Sec

Performance Analysis — SQL Server Merge Replication Performance Analysis

Replication Merge-Conflicts/sec

Replication Merge-Downloaded Changes/sec

Replication Merge-Uploaded Changes/sec

Performance Analysis — SQL Server Overall Replication Performance Analysis

Replication Database Pending Xacts

Replication Database Trans. Rate

Performance Analysis — SQL Server Snapshot Replication Performance Analysis

Replication Snapshot:Delivered cmds/sec

Replication Snapshot:Delivered Trans/sec

Reporting for Clustered Databases

The SQL Server Management Pack Refresh supports reporting against clustered servers. To report against clustered servers, the virtual server name must be added to the MS SQL Server 2000 computer group.

To add virtual server names to the MS SQL Server 2000 computer group

  1. In the MOM Administrator console, navigate to Rules, Computer Groups.

  2. Right-click the MS SQL Server 2000 computer group, and then click Properties.

  3. Click the Included Computers tab, and then click Add.

  4. On the Computer menu, select Equals, and then enter the virtual server name.

  5. Click OK, and then Apply.

Reporting for Named Instances on SQL Server 2000

The SQL Server Management Pack Refresh supports reporting against named instances of SQL Server. A list of the installed SQL Server instances on each server is collected by a script every 24 hours. By default, this script is disabled. To configure reporting for named instances, enable the following processing rule:

  • \Microsoft SQL Server\SQL Server 2000\Report Collection Rules\Event Processing Rules\Named Instance Discovery (24 hours)

Service Monitoring and Local Connectivity

The SQL Server Management Pack Refresh monitors the availability of critical SQL Server services such as the SQL Server service, the SQL Agent service, and optionally the Full Text Search service. As part of the service check functionality, the SQL Server Management Pack also monitors the availability of SQL Server for connections by querying the server for data. The service check functionality is fully named instance and cluster aware, and it does not generate an alert if a clustered node is passive (and the SQL Server services are not running). On a server with multiple instances of SQL Server installed, each instance is queried to ensure that all appropriate services are running and functional.

Service monitoring and local connectivity is checked every five minutes, by default.

Processing Rules

Service monitoring and local connectivity rules are listed in the following locations:

  • \SQL Server 2000\SQL Server 2000 Health and Availability Monitoring\Event Processing Rules\SQL Server Service Availability

  • \SQL Server 7.0\SQL Server 7.0 Health and Availability Monitoring\Event Processing Rules\SQL Server Service Availability

The following table lists the service availability processing rules.

Table 2 SQL Server Service Availability Processing Rules

Processing Rule

Default Value

SQL Server Service Availability

Enabled

SQL Server Service Availability — Connection Failure

Enabled

SQL Server Service Availability — SQL Server Agent service not running

Enabled

SQL Server Service Availability — SQL Server Full Text Search not running

Enabled

SQL Server Service Availability — SQL Server service not running

Enabled

Scripts

While there are five SQL Server service availability processing rules, the first of these rules (SQL Server Service Availability) stores the script and executes the script parameters for service availability monitoring.

The best practice for modifying these service availability monitoring settings is to adjust the script parameters in the SQL Server Service Availability processing rule, rather than disabling any of the remaining service availability processing rules. If you disable any of the remaining service availability processing rules without disabling the script responses associated with those rules, the scripts continues to run but the alerts are not generated.

The following table lists the script parameters, descriptions, and default states.

Table 3 SQL Server Service Availability Script Parameters

Script Parameter

Description

Default Value

CheckSQLAgent

Logs a warning event when the SQL Agent service is not running

Enabled

CheckSQLConnection

Logs an error event when SQL Server local connectivity cannot be established

Enabled

CheckSQLServer

Logs an error event when the SQL Server service is not running

Enabled

CheckSearch

Logs a warning event when full text search is not available

Disabled

InformationEvent

Logs an information event each time the script runs

Disabled

Customization

SQL Server service availability monitoring can be customized to modify the behavior to only check some services and connectivity. However, the default configuration effectively monitors the health of SQL Server. Therefore, the best practice is to not change the default configuration.

To customize SQL Server service monitoring

  1. In the MOM Administrator console, navigate to the SQL Server Service Availability processing rule.

  2. Right-click the rule, and then click the Response tab.

  3. Select the rule, and then click Edit.

  4. In the Script parameters box, modify the script responses by selecting a script, and then click Edit Script Parameters.

  5. After modifying script responses, click OK, and then Apply.

After modifying the script responses, commit the configuration changes to apply the changes to agent computers.

Database Space Analysis and Thresholds

The SQL Server Management Pack monitors the space of all databases and transaction logs. Granular thresholds can be defined based on the role of the databases.

Processing Rules

The database space analysis processing rules are listed in the following locations:

  • \SQL Server 2000\SQL Server 2000 Health and Availability Monitoring\Event Processing Rules\SQL Server Database Space Analysis

  • \SQL Server 7.0\SQL Server 7.0 Health and Availability Monitoring\Event Processing Rules\SQL Server Database Space Analysis

The following table lists the database space analysis processing rules.

Table 4 SQL Server Service Availability Processing Rules

Processing Rule

Default Value

SQL Server Database Space Analysis

Enabled

SQL Server Database Space Analysis — Database space error

Enabled

SQL Server Database Space Analysis — Database space warning

Enabled

SQL Server Database Space Analysis — Log file space error

Enabled

SQL Server Database Space Analysis — Log file space warning

Enabled

Scripts

While there are five SQL Server database space processing rules, the first of these rules (SQL Server Database Space) stores the script responses for database space monitoring. The following table lists the script parameters, the database to which they are applied, the threshold settings, and the default states. If the indicated database is General, the script is applied to all databases except those specifically called out in the table, such as the System Databases and the TempDB databases. The Model database is excluded from space monitoring.

Table 5 SQL Server Service Availability Script Parameters

Script Parameter

Database

Event Severity

Threshold Value

Default Value

DBErrorLevel

General databases

Error

100 MB

Enabled

DBWarningLevel

General databases

Warning

200 MB

Enabled

LogErrorLevel

General Transaction logs

Error

75 MB

Enabled

LogWarningLevel

General Transaction logs

Warning

150 MB

Enabled

SysDBErrorLevel

System Databases (MSDB and Master)

Error

15 MB

Enabled

SysDBWarningLevel

System Databases (MSDB and Master)

Warning

30 MB

Enabled

SysLogErrorLevel

System Transaction Logs (MSDB and Master)

Error

15 MB

Enabled

SysLogWarningLevel

System Transaction Logs (MSDB and Master)

Warning

30 MB

Enabled

TempDBErrorLevel

TempDB

Error

100 MB

Enabled

TempDBWarningLevel

TempDB

Warning

200 MB

Enabled

TempLogErrorLevel

TempDB Transaction Logs

Error

75 MB

Enabled

TempLogWarningLevel

TempDB Transaction Logs

Warning

150 MB

Enabled

Monitoring for Free Space

Database and transaction log space monitoring intelligently monitors free space and takes into account database automatic growth, multiple file groups, and files. The free space check runs every hour, by default, and checks each installed database on the server. When checking the database, each database and transaction log file group and associated file is enumerated.

Reporting for Databases That Are Configured to Automatically Grow

When checking the free space, each file is checked for the Automatically grow file option. If this option is configured, the SQL Server Management Pack does not generate warnings or errors for these databases.

Excluding Databases from Monitoring

Individual databases can be excluded from monitoring, if required. For more information, see the “Excluding Databases from Monitoring” section later in this guide.

Combining Database Space Monitoring with Disk Space Monitoring

It is recommended that you use database and transaction log free space monitoring in combination with disk space monitoring to ensure that databases that are set to automatically grow have sufficient disk space as they grow. The SQL Server Management Pack includes two public views that help monitor disk space. The public views are in the following location:

  • Monitor\Public Views\Microsoft SQL Server\SQL Server 2000\Server Resource Utilization\Disk Capacity

The following disk space views are provided:

  • % Free Space

  • Free Megabytes

Both of these views assist in determining whether sufficient free space is available on the disk subsystem.

Customizing Thresholds

The database and transaction log space monitoring thresholds can be customized. If you are customizing thresholds, it is recommended that you work with database administrators to determine the appropriate warning and error threshold levels. By default, the space thresholds do not require any customization.

To customize database space analysis monitoring

  1. In the MOM Administrator console, navigate to the SQL Server Database Space Analysis processing rule.

  2. Right-click the rule, and then click the Response tab.

  3. Select the rule, and then click Edit.

  4. In the Script parameters box, modify the script responses by selecting a script, and then click Edit Script Parameters.

  5. After modifying script responses, click OK, and then Apply.

After modifying the script responses, commit the configuration changes to apply the changes to agent computers.

Remote Connectivity Monitoring

The SQL Server Management Pack provides a simple way to verify remote connectivity to SQL Server and to verify that users can successful connect to remote SQL Server databases. While other monitoring tools do not verify that the network stack of a server is functioning or that other network devices between the server and the end user are functioning, the SQL Server Management Pack Refresh provides client-side monitoring of remote SQL Server databases. This feature monitors the availability of SQL Server by querying the server remotely from client computers that you designate.

To check for remote connectivity, the SQL Server Management Pack runs a timed user-defined Transact-SQL remote connectivity script on computers that are members of the following computer groups:

  • MS SQL Server 2000 Client Side Monitoring

  • Microsoft SQL Server 7.x Client Side Monitoring

The script is run against servers listed in the SQL remote connectivity processing rule. If the script fails to execute successfully or takes too long to execute, alerts are generated.

You can designate multiple remote client computers; however, each client computer checks for connectivity on all servers that are listed in the SQL remote connectivity processing rule. Specifying multiple remote client computers is useful for checking connectivity from different locations across a network.

Remote connectivity checking requires that Structured Query Language-Distributed Management Objects (SQL-DMO) components be installed on the designated remote client. You can meet this requirement by either installing SQL Server or SQL Server client tools on designated remote client computers.

The following figure highlights the two-step process for remote connectivity checking.

Cc723729.sqlmp01(en-us,TechNet.10).gif

Figure 1: SQL Server remote connectivity check

Processing Rules

The client-side monitoring processing rules are listed in the following locations:

  • \SQL Server 2000\Client Side Monitoring\Event Processing Rules\SQL Server Remote Connectivity

  • \SQL Server 7.0\Client Side Monitoring\Event Processing Rules\SQL Server Remote Connectivity

The following table lists the client-side monitoring processing rules.

Table 6 SQL Server Client-Side Monitoring Processing Rules

Processing Rule

Default Value

SQL Server Remote Connectivity

Enabled

SQL Server Remote Connectivity — Execution Failure

Enabled

SQL Server Remote Connectivity — Execution Time Warning

Enabled

Scripts

While there are several SQL Server remote connectivity processing rules, the first of these rules (SQL Server Remote Connectivity) stores the script parameters for remote connectivity monitoring. The following table lists the script parameters, descriptions, and default states for the SQL Server Remote Connectivity processing rule.

Table 7 SQL Server Service Availability Script Parameters

Script Parameter

Description

Default Value

CheckInstances

A comma-separated list of remote databases to connect to. This list might include only computer names (for a default instance) or a computer and instance name (for example, Server\Instance10).

Disabled

DatabaseName

The database to run the query against. By default, the database is set to Master.

Enabled

ExecutionTimeAlert

The acceptable time for the specified query to execute. If the query takes longer than the specified time to execute, an alert is raised. This number can be a decimal number, for example 0.01. The default value is 5 seconds.

Enabled

InformationEvent

Logs an information event each time the script runs.

Disabled

Query

The Transact-SQL query that is run on the designated client computer to check connectivity to SQL Server databases specified in the CheckInstances script parameter. The default query is SELECT GETDATE.

Enabled

Configuring Client Monitoring of Remote Connectivity

Client monitoring of remote connectivity is configured by:

  • Installing either SQL Server or SQL Server client tools on the designated client computers.

  • Installing a MOM agent on the designated client computers. Add the designated client computers to the same configuration group that you are using to monitor remote connectivity.

  • Adding designated client computers to the appropriate computer group, according to the SQL Server version:

    • MS SQL Server 2000 Client Side Monitoring

    • MS SQL Server 7.x Client Side Monitoring

  • Ensuring that the MOM service account on the designated client computer has the appropriate permissions to connect to the SQL Server computers.

  • Adding SQL Server computers and instances to the SQL Server remote connectivity processing rule.

To add computers to the MS SQL Server client-side monitoring computer groups

  1. In the MOM Administrator console, navigate to Rules, Computer Groups.

  2. Right-click the appropriate computer group, and then click Properties.

  3. Click the Included Computers tab.

  4. Click Add to add computers, add computer criteria, and then click OK.

  5. Click Apply, and then OK.

Be sure to initiate a managed computer scan after modifying a computer group membership.

To add computers to the SQL Server remote connectivity processing rule

  1. In the MOM Administrator console, navigate to the Client Side Monitoring processing rule group, and then click Event Processing Rules.

  2. Right-click the SQL Server Remote Connectivity processing rule, and then click Properties.

  3. Click the Responses tab, select SQL Server Remote Connectivity, and then click Edit.

  4. In the Script parameters box, select CheckInstances, and then click Edit Script Parameters.

  5. In the Value field, enter a comma-separated list of instances to connect to, and then click OK. To check for connectivity to a default instance, enter only the server name. To check for connectivity to a named instance, enter the server name and instance name by using the following format: server_name\instance_name.

  6. After modifying script responses, click OK, and then Apply.

After modifying the computer group membership and script responses, commit the configuration changes to apply the changes to agent computers.

Alerts

The results of the query determine the alerts that are generated:

Alert – The query failed to execute.

Warning: – The query executed outside the acceptable time threshold.

Service Pack Compliance

The SQL Server Management Pack can monitor servers running SQL Server to ensure that they are running the most recent SQL Server service pack or hotfix version. This checking generates an alert when a computer is running a service pack or hotfix version earlier than the specified version. The checking can also optionally generate an informational alert for service pack compliance that can be used for auditing purposes.

Service pack compliance monitoring is not configured by default. While the processing rules that are associated with service pack compliance are enabled by default, the script responses must be configured to monitor for service pack compliance.

Version compliance is performed by using the SQL Server version string, where the version strings beginning with 7 are SQL Server 7.x and the version strings beginning with 8 are SQL Server 2000. The following table lists SQL Server version strings and their service pack levels.

Table 8 SQL Server Version Strings and Associated Releases

Version String

Service Pack Level

7.00.163

SQL Server 7.x RTM

7.00.699

SQL Server 7.x Service Pack 1

7.00.842

SQL Server 7.x Service Pack 2

7.00.961

SQL Server 7.x Service Pack 3

7.00.1063

SQL Server 7.x Service Pack 4

8.00.194

SQL Server 2000 RTM

8.00.384

SQL Server 2000 Service Pack 1

8.00.534

SQL Server 2000 Service Pack 2

8.00.760

SQL Server 2000 Service Pack 3

For information about how to determine the SQL Server version, see article 321185 in the Microsoft Knowledge Base.

Processing Rules

The service pack compliance processing rules are listed in the following locations:

  • \SQL Server 2000\SQL Server 2000 Health and Availability Monitoring\Event Processing Rules\SQL Service Pack Compliance

  • \SQL Server 7.0\SQL Server 7.0 Health and Availability Monitoring\Event Processing Rules\SQL Service Pack Compliance

The following table lists the service pack compliance processing rules.

Table 9 SQL Service Pack Compliance Processing Rules

Processing Rule

Default Value

SQL Service Pack Compliance

Enabled

SQL Service Pack Compliance — Version does comply

Enabled

SQL Service Pack Compliance — Version does not comply

Enabled

Scripts

While there are several SQL Server service pack compliance processing rules, the first of these rules (SQL Service Pack Compliance) stores the script parameters for service pack monitoring. The following table lists the script parameters, descriptions, and default states for the SQL Service Pack Compliance processing rule.

Table 10 SQL Service Pack Compliance Script Parameters

Script Parameter

Description

Default Value

AlertOnAll

Generates a success event if the version complies.

False (Disabled)

InformationEvent

Logs an information event when the script is run.

False (Disabled)

VersionString

The SQL Server version string that is used to evaluate service pack compliance. The version string is the earliest version that is acceptable to meet service pack compliance.

8.00.194

Configuring Service Pack Monitoring

By default, service pack compliance is enabled. However, you need to configure the version string for your environment. Additionally, you can optionally configure the script to generate an event for service pack compliance success.

To configure service pack monitoring

  1. In the MOM Administrator console, navigate to the SQL Service Pack Compliance processing rule.

  2. Right-click the rule, and then click the Response tab.

  3. Select the rule, and then click Edit.

  4. In the Script parameters box, select AlertOnAll, and then click Edit Script Parameters.

  5. If you want to receive success version compliance alerts, type True in the Value box, and then click OK.

  6. In the Script parameters box, select VersionString, and then click Edit Script Parameters.

  7. In the Value box, enter the SQL Server version number that applies to your environment, and then click OK.

  8. After modifying script responses, click OK.

  9. Click the General tab, select Enabled, and then click Apply.

After modifying the script responses, commit the configuration changes to apply the changes to agent computers.

Database Health Monitoring

The SQL Server Management Pack Refresh monitors database health. In the event that a database enters an unhealthy state (such as Suspect), the Management Pack generates an alert. However, the Management Pack does not generate an alert if a database enters an intentional states such as Offline or Read Only.

Database health monitoring checking occurs every 15 minutes by default and requires no configuration changes to work.

An unhealthy database generates a critical error alert. The checking script allows high-impact databases to be defined, without which SQL Server or a particular application cannot function. High-severity databases that are entering an unhealthy state generate a service unavailable alert. These databases are defined as high-severity databases. By default, the following databases are defined as high severity:

  • Master

  • TempDB

  • Model

  • msdb

  • Distribution

  • OnePoint

Individual databases can be excluded from monitoring, if required. For more information, see the “Excluding Databases from Monitoring” section later in this guide.

Processing Rules

The database health monitoring processing rules are listed in the following locations:

  • \SQL Server 2000\SQL Server 2000 Health and Availability Monitoring\Event Processing Rules\SQL Server Database Health

  • \SQL Server 7.0\SQL Server 7.0 Health and Availability Monitoring\Event Processing Rules\SQL Server Database Health

The following table lists the database health monitoring processing rules.

Table 11 SQL Server Database Health Processing Rules

Processing Rule

Default Value

SQL Service Database Health

Enabled

SQL Service Database Health — Database is unhealthy

Enabled

SQL Service Database Health — High impact database is unhealthy

Enabled

Scripts

While there are several SQL Server database health processing rules, the first of these rules (SQL Server Database Health) stores the script parameters for database health monitoring. The following table lists the script parameters, descriptions, and default states for this processing rule.

Table 12 SQL Server Database Health Script Parameters

Script Parameter

Description

Default Value

HighSevDatabases

A comma-separated list of databases that are of high importance and require extra health checks

master, tempdb, model, msdb, distribution, onepoint

InformationEvent

Generates an information event when the script is run

False (Disabled)

Customizing Database Health Monitoring

You can customize database health monitoring by adding databases to the list of high-severity databases.

To add databases to the high-severity list

  1. In the MOM Administrator console, navigate to the SQL Server Database Health processing rule.

  2. Right-click the rule, and then click the Response tab.

  3. Select the rule, and then click Edit.

  4. In the Script parameters box, select HighSevDatabases, and then click Edit Script Parameters.

  5. In the Value box, enter the database names, and then click OK.

  6. After modifying script responses, click OK, and then Apply.

After modifying the script responses, commit the configuration changes to apply the changes to agent computers.

Database Configuration Monitoring

Database configuration monitoring allows you to audit your environment for database configuration settings that you establish as a standard for your organization. By default, this monitoring feature is disabled. You must enable the processing rule associated with this monitoring feature and configure the script responses to check for the database configuration settings that apply to your environment.

Processing Rules

The database configuration monitoring processing rules are listed in the following locations:

  • \SQL Server 2000\SQL Server 2000 Health and Availability Monitoring\Event Processing Rules\SQL Server Database Configuration Monitoring

  • \SQL Server 7.0\SQL Server 7.0 Health and Availability Monitoring\Event Processing Rules\SQL Server Database Configuration Monitoring

The following table lists the database configuration monitoring processing rules.

Table 13 SQL Server Database Health Processing Rules

Processing Rule

Default Value

SQL Server Database Configuration Monitoring

Disabled

SQL Server Database Configuration Monitoring — Database is not configured correctly

Enabled

Scripts

The SQL Server Database Configuration Monitoring processing rule stores the script parameters for database configuration auditing. The following table lists the script parameters, descriptions, and default states for this processing rule.

Important: The default state for database configuration monitoring script parameters indicates whether an event is generated for a configuration setting that is either enabled or disabled. For example, if the default state of the AutoClose parameter is set to False, an event is generated if the AutoClose configuration setting on a database is enabled.

Table 14 SQL Server Database Health Script Parameters

Script Parameter

Description

Default Value

AutoClose

Checks for the state of the Auto close configuration setting

False

AutoCreateStatistics

Checks for the state of the Auto create statistics configuration setting

True

AutoShrink

Checks for the state of the Auto shrink configuration setting

False

AutoUpdatesStatistics

Checks for the state of the Auto updates statistics configuration setting

True

CrossDBChaining

Checks for the state of the Cross-database ownership chaining configuration setting

False

InformationEvent

Logs an event when this script is run

False (Disabled)

TornPageDetection

Checks for the state of the Torn page detection configuration setting

True

To customize SQL Server database configuration monitoring

  1. In the MOM Administrator console, navigate to the SQL Server Database Configuration Monitoring processing rule.

  2. Right-click the rule, and then click the Response tab.

  3. Select the rule, and then click Edit.

  4. In the Script parameters box, modify the script responses by selecting a script, and then click Edit Script Parameters.

  5. After modifying script responses, click OK, and then Apply.

After modifying the script responses, commit the configuration changes to apply the changes to agent computers.

Excluding Databases from Monitoring

The SQL Server Management Pack allows administrators to exclude individual databases from script-based monitoring. By adding databases to an exclusion file, alerts are not generated for the following monitoring scenarios:

  • Database space monitoring

  • Transaction log space monitoring

  • Database health

Exclusions are implemented by using a text file on the monitored server that lists database names. If databases that are to be excluded are in named instances, the database name should be preceded with the instance name. All databases are assumed to be in the default instance unless an instance is specified.

Save the file as SQLExclude.txt and place it in the c:\ folder.

The exclusions file is parsed when read. An invalid entry results in the reading being terminated for the invalid line. Reading continues at the next line until the end of the file is reached. An exclusion file can contain any number of databases; however, an exclusions list places an overhead on monitoring and should not be overused.

The following example shows a typical exclusion file.

Northwind
Master
Pubs
Instance1\Northwind
Instance1\Pubs
Instance2\Northwind

SQL Server Performance Monitoring

The SQL Server Management Pack collects performance data for sampling purposes. The collected data is displayed in public views that can be used to monitor the health of SQL Server. This section provides an overview of the performance monitoring that is provided in the SQL Server Management Pack Refresh. Review the monitoring rules, default states, and thresholds and configure these appropriately for your environment.

Performance Counters Collected by Default

The following performance counters are collected by default:

  • Active Transactions – A count of the current active transactions for the database.

  • Average Wait Time (ms) – The average amount of time (in milliseconds) for each lock request that resulted in a wait.

  • DBCC Logical Scan Bytes/second – The number of logical read scan bytes per second for database consistency checker (DBCC) statements.

  • Full Scans/second – The number of unrestricted full scans per second. These can be either base-table or full-index scans.

  • Lock Blocks – Individual resources, such as rows or tables, that are locked by a user or process. This counter represents the number of lock blocks currently in use on the server. This counter is refreshed periodically.

  • Lock Timeouts/second – The number of lock requests per second that timed out. This includes internal requests for NOWAIT locks.

  • Log Cache Reads/second – The count per second of reads performed through the log manager cache.

  • Log Truncations – The total number of times the database's transaction log has been truncated.

  • Logins/second – The total number of logins started per second.

  • Memory Grants Pending – The count per second of processes waiting for a workspace memory grant.

  • Mixed Page Allocations/second – The count per second of pages allocated from mixed extents. An extent is the smallest amount of space (eight contiguous pages or 16 KB) allocated for a table or index.

  • Page Writes/second – The count per second of issued database page writes. Page writes are generally expensive. One method for reducing page-write activity is to ensure that the free buffer pool does not run out of free buffers. If it does, page writes occur while waiting for an unused cache buffer to flush.

  • Pages Allocated/second – The count per second of pages allocated to database objects that are used for storing index or data records.

SQL Server Performance Threshold Defaults

The SQL Server Management Pack samples performance counters for threshold breaches on a regular basis. Breaches in these thresholds generate an alert. The following default thresholds are monitored by the SQL Server Management Pack:

  • Access Methods – Full scans > two for 15 minutes (disabled by default)

  • Buffer Manager – Page life expectancy < 300 second (disabled by default)

  • Cache Hit Ratio – < 90% for 15 minutes

  • Locks – The number of deadlocks > 1 for 15 minutes

  • SQL Server User Connections – > 500

It might be necessary to adjust thresholds based on the acceptable performance of your servers running SQL Server.

SQL Replication Performance Collection Defaults

This Management Pack collects counters that monitor the health of SQL Server replication. All data that is collected by these counters is displayed in public views. By default, all replication performance collection rules are disabled. Each performance collection rule must be enabled to collect data.

Processing rule group

All replication performance rules are located in the following processing rule group:

  • \SQL Server 2000\Server Performance Collection\Replication Performance Collection

Default performance counters

The following replication performance counters are collected by default:

  • Replication Distributor Delivered Commands/second

  • Replication Distributor Delivered Transactions/second

  • Replication Logreader Delivered Commands/second

  • Replication Logreader Delivered Transactions /second

  • Replication Merge Conflicts/second

  • Replication Merge Downloaded Changes/second

  • Replication Merge Uploaded Changes/second

  • Replication Snapshot Delivered Commands/second

  • Replication Snapshot Delivered Transactions/second

  • Replication Total Agents Running

Named Instance Performance Collection

This Management Pack can collect performance data from named and clustered instances of SQL Server. This performance collection is script based and disabled by default. When enabled, performance collection occurs for all named instances (and not default instances) using each script. Default instance performance collection uses standard MOM rules. The following named instance performance collection rules are included in the Management Pack.

Named instance base performance collection (15 minutes)

  • Location – SQL Server 2000\Server Performance Collection\Event Processing Rules

  • Purpose – This rule collects all default performance data every 15 minutes for named instances of SQL Server.

  • Default State – Disabled

Named instance replication performance collection (15 minutes)

  • Location – SQL Server 2000\Server Performance Collection\Replication Performance Collection\Event Processing Rule

  • Purpose – This rule collects all replication performance data every 15 minutes for named instances of SQL Server.

  • Default State – Disabled

Named instance reporting performance collection (15 minutes)

  • Location – SQL Server 2000\SQL Server 2000 Report Collection Rules\Event Processing Rules

  • Purpose – This rule collects all named instance performance counters that are required for reports to work every 15 minutes.

  • Default State – Disabled

Each named instance performance collection script can be toggled to adjust the individual performance counters that it collects. Each collected performance counter can be enabled and disabled by adjusting the script parameter.

Note: By default all scripts are disabled, but all counters are enabled. When enabling a script, all performance counters are collected.

To adjust the performance data that is collected

  1. In the MOM Administrator console, navigate to the desired performance rule.

  2. Right-click the rule, and then click the Response tab.

  3. Select the rule, and then click Edit.

  4. In the Script parameters box, select the desired script parameter, and then click Edit Script Parameters. The name of the parameter is the name of the performance counter.

  5. After modifying script responses, click OK, and then Apply.

After modifying the script responses, commit the configuration changes to apply the changes to agent computers.

Base Operating System Performance Monitoring

The SQL Server Management Pack monitors the underlying health of the base operating system, which might affect SQL Server health. All performance counters are collected every 15 minutes. Administrators should compare this list with the counters that were collected by other Management Packs to ensure that there is no duplicate collection of performance counters on SQL Servers.

The Management Pack monitors the following base operating system areas:

CPU

  • Context Switches/Second

  • Processor Queue Length

  • Total CPU Usage

  • CPU Usage – SQLAGENT process (threshold and collection)

  • CPU Usage – SQLSERVR process (threshold and collection)

  • CPU Usage – SQLMANGR process (threshold and collection)

  • CPU Usage and Processor Queue Length (threshold)

Disk

  • Disk Free Space Capacity

  • Disk Queue Length

  • Disk Read Latency (threshold)

  • Disk Write Latency (threshold)

Memory

  • Page Read/Sec

  • Page Writes/Sec

  • Pages/Sec

  • Page Nonpaged Bytes

  • Pool Paged Bytes

Network

  • Bytes Received/Sec

  • Bytes Sent/Sec

  • Bytes Total/Sec

Operations Tasks

The best practice is to review and prioritize all alerts on a daily basis. In addition, perform other tasks on a regular basis, depending on your environment. Many important problems do not cause alerts, but they still require periodic attention. The SQL Server Management Pack generates reports that display data over time and present patterns that indicate problems. Review the reports to resolve issues before they generate alerts.

You can perform the daily, weekly, and monthly tasks as specified in this guide. However, adjust the frequency of these tasks to meet the needs of your particular environment.

Daily Tasks

On a daily basis, perform the following tasks:

  • Review all open alerts.

  • Verify that all servers running SQL Server are communicating with the MOM Administrator console.

  • Review warnings (optional).

Reviewing All Open Alerts

Review all new alerts in the following order of priority:

  • Service Unavailable errors

  • Critical errors

  • SQL Server scripts, such as the SQL Server Service Availability and SQL Server Remote Connectivity

  • Warnings (optional)

  • Informational messages (optional)

Not all problems can be repaired in one day or less. Commonly, parts must be ordered or computers must be scheduled for restart. It is important that you follow up on these open alerts to ensure that they are addressed in a timely manner.

To review open alerts

  1. In the MOM Administrator console, navigate to Monitor, All Open Alerts.

  2. Review all alerts that are older than 24 hours to ensure that they are addressed in a timely manner.

Verifying That All SQL Servers Are Communicating with the MOM Administrator Console

Communication failure between the servers running SQL Server and the monitoring infrastructure prevents you from receiving alerts so that you can examine and resolve them.

To verify that servers running SQL Server are communicating with the MOM Administrator console

  1. In the MOM Administrator console, navigate to Monitor, All Agents.

  2. In the right pane, click the Last Contact column heading. This sorts all computers based on the last contact time. If the last contact time is greater than five minutes, determine why the computer is not communicating with MOM.

Weekly Tasks

In addition to the tasks that you perform daily, review the following reports weekly:

  • SQL Server Critical Events

  • SQL Server Critical Events Trend by Day

  • SQL Server Lock Performance Analysis

  • SQL Server Log Performance Analysis

  • SQL Server Memory Performance Analysis

For a description of the reports, see the “Reports” section later in this guide.

Monthly Tasks

In addition to the tasks you perform daily, and the reports you review weekly, review reports in the following categories on a monthly basis:

Capacity planning and trending

  • SQL Server Backup Device Capacity Planning by Day

  • SQL Server User Connections by Day

  • SQL Server User Connections by Peak Hours

SQL Server performance analysis (if using replication)

  • SQL Server Distribution Replication Performance Analysis

  • SQL Server Log Reader Replication Performance Analysis

  • SQL Server Merge Replication Performance Analysis

  • SQL Server Overall Replication Performance Analysis

  • SQL Server Snapshot Replication Performance Analysis

Other reports

Review other reports as appropriate for your installation.

As Needed Tasks

It is recommended that you perform the following tasks as required to ensure the availability of SQL Server:

  • Review all open alerts.

  • Verify that all MOM agents are communicating.

  • Use the SQL Server public views.

  • Use the SQL Server Health Monitoring public views.

Recommendations for the Network Operations Desk

It is recommended that network operations staff monitor servers running SQL Server for high-impact alert scenarios. These scenarios include:

  • SQL Server database and transaction log space warnings and errors.

  • SQL Server failed backups.

  • SQL Server failed agent jobs.

  • SQL Server connection errors.

  • Configuring notification groups.

The best practice is to review public views in the SQL Server Health Monitoring group and use these views to monitor the previous scenarios.

Troubleshooting

WMI Performance Collection

The SQL Server Management Pack can collect performance counters from named instances of SQL Server. This collection of performance data uses Windows Management Instrumentation (WMI) and requires access to WMI performance objects.

Due to known issues with SQL Server 2000 and Microsoft Windows Server™ 2003, WMI performance objects might not be available. The SQL Server Management Pack checks for this condition every 24 hours and notifies operators with an alert, with steps to resolve this issue.

Information Events

Information events can be enabled to trace the behavior of the Management Pack. Information events can be enabled for any script-determined health check in the Management Pack, such as Service Availability or Database Free Space Checking. When enabled, an information event is logged when the associated script is run. The event lists the servers or instances excluded during monitoring, such as passive nodes.

To enable information events, set the InformationEvent parameter to True on any script-determined health check.

Reference

Processing Rules That Generate Critical Events and Alerts

This section lists the processing rule groups and event processing rules in the SQL Server Management Pack. These are found in the MOM Administrator console at the following locations:

  • Processing Rule Groups\Microsoft SQL Server\SQL Server 2000

  • Processing Rule Groups\Microsoft SQL Server\SQL Server 7.0

Processing Rule Group: SQL Server Health Monitoring

The SQL Server Health Monitoring group contains rules that monitor the health of key SQL Server components.

Table 15 Processing Rules in the SQL Server Health Monitoring Group

Rule

Default Alert Severity

SQL Server Database Health - Database is unhealthy

Critical Error

SQL Server Database Health – High-impact database is unhealthy

Service Unavailable

SQL Server Database Space Analysis - Database space error

Critical Error

SQL Server Database Space Analysis - Log file space error

Critical Error

SQL Server Service Availability - Connection failure

Service Unavailable

SQL Server Service Availability - SQL Server Agent service is not running

Service Unavailable

SQL Server Service Availability - SQL Server service is not running

Service Unavailable

Reports

This section lists the reports that are included in the SQL Server Management Pack.

SQL Server Capacity Planning Reports

SQL Server Backup Device Capacity Planning by Day

This report provides a graph of the daily use of the SQL Server backup device. The graph indicates the minimum, maximum, and average backup device capacity based on the backup device/device throughput bytes per second performance counter. This report provides one graph for each specified server.

SQL Server User Connections by Day

This report provides a graph of the daily SQL Server user connections for the specified server during the specified time period, displayed by day. The graph indicates the minimum, maximum, and average user connections capacity based on the SQL Server/general statistics - user connections performance counter. This report provides one graph for each specified server.

SQL Server User Connections by Peak Hours

This report provides a graph of the daily SQL Server user connections for the specified server during the specified time period, displayed by day or by peak hours. The graph indicates the minimum, maximum, and average user connections capacity based on the SQL Server/general statistics - user connections performance counter. This report provides one graph for each specified server.

SQL Server Operations Reports

SQL Server Critical Events

This report lists critical events, which are events with a severity of 1, that have occurred on the specified server in the specified time period. This report is sorted alphabetically by server and by event time.

SQL Server Critical Events Trend by Day

This report provides a daily graph of all critical events, which are events with a severity of 1, that have occurred on the specified server in the specified time period.

SQL Server Performance Analysis Reports

SQL Server Distribution Replication Performance Analysis

This report provides a graph of several SQL Server distribution replication performance counters for the specified server during the specified time period. Performance counter data is averaged by the hour.

SQL Server Lock Performance Analysis

This report provides a graph of several SQL Server lock performance counters for the specified server during the specified time period. Performance counter data is averaged by the hour.

SQL Server Log Performance Analysis

This report provides a graph of several SQL Server transaction log performance counters for the specified server during the specified time period, where the database instance is not Total, master, msdb, model, Northwind, or pubs. Performance counter data is averaged by the hour. This report provides one graph for each database instance.

SQL Server Logreader Replication Performance Analysis

This report provides a graph of several SQL Server logreader replication performance counters for the specified server during the specified time period. Performance counter data is averaged by the hour.

SQL Server Memory Performance Analysis

This report provides a graph of several SQL Server memory performance counters for the specified server during the specified time period. Performance counter data is averaged by the hour.

The following counters are plotted on a primary axis:

  • Total Server Memory (KB) (Scale = KB)

  • Buffer Cache Hit Ratio (Scale = 1)

  • Free Buffers (Scale = 1/10)

  • Lazy Writes/sec (Scale = 1/10)

  • Readahead Pages/sec (Scale = 1/10)

The following counters are plotted on a secondary axis:

  • Process-Page Faults/sec (Scale = 1/10)

  • Process-Private Bytes (Scale = MB)

SQL Server Merge Replication Performance Analysis

This report provides a graph of several SQL Server merge replication performance counters for the specified server during the specified time period. Performance counter data is averaged by the hour.

SQL Server Overall Replication Performance Analysis

This report provides a graph of several SQL Server overall replication performance counters for the specified server during the specified time period. Performance counter data is averaged by the hour.

SQL Server Snapshot Replication Performance Analysis

This report provides a graph of several SQL Server snapshot replication counters for the specified server during the specified time period. Performance counter data is averaged by the hour.

Computer Groups

The following computer groups are included in the SQL Server Management Pack Refresh:

  • MSSQL Server 2000

  • MSSQL Server 2000 Client Side Monitoring

  • Microsoft SQL Server 7.x

  • Microsoft SQL Server 7.x Client Side Monitoring

Scripts

The following scripts are included in the SQL Server Management Pack Refresh:

  • SQL Server 2000 Base Performance Collection (15 minutes)

  • SQL Server 2000 Long Running Agent Jobs

  • SQL Server 2000 Replication Performance Collection (15 minutes)

  • SQL Server 2000 Report Performance Collection (15 minutes)

  • SQL Server 2000 WMI Performance Object Check

  • SQL Server 7.0 Instance Process ID

  • SQL Server CPU Overload

  • SQL Server Database Health Monitoring

  • SQL Server Named Instance Detection

  • SQL Server Remote Connectivity

  • SQL Server Service Monitoring

  • SQL Server Service Pack Compliance

  • SQL Server Space Analysis

Default Public Views

This section lists the public views that are included in the SQL Server Management Pack Refresh.

SQL Server 2000

Microsoft SQL Server 2000

  • All alerts generated in the last 2 hours

  • All alerts generated in the last 24 hours

  • SQL Server 2000 computer groups

  • SQL Server 2000 servers

  • Unresolved alerts

Microsoft SQL Server 2000\Server Resource Utilization

  • CPU Usage\% CPU - MOM OnePoint Process

  • CPU Usage\% CPU - SQLAGENT Process

  • CPU Usage\% CPU - SQLMANGR Process

  • CPU Usage\% CPU - SQLSERVR Process

  • CPU Usage\Context Switches / second

  • CPU Usage\Processor Queue Length

  • CPU Usage\Total % CPU Usage

  • Disk Capacity\% Free Space

  • Disk Capacity\Free Megabytes

  • Disk Performance\Average Disk Queue Length

  • Disk Performance\Current Disk Queue Length

  • Disk Performance\Disk Read and Write Latency

  • Disk Performance\Disk Reads and Writes / second

  • Memory Usage\Memory: Page Reads/sec

  • Memory Usage\Memory: Page Writes/sec

  • Memory Usage\Memory: Pages / second

  • Memory Usage\Memory: Pool Nonpaged Bytes

  • Memory Usage\Memory: Pool Paged Bytes

  • Network Usage\Bytes Received/second

  • Network Usage\Bytes Sent/second

  • Network Usage\Bytes Total/second

Microsoft SQL Server 2000\ SQL Server Health Monitoring

  • SQL Agent\Failed SQL Agent Jobs

  • SQL Server Backups\Failed SQL Backups

  • SQL Server Replication\Distributor\Delivered Commands/second

  • SQL Server Replication\Distributor\Delivered Transaction/second

  • SQL Server Replication\Log Reader\Delivered Commands/second

  • SQL Server Replication\Log Reader\Delivered Transactions/second

  • SQL Server Replication\Merge\Conflicts/second

  • SQL Server Replication\Merge\Downloaded Changes/second

  • SQL Server Replication\Merge\Uploaded Changes/second

  • SQL Server Replication\Replication Agents Running

  • SQL Server Replication\Snapshot\Delivered Commands/second

  • SQL Server Replication\Snapshot\Delivered Transactions/second

  • SQL Server Replication\SQL Server 2000 Replication Servers

Microsoft SQL Server 2000\ SQL Server Utilization and Performance

  • Active Transactions

  • Average Wait Time/ms

  • DBCC Logical Scan Bytes/second

  • Full Scans/second

  • Lock Blocks

  • Lock Timeouts/second

  • Log Cache Reads/second

  • Log Truncations

  • Logins/second

  • Memory Grants Pending

  • Mixed page allocations/second

  • Page Writes/second

  • Pages Allocated/second

SQL Server 7.0

Microsoft SQL Server 7.0

  • All alerts generated in the last 2 hours

  • All alerts generated in the last 24 hours

  • SQL Server 7.x Servers

  • Unresolved alerts

Microsoft SQL Server 7.0\ SQL Server Utilization and Performance

  • Active Transactions

  • Average Wait Time/ms

  • Full Scans/second

  • Lock Blocks

  • Lock Timeouts/second

  • Log Cache Reads/second

  • Log Truncations

  • Logins/second

  • Memory Grants Pending

  • Mixed page allocations/second

  • Page Writes/second

  • Pages Allocated/second

Processing Rule Groups

The following processing rule group hierarchy is used by the SQL Server Management Pack Refresh. If a specific monitoring scenario is not being used, the best practice is to disable the processing rule group associated with the monitoring scenario.

SQL Server 2000

Microsoft SQL Server

SQL Server 2000

Client Side Monitoring

Server Performance Collection

Replication Performance Collection

Server Resource Utilization

CPU Performance Collection

Disk Performance Collection

Disk Space Usage

Memory Performance Collection

Network Performance Collection

Server Performance Thresholds

SQL Server 2000 Event Collection

MSDTC

OLE DB

SQL Server Active Directory® Helper Service

SQL Server Agent

SQL Server Backup

SQL Server Databases

SQL Server DBCC

SQL Server Full Text Search

SQL Server General

SQL Server Log Shipping

SQL Server Mail

SQL Server Performance

SQL Server Query Processor

SQL Server Replication

SQL Server Security

SQL Server Security Audit Collection

SQL Server Tables

SQL Server Web Assistant

SQL Server XML

SQL Server 2000 Health and Availability Monitoring

SQL Server 2000 Report Collection Rules

SQL Server 7.x

Microsoft SQL Server

SQL Server 7.0

Client Side Monitoring

Server Performance Collection

Server Performance Thresholds

SQL Server 7.0 Event Collection

SQL Server 7.0 Agent

SQL Server 7.0 Events

SQL Server 7.0 Report Collection Rules

SQL Server 7.0 Profiler

SQL Server 7.0 Security Events

SQL Server 7.0 Health and Availability Monitoring

SQL Server 7.0 Report Collection Rules

State Monitoring and Service Discovery

Acknowledgements

Program Managers: Tom Keane, Steve Wilson

Writer: Brenda Carter

Technical Reviewer: James Hedrick

Editor: Scott Kendall