Oracle® Enterprise Manager

System Monitoring Plug-in Metric Reference Manual for Non Oracle Database Management

12c Release 5 (12.1.0.5)

E51915-05

June 2015

Oracle Enterprise Manager System Monitoring Plug-in Metric Reference Manual for Non Oracle Database Management 12c Release 5 (12.1.0.5)

E51915-05

Copyright © 2001, 2015, Oracle and/or its affiliates. All rights reserved.

This software and related documentation are provided under a license agreement containing restrictions on use and disclosure and are protected by intellectual property laws. Except as expressly permitted in your license agreement or allowed by law, you may not use, copy, reproduce, translate, broadcast, modify, license, transmit, distribute, exhibit, perform, publish, or display any part, in any form, or by any means. Reverse engineering, disassembly, or decompilation of this software, unless required by law for interoperability, is prohibited.

The information contained herein is subject to change without notice and is not warranted to be error-free. If you find any errors, please report them to us in writing.

If this is software or related documentation that is delivered to the U.S. Government or anyone licensing it on behalf of the U.S. Government, then the following notice is applicable:

U.S. GOVERNMENT END USERS: Oracle programs, including any operating system, integrated software, any programs installed on the hardware, and/or documentation, delivered to U.S. Government end users are "commercial computer software" pursuant to the applicable Federal Acquisition Regulation and agency-specific supplemental regulations. As such, use, duplication, disclosure, modification, and adaptation of the programs, including any operating system, integrated software, any programs installed on the hardware, and/or documentation, shall be subject to license terms and license restrictions applicable to the programs. No other rights are granted to the U.S. Government.

This software or hardware is developed for general use in a variety of information management applications. It is not developed or intended for use in any inherently dangerous applications, including applications that may create a risk of personal injury. If you use this software or hardware in dangerous applications, then you shall be responsible to take all appropriate fail-safe, backup, redundancy, and other measures to ensure its safe use. Oracle Corporation and its affiliates disclaim any liability for any damages caused by use of this software or hardware in dangerous applications.

Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.

Intel and Intel Xeon are trademarks or registered trademarks of Intel Corporation. All SPARC trademarks are used under license and are trademarks or registered trademarks of SPARC International, Inc. AMD, Opteron, the AMD logo, and the AMD Opteron logo are trademarks or registered trademarks of Advanced Micro Devices. UNIX is a registered trademark of The Open Group.

This software or hardware and documentation may provide access to or information about content, products, and services from third parties. Oracle Corporation and its affiliates are not responsible for and expressly disclaim all warranties of any kind with respect to third-party content, products, and services unless otherwise set forth in an applicable agreement between you and Oracle. Oracle Corporation and its affiliates will not be responsible for any loss, costs, or damages incurred due to your access to or use of third-party content, products, or services, except as set forth in an applicable agreement between you and Oracle.

Contents

Preface

- Audience

- Documentation Accessibility

- Related Documents

- Conventions

What's Changed in this Guide?

How to Use This Manual

- Structure of the Metric Reference Manual

- Background Information on Metrics, Thresholds, and Alerts

1 Microsoft SQL Server Metrics

- 1.1 Viewing Metrics

- 1.2 Access Methods

- 1.2.1 For Microsoft SQL Server 2005/2008/2012

- 1.3 Active SQL Cluster Node

- 1.4 Agent Status

- 1.5 Associated Services

- 1.6 Buffer Manager

- 1.6.1 For Microsoft SQL Server 2005/2008/2012

- 1.7 Cache Manager

- 1.7.1 For Microsoft SQL Server 2000/2005/2008

- 1.8 Cluster Active Group and Node

- 1.8.1 For Microsoft SQL Server 2005

- 1.9 Cluster Active Resource and Node Metrics

- 1.9.1 For Microsoft SQL Server 2005

- 1.10 Cluster Name and Network

- 1.10.1 For Microsoft SQL Server 2005

- 1.11 Cluster Quorom Resource and Cluster Name

- 1.11.1 For Microsoft SQL Server 2005

- 1.12 Cluster Resources

- 1.12.1 For Microsoft SQL Server 2005

- 1.13 Cluster Resource and Group

- 1.13.1 For Microsoft SQL Server 2005

- 1.14 Cluster Resource and Type

- 1.14.1 For Microsoft SQL Server 2005

- 1.15 Cluster Resource Group and Cluster Name Metrics

- 1.15.1 For Microsoft SQL Server 2005

- 1.16 Cluster Resource and Owner Node Name

- 1.16.1 For Microsoft SQL Server 2005

- 1.17 Cluster Resource and Cluster Name

- 1.17.1 For Microsoft SQL Server 2005

- 1.18 Cluster Resource Group and Preferred Node

- 1.18.1 For Microsoft SQL Server 2005

- 1.19 Configuration Metrics

- 1.19.1 SQL Server Configuration Metrics

- 1.19.2 Registry Setting Configuration Metrics

- 1.19.3 Database Setting Configuration Metrics

- 1.19.4 HADR Availability Group Databases Metrics

- 1.20 Connection Statistics

- 1.21 Database

- 1.22 Database Backup

- 1.23 Database IOPS Metrics

- 1.24 Database Job

- 1.25 Database Lock

- 1.26 Database Parameter

- 1.27 Database Performance

- 1.27.1 For Microsoft SQL Server 2005/2008/2012

- 1.28 Database Status

- 1.29 Detailed Response

- 1.30 Event Log Entry

- 1.31 General Statistics

- 1.31.1 For Microsoft SQL Server 2005/2008/2012

- 1.32 HADR Availability Groups

- 1.33 HADR Availability Group Replicas

- 1.34 HADR Cluster Members

- 1.35 Index/Table Fragmentation Metrics

- 1.36 Index Usage

- 1.37 Last Database Backup

- 1.38 Latches

- 1.39 Latch Wait Time

- 1.40 Login

- 1.41 Memory Manager

- 1.41.1 For Microsoft SQL Server 2005/2008/2012

- 1.42 Memory Statistics

- 1.43 Memory Usage

- 1.44 Mirroring Monitoring

- 1.45 Mirroring Statistics

- 1.46 Missing Indexes

- 1.47 MSSQL Database File

- 1.48 MSSQL File Group

- 1.49 MSSQL Transaction Log

- 1.50 Processor

- 1.51 Response

- 1.52 Server Statistics Metrics

- 1.53 Space Usage

- 1.54 SQL Cluster Nodes Information

- 1.55 SQL Cluster Number of Nodes

- 1.56 SQL Instance Statistics

- 1.57 SQL Server Process Statistics

- 1.58 SQL Server Backups

- 1.59 SQL Server Locks

- 1.59.1 For Microsoft SQL Server 2005/2008/2012

- 1.60 SQL Server Process

- 1.61 SQL Server Role

- 1.62 SQL Statistics

- 1.62.1 For Microsoft SQL Server 2005/2008/2012

- 1.63 Top Queries by CPU Time Metrics

- 1.64 Top Queries by Execution Count

- 1.65 Top Queries by Total Blocked Time

- 1.66 Top Sessions by CPU Utilization

- 1.67 Top Sessions by Memory Utilization

- 1.68 User

- 1.69 Windows Cluster Name

- 1.69.1 For Microsoft SQL Server 2005

- 1.70 Windows Cluster Nodes

- 1.70.1 For Microsoft SQL Server 2005

2 IBM DB2 Database Metrics

- 2.1 Viewing Metrics

- 2.2 Connections

- 2.2.1 Agents Connection

- 2.2.2 Application Connection

- 2.3 Database Manager Configuration Information

- 2.3.1 Capacity

- 2.3.2 Connection

- 2.3.3 Database Instance

- 2.3.4 Logging and Recovery

- 2.3.5 Partitioned Database Environment

- 2.4 Health Indicators/Alarms

- 2.4.1 Container Health Indicator

- 2.4.2 Container Health

- 2.4.3 Database Collection Health Indicator

- 2.4.4 Database Health

- 2.4.5 Database Health Indicator

- 2.4.6 Database Manager Health

- 2.4.7 Database Manager Health Indicator

- 2.4.8 Tablespaces Health

- 2.4.9 Tablespace Health Indicator

- 2.5 Monitoring Information

- 2.5.1 Agent Monitoring

- 2.5.2 Database Monitoring

- 2.5.3 Database Backup Monitoring

- 2.5.4 General Monitoring

- 2.6 Performance

- 2.6.1 IO Agents Performance

- 2.6.2 Database Performance

- 2.6.3 Bufferpool Performance

- 2.6.4 Cache Performance

- 2.6.5 Log IO Performance

- 2.6.6 Memory Manager Performance

- 2.6.7 SortHeap Performance

- 2.6.8 Non-Buffered I/O Performance

- 2.7 Response

- 2.8 Detailed Response

- 2.9 Storage Information

- 2.9.1 Data Files Storage

- 2.9.2 Log Storage

- 2.9.3 Tablespace Storage

- 2.10 System Configuration Information

- 2.10.1 Database System Information

- 2.10.2 Instance Information

- 2.10.3 Product Information

- 2.10.4 Partition Information

- 2.10.5 Registry Settings

- 2.11 DB2 Diag Log File Monitoring

- 2.12 HADR Status Metrics

- 2.13 Top Statements by CPU Time Metrics

- 2.14 Top Statements by Execution Count Metrics

- 2.15 Lock Waits Metrics

- 2.16 Lock Waits by Table Metrics

- 2.17 Lock Waits by Blocked Application Metrics

- 2.18 Lock Waits by Blocking Application Metrics

3 Sybase Adaptive Server Enterprise Database Metrics

- 3.1 About Sybase Adaptive Server Enterprise Database Metrics

- 3.2 Viewing Metrics

- 3.3 Adaptive Server Engines Statistics

- 3.4 Adaptive Server State

- 3.5 Adaptive Server Statistics

- 3.6 Cache Pools Statistics

- 3.7 Cached Procedures Statistics

- 3.8 Connection Statistics

- 3.9 Currently Executing Queries

- 3.10 Currently Executing SQL Text Information

- 3.11 Data Caches Statistics

- 3.12 Database Indexes

- 3.13 Database Logins

- 3.14 Database Login Roles

- 3.15 Database Objects

- 3.16 Database Segments

- 3.17 Database Thresholds

- 3.18 Database Transactions

- 3.19 Databases Instances

- 3.20 Deadlock Statistics

- 3.21 Device Data and IO Log Statistics

- 3.22 Devices Statistics

- 3.23 General Statistics

- 3.24 Locks Information

- 3.25 Most Recent Error Messages

- 3.26 Most Recently Executed Statement Statistics

- 3.27 Network I/O Statistics

- 3.28 Open Databases Statistics

- 3.29 Open Objects Statistics

- 3.30 Procedure Cache Statistics

- 3.31 Process Network IO Activity

- 3.32 Process Objects Information

- 3.33 Process Statistics

- 3.34 Process Tracking Details

- 3.35 Processes Activity Statistics

- 3.36 Response

- 3.37 Recently Executed SQL Text

- 3.38 Running Procedures Statistics

- 3.39 Segment Usages

- 3.40 Server-Wide Worker Threads Statistics

- 3.41 Top SQL Statements by CPU Time

- 3.42 Top SQL Statements by Wait Time

- 3.43 Top SQL Statements by Memory Usage

- 3.44 Top Ten Big Cached Objects

- 3.45 Top Ten Frequently Accessed Cached Objects

- 3.46 Transaction Logs

- 3.47 Wait Class Event Information

- 3.48 Wait Events Information

- 3.49 Waiting Events Statistics

- 3.50 Waiting Process Statistics

- 3.51 Configuration Metrics

- 3.51.1 Charsets Information

- 3.51.2 Configuration Parameters

- 3.51.3 Database Instances

- 3.51.4 Installed Scripts

- 3.51.5 Sybase ASE Version

- 3.51.6 System Databases

- 3.51.7 System Listeners

4 Microsoft SQL Server Reports

5 IBM DB2 Database Reports

6 Sybase Adaptive Server Enterprise Database Reports

Preface

This manual is a compilation of the plug-ins metrics provided in Oracle Enterprise Manager for database management.

Audience

This document is intended for Oracle Enterprise Manager users interested in plug-ins metrics for database management.

Documentation Accessibility

For information about Oracle's commitment to accessibility, visit the Oracle Accessibility Program website at http://www.oracle.com/pls/topic/lookup?ctx=acc&id=docacc.

Access to Oracle Support

Oracle customers that have purchased support have access to electronic support through My Oracle Support. For information, visit http://www.oracle.com/pls/topic/lookup?ctx=acc&id=info or visit http://www.oracle.com/pls/topic/lookup?ctx=acc&id=trs if you are hearing impaired.

Related Documents

For more information, see the following documents in the Oracle Enterprise Manager 12c documentation set:

- Oracle Enterprise Manager System Monitoring Plug-in Installation Guide for Sybase Adaptive Server Enterprise (ASE)

- Oracle Enterprise Manager System Monitoring Plug-in Installation Guide for IBM DB2 Database

- Oracle Enterprise Manager System Monitoring Plug-in Installation Guide for Microsoft SQL Server

- Enterprise Manager Cloud Control Administrator's Guide

Conventions

The following text conventions are used in this document:

Convention

Meaning

boldface

Boldface type indicates graphical user interface elements associated with an action, or terms defined in text or the glossary.

italic

Italic type indicates book titles, emphasis, or placeholder variables for which you supply particular values.

monospace

Monospace type indicates commands within a paragraph, URLs, code in examples, text that appears on the screen, or text that you enter.

What's Changed in this Guide?

In addition to updating books for an incremental software release or a patch set release, Oracle revises its books regularly to incorporate bug fixes and value-added feedback from customers, product managers, support teams, and other key stakeholders. Every time a book is revised, the revision number of the book is increased by one and then published.

This chapter lists the changes incorporated in the latest revision (E51915-05) of this book.

Since the last revision, the following changes have been made:

- Added: Added metrics to the following tables:

- Table 1-2, "Access Method Counter Name Metrics"

- Table 1-4, "Agent Status Metrics"

- Table 1-26, "Database Metrics"

- Table 1-36, "Detailed Response Metrics"

- Table 1-43, "Index/Table Fragmentation Metrics"

- Table 1-44, "Index Usage Metrics"

- Table 1-52, "Memory Usage Metrics"

- Table 1-53, "Mirroring Monitoring Metrics"

- Table 1-54, "Mirroring Statistics Metrics"

- Table 1-55, "Missing Indexes Metrics"

- Table 1-56, "MSSQL Database File Metrics"

- Table 1-65, "SQL Instance Statistics Metrics"

- Table 1-67, "SQL Server Backups Metrics"

- Table 1-66, "SQL Server Process Statistics Metrics"

- Table 2-17, "Agent Monitoring Metrics"

- Table 2-19, "Database Backup Monitoring Metrics"

- Table 2-22, "Database Performance Metrics"

- Table 2-30, "Detailed Response Metrics"

- Modified: Modified metrics in the following tables:

- Table 1-4, "Agent Status Metrics"

- Table 1-58, "MSSQL Transaction Log Metrics"

- Table 1-62, "Space Usage Metrics"

- Table 2-22, "Database Performance Metrics"

- Table 2-32, "Log Storage Metrics"

- Removed: Removed metrics from the following tables:

- Table 1-37, "Event Log Entry Metrics"

- Table 1-58, "MSSQL Transaction Log Metrics"

- Table 1-62, "Space Usage Metrics"

- Table 3-40, "Process Activity Statistics Metrics"

How to Use This Manual

The System Monitoring Plug-in Metric Reference Manual for Non Oracle Database Management lists all the plug-ins metrics for database management that Enterprise Manager monitors.

This preface describes:

- Structure of the Metric Reference Manual

- Background Information on Metrics, Thresholds, and Alerts

Structure of the Metric Reference Manual

This manual contains chapters for the Microsoft SQL Server, IBM DB2 Database and Sybase Adaptive Server Enterprise Database. The metrics in these chapters appear in alphabetical order according to category.

Metric Information

The information for each metric comprises a description and user action if available:

- Description
Provides an explanation following the metric name. This text defines the metric and, when available, provides additional information pertinent to the metric.

- User Action
Suggests how to solve the problem causing the alert.

Definitions of Columns in Metric Summary Tables

The Metric Summary table in Enterprise Manager Cloud Control is part of the overall metric information. The following table provides descriptions of columns in the Enterprise Manager Metric Summary table.

Column Header

Column Definition

Target Version

Version of the target, for example, 9.0.2.x and 10.1.0.x. The x at the end of a version (for example, 9.0.2.x) represents the subsequent patchsets associated with that release.

Collection Schedule

The rate at which the Management Agent collects data. The collection frequency for a metric comes from the Enterprise Manager default collection file for that target type.

Default Warning Threshold

Value that indicates whether a warning alert should be initiated. If the evaluation of the warning threshold value returns a result of TRUE for the specified number of consecutive occurrences defined for the metric, an alert triggers at the warning severity level.

Default Critical Threshold

Value that indicates whether a critical alert should be initiated. If the evaluation of the critical threshold value returns a result of TRUE for the specified number of consecutive occurrences defined for the metric, an alert triggers at the critical severity level.

Alert Text

Message indicating why the alert was generated. Words that display between percent signs (%) denote variables. For example, Disk Utilization for %keyValue% is %value%% could translate to Disk Utilization for d0 is 80%.

Abbreviations and Acronyms

To reduce the page count in this document, the following abbreviations and acronyms are used:

Abbreviation/Acronym

Name

Agent

Oracle Management Agent

Database

Oracle Database

OMS

Oracle Management Service

Repository

Oracle Management Repository

Background Information on Metrics, Thresholds, and Alerts

A metric is a unit of measurement used to determine the health of a target. It is through the use of metrics and associated thresholds that Enterprise Manager sends out alerts notifying you of problems with the target.

Thresholds are boundary values against which monitored metric values are compared. For example, for each disk device associated with the Disk Utilization (%) metric, you can define a different warning and critical threshold. Some of the thresholds are predefined by Oracle; others are not.

After a threshold is reached, an alert is generated. An alert is an indicator signifying that a particular condition has been encountered and is triggered when one of the following conditions is true:

- A threshold is reached.

- An alert has been cleared.

- The availability of a monitored service changes. For example, the availability of an application server changes from up to down.

- A specific condition occurs. For example, an alert is triggered whenever an error message is written to a database alert log file.

Alerts are detected through a polling-based mechanism by checking for the monitored condition from a separate process at regular, predefined intervals.

See Also:
See the Enterprise Manager Cloud Control Administrator's Guide for additional information about metrics, thresholds, and alerts

Editing

Out of the box, Enterprise Manager comes with thresholds for critical metrics. Warning and critical thresholds are used to generate an alert, letting you know of impending problems so that you can address them in a timely manner.

To better suit the monitoring needs of your organization, you can edit the thresholds provided by Enterprise Manager and define new thresholds. When defining thresholds, the key is to choose acceptable values to avoid unnecessary alerts, while still being notified of issues in a timely manner.

You can establish thresholds that will provide pertinent information in a timely manner by defining metric baselines that reflect how your system runs for a normal period of time.

The metrics listed on the Edit Thresholds page are either default metrics provided by Oracle or metrics whose thresholds you can change.

Accessing Metrics Using the Cloud Control Console

To access metrics in the Cloud Control Console, use the All Metrics page associated with a particular target by doing the following:

1. From the Cloud Control Console, choose the target.

2. On the target's home page, click All Metrics in the Related Links section.

1 Microsoft SQL Server Metrics

This chapter provides descriptions for all Microsoft SQL Server metric categories, and the tables list and describe associated metrics for each category. Shaded rows represent key columns for a particular category.

- Viewing Metrics

- Access Methods

- Active SQL Cluster Node

- Agent Status

- Associated Services

- Buffer Manager

- Cache Manager

- Cluster Active Group and Node

- Cluster Active Resource and Node Metrics

- Cluster Name and Network

- Cluster Quorom Resource and Cluster Name

- Cluster Resources

- Cluster Resource and Group

- Cluster Resource and Type

- Cluster Resource Group and Cluster Name Metrics

- Cluster Resource and Owner Node Name

- Cluster Resource and Cluster Name

- Cluster Resource Group and Preferred Node

- Configuration Metrics

- Connection Statistics

- Database

- Database Backup

- Database IOPS Metrics

- Database Job

- Database Lock

- Database Parameter

- Database Performance

- Database Status

- Detailed Response

- Event Log Entry

- General Statistics

- HADR Availability Groups

- HADR Availability Group Replicas

- HADR Cluster Members

- Index/Table Fragmentation Metrics

- Index Usage

- Last Database Backup

- Latches

- Latch Wait Time

- Login

- Memory Manager

- Memory Statistics

- Memory Usage

- Mirroring Monitoring

- Mirroring Statistics

- Missing Indexes

- MSSQL Database File

- MSSQL File Group

- MSSQL Transaction Log

- Processor

- Response

- Server Statistics Metrics

- Space Usage

- SQL Cluster Nodes Information

- SQL Cluster Number of Nodes

- SQL Instance Statistics

- SQL Server Process Statistics

- SQL Server Backups

- SQL Server Locks

- SQL Server Process

- SQL Server Role

- SQL Statistics

- Top Queries by CPU Time Metrics

- Top Queries by Execution Count

- Top Queries by Total Blocked Time

- Top Sessions by CPU Utilization

- Top Sessions by Memory Utilization

- User

- Windows Cluster Name

- Windows Cluster Nodes

1.1 Viewing Metrics

To view configuration metrics within the plug-in, navigate to the Last Collected configuration metrics page by selecting Configuration, then Last Collected from Microsoft SQL Server.

Figure 1-1 Last Collected Configuration Metrics

To view the performance and status metrics within the plug-in, navigate to the plug-in's All Metrics page by selected Monitoring, then All Metrics from Microsoft SQL Server.

Figure 1-2 All Metrics Page

1.2 Access Methods

The metrics in this category search through and measure the allocation of Microsoft SQL Server database objects, such as the number of index searches or number of pages that are allocated to indexes and data.

1.2.1 For Microsoft SQL Server 2005/2008/2012

Default Collection Interval — Every 30 minutes

Table 1-1 Access Methods Metrics

Metric

Description

Access Method Counter Name (key column)

Performance metric name. See Table 1-2.

The Access Method Counter Name key column contains several metrics. Table 1-2 provides a list of these metrics and a description for each.

Table 1-2 Access Method Counter Name Metrics

Metric

Description

Extents Deallocations/sec

Number of extents deallocated per second from database objects used for storing index or data records.

Extents Allocated/sec

Number of extents allocated per second to database objects used for storing index or data records.

Forwarded Records/sec

Number of records per second fetched through forwarded record pointers.

FreeSpace Page Fetches/sec

Number of pages returned per second by free space scans used to satisfy requests to insert record fragments.

FreeSpace Scans/sec

Number of scans per second that were initiated to search for free space in which to insert a new record fragment.

Full Scans/sec

Number of unrestricted full scans per second, which can be either base-table or full-index scans.

Index Searches/sec

Number of index searches per second. These are used to start range scans and single index record fetches and to reposition an index.

Mixed Page Allocations/sec

Number of pages allocated per second from mixed extents. These are used for storing the first eight pages that are allocated to an index or table.

Page Deallocations/sec

Number of pages deallocated per second from database objects used for storing index or data records.

Page Splits/sec

Number of page splits per second that occur because of overflowing index pages.

Pages Allocated/sec

Number of pages allocated per second to database objects used for storing index or data records.

Probe Scans/sec

Number of probe scans per second. These are used to directly find rows in an index or base table.

Range Scans/sec

Number of qualified range scans through indexes per second.

Scan Point Revalidations/sec

Number of times per second that the scan point had to be revalidated to continue the scan.

Skipped Ghosted Records/sec

Number of ghosted records per second skipped during scans.

Table Lock Escalations/sec

Number of times locks on a table were escalated.

Workfiles Created/sec

Number of workfiles created per second.

Worktables Created/sec

Number of work tables created per second.

Worktables from Cache Base

Denominator ("base") of a fraction that the performance counter Worktables from Cache ratio represents.

Worktables from Cache Ratio

Percentage of work tables created where the initial pages were immediately available in the work table cache.

1.3 Active SQL Cluster Node

The metrics in this category provide details about the active SQL cluster node.

Evaluation and Collection Frequency — Every 30 Minutes

Table 1-3 Active SQL Cluster Node Metrics

Metric

Description

Node Name

Name of the cluster node.

1.4 Agent Status

The metrics in this category provide information regarding the current status of the Agent.

Default Collection Interval — Every 5 minutes

Table 1-4 Agent Status Metrics

Metric

Description and User Action

Process ID

Process ID of the Sqlserver Agent process.

Server name

Name of the Sqlserver instance.

Service Path

Path of the Sqlserver process.

Sqlserver Agent Status

Status of the Sqlserver Agent process.

When the status is not running, the Microsoft SQL server Agent must be started.

Sqlserver Agent Start

The start mode of the Sqlserver Agent.

1.5 Associated Services

The metrics in this category provide information on Microsoft Windows services that are associated with Microsoft SQL Server.

Evaluation and Collection Frequency — Every 15 Minutes

Table 1-5 Associated Services Metrics

Metric

Description

Process ID

Process ID of the service.

Service Name

Name of the associated service.

Service State

State of the service.

Service Status

Status of the service.

Start Mode

Start mode of the service.

Start Name

Party responsible for starting the service.

System Name

Name of the system on which the service is running.

1.6 Buffer Manager

The Buffer Manager object provides counters to monitor how Microsoft SQL Server uses:

- Memory to store data pages, internal data structures, and the procedure cache.

- Counters to monitor the physical I/O as Microsoft SQL Server reads database pages from, and writes database pages to, the disk.

1.6.1 For Microsoft SQL Server 2005/2008/2012

Default Collection Interval — Every 15 minutes

Table 1-6 Buffer Manager Metrics

Metric

Description

Buffer Manager Counter Name (key column)

Performance metric name. See Table 1-7.

The Buffer Manager Counter Name key column contains several metrics. Table 1-7 provides a list of these metrics and a description for each.

Table 1-7 Buffer Manager Counter Name Metrics

Metric

Description

Buffer Cache Hit Ratio

Percentage of pages found in the buffer cache without having to read from disk. The ratio is the total number of cache hits divided by the total number of cache lookups since Microsoft SQL Server was started. After a long period of time, the ratio does not change very much. Because reading from the cache is much less expensive than reading from disk, this ratio should be high. Generally, you can increase the buffer cache hit ratio by increasing the amount of memory available to Microsoft SQL Server.

Buffer Cache Hit Ratio Base

Denominator ("base") of a fraction that the performance counter Buffer Cache Hit Ratio represents.

Checkpoint Pages/sec

Number of pages flushed to disk per second by a checkpoint or other operations that cause all dirty pages to be flushed to disk.

Database Pages

Total number of database pages.

Free List Stalls/sec

Number of requests that had to wait for a free page.

Free Pages

Total number of pages on all free lists.

Lazy Writes/sec

Number of buffers written per second by the buffer manager's lazy writer. The lazy writer is a system process that flushes out batches of dirty, aged buffers (buffers that contain changes that must be written back to disk before the buffer can be reused for a different page) and make them available to user processes. The lazy writer eliminates the need to perform frequent checkpoints in order to create available buffers.

Page Lookups/sec

Number of requests to find a page in the buffer pool.

Page Reads/sec

Number of physical database page reads issued per second. This statistic displays the total number of physical page reads across all databases. Because physical I/O is expensive, you may be able to minimize the cost by using a larger data cache, intelligent indexes, more efficient queries, or by changing the database design.

Page Writes/sec

Number of database page writes issued per second. Page writes are generally expensive. Reducing page-write activity is important for optimal tuning. One way to do this is to ensure that you do not run out of free buffers in the free buffer pool. If you do, page writes will occur while waiting for an unused cache buffer to flush.

Procedure Cache Pages

Number of pages used to store compiled queries.

Readahead Pages/sec

Number of pages read in anticipation of use.

Reserved Pages

Number of buffer pool reserved pages.

Stolen Pages

Number of pages used for miscellaneous server purposes (including procedure cache).

Target Pages

Ideal number of pages in the buffer pool.

Total Pages

Number of pages in the buffer pool (includes database, free, and stolen pages).

1.7 Cache Manager

The Cache Manager object provides counters to monitor how Microsoft SQL Server uses memory to store objects such as stored procedures, ad hoc and prepared Transact-SQL statements, and triggers. Multiple instances of the Cache Manager object can be monitored at the same time, with each instance representing a different type of plan to monitor.

1.7.1 For Microsoft SQL Server 2000/2005/2008

Default Collection Interval — Every 15 minutes

Table 1-8 Cache Manager Metrics

Metric

Description

Cache Manager Counter Name (key column)

Performance metric name. See Table 1-9.

Cache Manager Instance Name (key column)

Instance for the Cache Manager counter name.

The Cache Manager Counter Name key column contains several metrics. Table 1-9 provides a list of these metrics and a description for each.

Table 1-9 Cache Manager Counter Name Metrics

Metric

Description

Cache Hit Ratio

Percentage of pages found in the cache without having to read from disk. The ratio is the total number of cache hits divided by the total number of cache lookups since Microsoft SQL Server was started. After a long period of time, the ratio does not change very much. Because reading from the cache is less expensive than reading from disk, this ratio should be high. Generally, you can increase the cache hit ratio by increasing the amount of memory available to Microsoft SQL Server.

Cache Hit Ratio Base

Denominator ("base") of a fraction that the performance counter Cache Hit Ratio represents.

Cache Pages

Number of pages used by objects in the cache. After a long period of time, the count does not change very much.

Cache Object Counts

Number of objects found in the cache. After a long period of time, the count does not change very much.

Cache Use Counts/sec

Number of times per second that each type of object in the cache has been used. The higher this value is, the better. After a long period of time, the count does not change very much.

1.8 Cluster Active Group and Node

The metrics in this category provide details about the cluster active group and the node.

1.8.1 For Microsoft SQL Server 2005

Default Collection Interval — Every 30 minutes

Table 1-10 Cluster Active Group and Node Metrics

Metric

Description

Active Group (key column)

Name of the active group.

Node (key column)

Name of the node.

Cluster Server Name

Name of the cluster server.

1.9 Cluster Active Resource and Node Metrics

The metrics in this category provide details about the cluster active resource and node.

1.9.1 For Microsoft SQL Server 2005

Default Collection Interval — Every 30 minutes

Table 1-11 Cluster Active Resource and Node Metrics

Metric

Description

Active Resource Name (key column)

Name of the active resource.

Cluster Node (key column)

Name of the cluster node.

Cluster Server Name

Name of the cluster server.

1.10 Cluster Name and Network

The metrics in this category provide details about the cluster name and network.

1.10.1 For Microsoft SQL Server 2005

Default Collection Interval — Every 30 minutes

Table 1-12 Cluster Name and Network Metrics

Metric

Description

Cluster Name (key column)

Name of the Windows cluster.

Network Used (key column)

Name of the network used.

Cluster Server Name

Name of the cluster server.

1.11 Cluster Quorom Resource and Cluster Name

The metrics in this category provide details about the cluster quorom resource and cluster name.

1.11.1 For Microsoft SQL Server 2005

Default Collection Interval — Every 30 minutes

Table 1-13 Cluster Quorom Resource and Cluster Name Metrics

Metric

Description

Quorom Resource Name (key column)

Name of the quorom resource.

Cluster Name (Key Column)

Name of the cluster.

Cluster Server Name

Name of the cluster server.

1.12 Cluster Resources

The metrics in this category provide details about cluster resource group.

1.12.1 For Microsoft SQL Server 2005

Default Collection Interval — Every 30 minutes

Table 1-14 Cluster Resource and Type Metrics

Metric

Description

Node Name

Name of the node.

Cluster Server Name

Name of the cluster server.

Node Status

Status of the node.

1.13 Cluster Resource and Group

The metrics in this category provide details about the cluster resource and group.

1.13.1 For Microsoft SQL Server 2005

Default Collection Interval — Every 30 minutes

Table 1-15 Cluster Resource and Group Metrics

Metric

Description

Resource (key column)

Name of the cluster resource and group.

Resource Group (key column)

Name of the resource group.

Cluster Server Name

Name of the cluster server.

1.14 Cluster Resource and Type

The metrics in this category provide details about the cluster resource and type.

1.14.1 For Microsoft SQL Server 2005

Default Collection Interval — Every 30 minutes

Table 1-16 Cluster Resource and Type Metrics

Metric

Description

Resource Name (key column)

Name of the Windows cluster resource.

Resource Type (key column)

Type of the resource.

Cluster Server Name

Name of the cluster server.

1.15 Cluster Resource Group and Cluster Name Metrics

The metrics in this category provide details about the cluster resource group and cluster name.

1.15.1 For Microsoft SQL Server 2005

Default Collection Interval — Every 30 minutes

Table 1-17 Cluster Resource Group and Cluster Name Metrics

Metric

Description

Resource Group (key column)

Name of the resource group.

Cluster (key column)

Name of the cluster.

Cluster Server Name

Name of the cluster server.

1.16 Cluster Resource and Owner Node Name

The metrics in this category provide details about the cluster resource and owner node.

1.16.1 For Microsoft SQL Server 2005

Default Collection Interval — Every 30 minutes

Table 1-18 Cluster Resource and Owner Node Name Metrics

Metric

Description

Cluster Resource (key column)

Name of the cluster resource.

Owner Node (Key column)

Name of the owner node.

Cluster Server Name

Name of the cluster server.

1.17 Cluster Resource and Cluster Name

The metrics in this category provide details about the cluster resource and cluster name.

1.17.1 For Microsoft SQL Server 2005

Default Collection Interval — Every 30 minutes

Table 1-19 Cluster Resource and Cluster Name Metrics

Metric

Description

Resource Name (key column)

Name of the resource.

Cluster Name (Key column)

Name of the cluster.

Cluster Server Name

Name of the cluster server.

1.18 Cluster Resource Group and Preferred Node

The metrics in this category provide details about the cluster resource group and preferred node.

1.18.1 For Microsoft SQL Server 2005

Default Collection Interval — Every 30 minutes

Table 1-20 Cluster Resource Group and Preferred Node Metrics

Metric

Description

Resource Group (key column)

Name of the resource group.

Preferred Node (key column)

Name of the preferred node.

Cluster Server Name

Name of the cluster server.

1.19 Configuration Metrics

Configuration metrics consist of the following categories:

- SQL Server Configuration

- Registry Setting Configuration

- Database Setting Configuration

1.19.1 SQL Server Configuration Metrics

The metrics in this category represent a Microsoft SQL Server installation. The metrics contain details of the product and version of the Microsoft SQL Server instance.

- Table Name — MGMT_EMX_MSSQL_SQLSERVER

- View Name — MGMT_EMX_MSSQL_SQLSERVER_VIEW

Default Collection Interval — Every 7 Days

Table 1-21 SQL Server Configuration Metrics

Metric

Description

Server Name

Name of the SQL Server.

Clustered

Whether the server belongs to a cluster.

Package

Product installed.

- 0 — Unknown

- 1 — Office

Product

Installed product.

Version String

Installed version.

Operatingsystem Details

Operating system on which the installation is done.

Version Name

Installed version including revision.

HADR Enabled

Whether AlwaysOn high-availability disaster recovery is enabled.

Database Service Pack

Installed SQL Server service pack

Edition

Installed SQL Server edition.

1.19.2 Registry Setting Configuration Metrics

The metrics in this category contain the installation and run-time parameters of the SQL Server stored in the registry.

- Table Name — MGMT_EMX_MSSQL_REGSETTING

- View Name — MGMT_EMX_MSSQL_REGSETTING_VIEW

Default Collection Interval — Every 7 Days

Table 1-22 Registry Setting Configuration Metrics

Metric

Description

Agent Log File

Path and file name for the Agent log.

Backup Directory

Location of the backup files directory.

Case Sensitive

Comparison method for multi-byte character data is either case-sensitive or not.

Error Log Path

Operating system path and file name to be used for the SQL Server error log.

Master DB Path

The full path and file name of the operating system file containing the master database.

NT Event Logging

Whether the SQL Server uses the Windows NT application log. If TRUE, the SQL Server sends all events to the Windows NT application log and the SQL Server error log. If FALSE, the SQL Server sends events only to the SQL Server error log.

Number of Processors

Number of CPUs available to the SQL Server on the server.

Perf Mon Mode

Operating system path and file name to be used for the SQL Server error log. Windows NT Performance Monitor polling behavior when the monitor is launched.

- 0 — Continuous

- 1 — On demand

Registered Organization

Company name supplied by the installer.

Registered Owner

User name supplied by the installer.

Replication Installed

TRUE when components supporting replication are installed.

RPC Encrypt

Whether RPC encryption is enabled.

SNMP

Whether Simple Network Management Protocol (SNMP) is installed on an instance of the SQL Server.

SNMP Current Version

Version of Simple Management Protocol (SNMP) currently installed on an instance of the SQL Server.

Sort Order

Character set used and ordering applied.

SQL Data Root

Default operating system directory implementing storage for SQL Server system user-defined databases.

TCP Port

TCP/IP Sockets Net-Libraries port number on an instance of the SQL Server.

1.19.3 Database Setting Configuration Metrics

The metrics in this category contain the settings for a database. These settings control the access to and the behavior of the database.

- Table Name — MGMT_EMX_MSSQL_DBSETTING

- View Name — MGMT_EMX_MSSQL_DBSETTING_VIEW

Default Collection Interval — Every 7 Days

Table 1-23 Database Setting Configuration Metrics

Metric

Description

Database Name (key column)

Database name.

Offline

Whether the database is online. Also, whether the database is unavailable, or is being made unavailable, for use by authorized users.

Recovery Type

Whether the comparison method for multi-byte character data is case-sensitive or not. Type of recovery model that a database will use:

- Value — 0
Description — Simple

Explanation — The database can be recovered only to the last full database backup or last differential backup.

- Value — 1
Description — Bulk Logged

Explanation — Logging for all SELECT INTO, CREATE INDEX, and bulk loading data operations is minimal and therefore requires less log space. In exchange for better performance and less log space usage, the risk of exposure to loss is greater than with full recovery.

- Value — 2
Description — Full

Explanation — Database backups and transaction log backups provide full recoverability from media failure. All operations are fully logged, including bulk operations such as SELECT INTO, CREATE INDEX, and bulk loading data.

- Value — 3
Description — Unknown

Explanation — The recovery type is not known.

AutoClose

Whether the database is closed and its resources are freed when no user connection accesses the database.

AutoCreateStatistics

Whether the optimizer directs automatic creation of supporting data statistics as required.

AutoShrink

Whether operating system files maintaining table and index data are evaluated for downward resizing when the server periodically checks for unused space.

AutoUpdateStatistics

Whether the optimizer directs the automatic rebuilding of statistics.

CursorCloseOnCommit

Whether cursors are closed when a transaction is completed.

DataSpaceUsage

Amount of space in use and reserved for use of data in megabytes.

IndexSpaceUsage

Amount of space for the index in megabytes.

DB Owner UseOnly

Whether only users with the database ownership privilege can access the database.

SingleUser Mode

Whether only one user can access the database at a given time.

ReadOnly

Whether the database is read-only.

DefaultCursor

Whether cursors declared in a batch are created with local scope.

SelectIntoBulkCopy

Whether non-logged operations are allowed.

TruncateLogOnChekpoint

Whether the SQL Server removes log entries referencing committed transactions when activity on the databases forces a dirty page write.

Member of an Availability Group

Whether the database is a member of an AlwaysOn availability group.

1.19.4 HADR Availability Group Databases Metrics

The metrics in this category provide information about which databases are members of which AlwaysOn high-availability disaster recovery availability groups.

Default Collection Interval — Every 30 Minutes]

Table 1-24 HADR Availability Group Databases Metrics

Metric

Description

Database ID

Database unique ID.

Group ID

Availability group unique ID.

Database Name

Database name.

Group Name

Availability group name.

1.20 Connection Statistics

The metrics in this category provide information about the number of connections available and used.

Evaluation and Collection Frequency — Every 15 Minutes

Table 1-25 Connection Statistics Metrics

Metric

Description

Number of Connections

The current number of connections to the SQL Server instance.

Maximum Connections

The maximum number of connections to the SQL Server instance.

Connections Used (%)

The percent of connections used.

1.21 Database

The MSSQL_Database class represents a SQL Server database. Each SQL Server installation can contain one or more databases.

Default Collection Interval — Every 15 minutes

Table 1-26 Database Metrics

Metric

Description and User Action

Database Name (key column)

Database name.

Create Date

Time and date the database was created.

Database File Path

Primary location of the database files.

Database Mirroring State

State of the mirror database and the database mirroring session.

Database Mirroring Partner Name

Server name of the database mirroring partner.

Database Mirroring Partner Instance

The instance name for the other partner.

Database Status

Status of the database:

- 0 — Normal

- 32 — Loading

- 192 — Recovering

- 256 — Suspect

- 512 — Offline

- 1024 — Standby

- 32768 — Emergency Mode

Database Size (MB)

Total size of the database in megabytes.

Allocate more space to the database if this metric decreases beyond the critical threshold.

Database Space Available %

Percentage of space that is available.

Allocate more space to the database if this metric decreases beyond the critical threshold.

Auto-growth Potential Space (MB)

Amount of disk space available for the database to grow in to based on its autogrowth settings.

Version

Version of Microsoft SQL Server used to create the referenced database.

Database Owner

The owner of the database

Database ID

The ID of the database

1.22 Database Backup

The metrics in this category provide detailed backup information for all databases.

Default Collection Interval — in real time

Table 1-27 Database Backup Metrics

Metric

Description

Backup Set ID (key column)

Unique backup set identification number that identifies the backup set.

Media Set ID (key column)

Unique media set identification number that identifies the media set containing the backup set.

Family Sequence Number (key column)

Position of this media family in the media set.

File Number (key column)

File identification number unique within a database.

Mirror Number (key column)

Mirror identification number unique within a database.

Backup Set Name

Name of the backup set. Can be NULL

Backup Set Description

Description of the backup set. Can be NULL.

Username

Name of the user performing the backup operation. Can be NULL.

Start Date

Date and time the backup operation started. Can be NULL.

Finish Date

Date and time the backup operation finished. Can be NULL.

Size of Backup (bytes)

Size of the backup set, in bytes. Can be NULL.

Database Name

Name of the database involved in the backup operation. Can be NULL.

Server Name

Name of the server running the SQL Server backup operation. Can be NULL.

Machine Name

Name of the computer running SQL Server. Can be NULL.

Media Set Name

Name of the media set. Can be NULL.

Media Set Device Name

Physical name of the backup device. Can be NULL.

Physical Block Size (Bytes)

Physical block size used to write the media family. Can be NULL.

File Group Name

Name of the filegroup containing a backed up database file. Can be NULL.

File Group Physical Name

Remainder of the physical (operating system) file name. Can be NULL.

Backup Set Expiration Date

Date and time the backup set expires. Can be NULL.

1.23 Database IOPS Metrics

The metrics in this category provide information on the read and write load on each database.

Evaluation and Collection Frequency — Every 15 Minutes

Table 1-28 Database IOPS Metrics

Metric

Description

Bytes Read per second

Number of bytes read per second by this database.

Bytes Written per second

Number of bytes written per second by this database.

Database Name

Name of the database.

Reads per second

Number of reads performed per second by this database.

Writes per second

Number of writes performed per second by this database.

1.24 Database Job

The metrics in this category return information about jobs that are used by the SQLServerAgent service to perform automated activities in Microsoft SQL Server.

Default Collection Interval — Every 2 hours

Table 1-29 Database Job Metrics

Metric

Description and User Action

Computer Used to Send Network Messages

Name of the user or computer used when sending network messages.

Computer Used to Send Pages

Name of the user or computer used when sending a page.

Current Execution Status

- 0 — Returns only jobs that are not idle or suspended

- 1 — Executing

- 2 — Waiting for thread

- 3 — Between retries

- 4 — Idle

- 5 — Suspended

- 7 — Performing completion actions

Current Execution Steps in the Job

Current job execution step.

Current Retry Attempt

If the job is running and the step has been retried, this is the current retry attempt.

Delete Job Event

Bitmask indicating under what circumstances the job should be deleted when a job completes. Possible values are the same as for notify_level_eventlog.

Description

Description for the job.

Email of Operator

Email name of the operator to notify.

Enabled

Indicates whether the job is enabled to be executed.

ID of Next Run Schedule

Identification number of the next run schedule.

Job Category

The category to which the job belongs.

Job Creation Date

Date the job was created.

Job ID (key column)

Job identification number.

Job Modification Date

Date the job was last modified.

Job Owner

The owner of the job.

Job Type

- 1 — Local job

- 2 — Multiserver job

- 0 — Job has no target servers

Job Version Number

Version of the job, which is automatically updated each time the job is modified.

Last Run Date (mm-dd-yyyy)

Date the job last started executing.

Last Run Outcome

Outcome of the job the last time it ran:

- 0 — Failed

- 1 — Succeeded

- 3 — Canceled

- 5 — Unknown

Last Run Time (hh:mm:ss)

Time the job last started executing.

Name

Name of the job.

Next Run Date (mm-dd-yyyy)

Date the job is next scheduled to run.

Next Run Time (hh:mm:ss)

Time the job is next scheduled to run.

Notify Level Email

Bitmask indicating under what circumstances a notification email should be sent when a job completes. Possible values are the same as for notify_level_eventlog.

Notify Level Event Log

Bitmask indicating under what circumstances a notification event should be logged to the Microsoft Windows NT application log. Possible values:

- 0 — Never

- 1 — When a job succeeds

- 2 — When the job fails

- 3 — Whenever the job completes (regardless of the job outcome)

Notify Level Net Send

Bitmask indicating under what circumstances a network message should be sent when a job completes. Possible values are the same as for notify_level_eventlog.

Notify Level Page

Bitmask indicating under what circumstances a page should be sent when a job completes. Possible values are the same as for notify_level_eventlog.

Number of Job Schedules

Number of job schedules the job has.

Number of Job Steps

Number of job steps the job has.

Number of Target Servers

Number of target servers the job has.

Originating Server

Name of the server from which the job originated.

Start Step ID

ID of the step in the job where execution should begin.

1.25 Database Lock

The metrics in this category report information about locks.

Default Collection Interval — Every 15 minutes

Table 1-30 Database Lock Metrics

Metric

Description

Server Process Identifier (key column)

Server process ID of the current user process.

Database Identifier (key column)

Database identification number requesting a lock.

Object Identifier (key column)

Object identification number of the object requesting a lock.

Index Identifier (key column)

Deprecated. Always returns 0.

Mode

Lock mode:

- Shared (S)

- Update (U)

- Exclusive (X)

- Intent

- Schema

- Bulk update (BU)

- RangeS_S — Shared range, shared resource lock; serializable range scan.

- RangeS_U — Shared range, update resource lock; serializable update scan.

- RangeI_N — Insert range, null resource lock. Used to test ranges before inserting a new key into an index.

- RangeX_X — Exclusive range, exclusive resource lock. Used when updating a key in a range.

Resource

Lock resource that corresponds to the value in syslockinfo.restext:

RID, KEY, PAG, EXT, TAB, and DB

Lock Request Status

The current status of the lock:

GRANT, WAIT, and CNVT

Type

The lock type:

- RID = Lock on a single row in a table identified by a row identifier (RID).

- KEY = Lock within an index that protects a range of keys in serializable transactions.

- PAG = Lock on a data or index page.

- EXT = Lock on an extent.

- TAB = Lock on an entire table, including all data and indexes.

- DB = Lock on a database.

- FIL = Lock on a database file.

- APP = Lock on an application-specified resource.

- MD = Locks on metadata, or catalog information.

- HBT = Lock on a heap or B-Tree index. This information is incomplete in SQL Server.

- AU = Lock on an allocation unit. This information is incomplete in SQL Server.

Resource Type

Type of resource being locked. This presents the same data as the Type metric:

- DATABASE

- FILE

- OBJECT

- PAGE

- KEY

- EXTENT

- RID

- APPLICATION

- METADATA

- HOBT

- ALLOCATION_UNIT

1.26 Database Parameter

The Databases object in Microsoft SQL Server provides counters to monitor:

- Bulk copy operations.

- Backup and restore throughput.

- Transaction log activities.

Monitoring transactions and the transaction log determine how much user activity is occurring in the database and how full the transaction log is becoming. The amount of user activity can determine the performance of the database and affect log size, locking, and replication. Monitoring low-level log activity to gauge user activity and resource usage can help you identify performance bottlenecks.

Default Collection Interval — Every 24 hours

Table 1-31 Database Parameter Metrics

Metric

Description and User Action

Parameter Name (key column)

Name of the Database Configuration parameter.

Current Value

Current value of the Database Configuration parameter.

Description

Text description of the configuration value.

Dynamic Reconfigure

Whether the parameter can be dynamically reconfigured. If TRUE, a modification to the value is immediately effective. If FALSE, modifications are visible only after the SQL Server service has been stopped and restarted.

ID

Parameter name.

Maximum Value

Upper bound for a configuration value.

Minimum Value

Lower bound for a configuration value.

Running Value

Value for the configuration option (value in syscurconfigs.value).

Table 1-32 ID Description Mapping

Metric

Description

101

Recovery interval.

102

Allow updates.

103

User Connections.

106

Locks.

107

Open objects

109

Fill factor.

115

Nested triggers.

117

Remote access.

124

Default language.

125

Language in cache.

502

Max async I/O.

503

Max worker threads.

505

Network packet size.

518

Show advanced option.

542

Remote proc trans.

543

Remote conn timeout.

1110

Time slice.

1123

Default sort order ID.

1124

Unicode local ID.

1125

Unicode comparison style.

1126

Language neutral.

1127

Two-digit year cutoff.

1505

Index create mem.

1514

Spin Counter.

1517

Priority boost.

1519

Remote login timeout.

1520

Remote query timeout.

1531

Cursor threshold.

1532

Set working set size.

1533

Resource timeout.

1534

User Options.

1535

Processor affinity mask.

1536

Max text repl size.

1537

Media retention.

1538

Cost threshold for parallelism.

1539

Max degree of parallelism.

1540

Min memory per query.

1541

Query wait.

1542

VLM size.

1543

Min memory.

1544

Max memory.

1545

Query max time.

1546

Lightweight pooling.

1.27 Database Performance

The Databases object in Microsoft SQL Server provides counters to monitor:

- Bulk copy operations.

- Backup and restore throughput.

- Transaction log activities.

Monitoring transactions and the transaction log determine how much user activity is occurring in the database and how full the transaction log is becoming. The amount of user activity can determine the performance of the database and affect log size, locking, and replication. Monitoring low-level log activity to gauge user activity and resource usage can help you identify performance bottlenecks.

1.27.1 For Microsoft SQL Server 2005/2008/2012

Default Collection Interval — Uploads data when an alert is raised

Table 1-33 Database Performance Metrics

Metric

Description

Database Performance Counter Name (key column)

Performance metric name. See Table 1-34.

Database Performance Instance Name (key column)

Instance for the Database Performance Counter Name

Database Performance Server Locks Counter Value

Performance metric value.

The Database Performance Counter Name key column contains several metrics. Table 1-34 provides a list of these metrics and a description for each.

Table 1-34 Database Performance Counter Name Metrics

Metric

Description

Active Transactions

Number of active transactions for the database.

Backup/Restore Throughput/sec

Read/write throughput for backup and restore operations of a database per second. For example, you can measure how the performance of the database backup operation changes when more backup devices are used in parallel or when faster devices are used. Throughput of a database backup or restore operation allows you to determine the progress and performance of your backup and restore operations.

Bulk Copy Rows/sec

Number of rows bulk-copied per second.

Bulk Copy Throughput/sec

Amount of data bulk-copied in kilobytes per second.

Data File(s) Size (KB)

Cumulative size (in kilobytes) of all the data files in the database including any automatic growth. Monitoring this counter is useful, for example, for determining the correct size of tempdb.

DBCC Logical Scan Bytes/sec

Number of logical read scan bytes per second for database consistency checker (DBCC) statements.

Log Bytes Flushed/sec

Total number of log bytes flushed.

Log Cache Hit Ratio

Percentage of log cache reads satisfied from the log cache.

Log Cache Reads/sec

Reads performed per second through the log manager cache.

Log File(s) Size

Cumulative size in kilobytes of all the transaction log files in the database.

Log File(s) Used Size (KB)

The cumulative used size of all the log files in the database.

Log Flush Wait Time

Total wait time in milliseconds to flush the log.

Log Flush Waits/sec

Number of commits per second waiting for the log flush.

Log Flushes/sec

Number of log flushes per second.

Log Growths

Total number of times the transaction log for the database has expanded.

Log Shrinks

Total number of times the transaction log for the database has contracted.

Log Truncations

Total number of times the transaction log for the database has truncated.

Percent Log Used

Percentage of space in the log that is in use.

Repl. Pending Xacts

Number of transactions in the transaction log of the publication database marked for replication, but not yet delivered to the distribution database.

Repl. Trans. Rate

Number of transactions per second read out of the transaction log of the publication database and delivered to the distribution database.

Shrink Data Movement Bytes/sec

Amount of data being moved per second by autoshrink operations, DBCC SHRINKDATABASE, or DBCC SHRINKFILE statements.

Transactions/sec

Number of transactions started for the database per second.

1.28 Database Status

The metrics in this category provide status metrics on which various thresholds can be set.

Evaluation and Collection Frequency — Every 15 Minutes

Table 1-35 Database Status Metrics

Metric

Description

Database Name

Name of the database.

Status

Status of the database.

ONLINE

Indicates whether the database status is ONLINE (Yes/No).

OFFLINE

Indicates whether the database status is OFFLINE (Yes/No)

RESTORING

Indicates whether the database status is RESTORING (Yes/No).

RECOVERING

Indicates whether the database status is RECOVERING (Yes/No)

RECOVERY PENDING

Indicates whether the database status is RECOVERY PENDING (Yes/No)

SUSPECT

Indicates whether the database status is SUSPECT (Yes/No)

EMERGENCY

Indicates whether the database status is EMERGENCY (Yes/No)

1.29 Detailed Response

This metric category provides information about the response of the server.

Evaluation and Collection Frequency — Every 15 Minutes

Table 1-36 Detailed Response Metrics

Metric

Description

Process ID

Process identifier.

Server Name

Name of the server.

Service Path

Path of the SQL service.

SQL Service Status

Status of the SQL server.

1.30 Event Log Entry

The MSSQL_ErrorLogEntry class represents the entries in a SQL Service error log.

Default Collection Interval — Metric is disabled. Uploads data when an alert is raised.

Table 1-37 Event Log Entry Metrics

Metric

Description

Type of Record (key column)

Specifies the type of event. This is an enumerated string

Record Number (key column)

Identifies the event within the Windows logfile (for example, NT Eventlog logfile). This is specific to the logfile and is used together with the logfile name to uniquely identify an instance of this class.

Event Log Entry

Name of the Windows logfile (for example, NT Eventlog logfile). This is used together with the RecordNumber to uniquely identify an instance of this class.

Event Code

This property has the value of the lower 16-bits of the EventIdentifier property. It is present to match the value displayed in the NT Event Viewer. Two events from the same source may have the same value for this property but may have different severity and EventIdentifier values

Event Identifier

Identifies the event. This is specific to the source that generated the event log entry, and is used, together with SourceName, to uniquely identify an NT event type.

Event Severity

Indicates the severity of the event. Two events from the same source may have may have different severity and EventIdentifier values.

Category

Specifies a subcategory for this event. This subcategory is source specific.

User

User name of the logged on user when the event occurred. If the user name cannot be determined this will be NULL.

Event Message

Event message as it appears in the NT Eventlog. This is a standard message with zero or more insertion strings supplied by the source of the NT event. The insertion strings are inserted into the standard message in a predefined format. If there are no insertion strings or there is a problem inserting the insertion strings, only the standard message will be present in this field.

1.31 General Statistics

The General Statistics object in Microsoft SQL Server provides counters to monitor general server-wide activity, such as the number of current connections and the number of users connecting and disconnecting per second from computers running an instance of SQL Server. This can be useful when you are working on large online transaction processing (OLTP) systems where many clients connect and disconnect from an instance of SQL Server.

1.31.1 For Microsoft SQL Server 2005/2008/2012

Default Collection Interval — Every 30 minutes

Table 1-38 General Statistics Metrics

Metric

Description

General Statistics Counter Name (key column)

Performance metric name. See Table 1-39.

General Statistics Counter Value

Performance metric value.

The General Statistics Counter Name key column contains several metrics. Table 1-39 provides a list of these metrics and a description for each.

Table 1-39 General Statistics Counter Name Metrics

Metric

Description

Logins/sec

Total number of logins started per second.

Logouts/sec

Total number of logout operations started per second.

User Connections

Number of user connections. Because each user connection consumes some memory, configuring overly high numbers of user connections could affect throughput. User connections should be set to the maximum expected number of concurrent users.

1.32 HADR Availability Groups

The metrics in this category provide status and health information about AlwaysOn high-availability disaster recovery availability groups.

Evaluation and Collection Frequency — Every 30 Minutes

Table 1-40 HADR Availability Groups Metrics

Metric

Description

Group ID

Availability group unique ID.

Group Name

Availability group name.

Synchronization Health

Summary of the synchronization state for the replicas in the availability group.

Failure Condition Level

Condition under which an automatic failover is triggered.

1.33 HADR Availability Group Replicas

The metrics in this category provide status and health information about AlwaysOn high-availability disaster recovery availability replicas.

Evaluation and Collection Frequency — Every 30 Minutes

Table 1-41 HADR Availability Group Replicas Metrics

Metric

Description

Replica ID

Availability replica unique ID.

Group ID

Availability group unique ID.

Server Name

SQL Server server/instance name hosting the replica.

Availability Mode

Replica availability mode.

- 0 - Asynchronous Commit

- 1 - Synchronous Commit

Availability Mode Description

Label of the replica availability mode.

Role

Current role of this replica in the availability group.

- 0 - Resolving

- 1 - Primary

- 2 - Secondary

Role Description

Label of the replica role.

Connected State

Indicates whether a secondary replica is currently connected to the primary replica.

- 0 - Disconnected

- 1 - Connected

Connected State Description

Label of the replica connected state.

Synchronization Health

Summary of the synchronization state for the databases in the availability group.

- 0 - Not Healthy. At least one database is not synchronizing

- 1 - Partially Healthy. Some databases are not in the target synchronization state.

- 2 - Healthy. All of the databases are in the target synchronization state.

Endpoint URL

Database mirroring endpoint used for synchronization.

1.34 HADR Cluster Members

The metrics in this category provide status and health information about members in a AlwaysOn high-availability disaster recovery cluster.

Evaluation and Collection Frequency — Every 30 Minutes

Table 1-42 HADR Cluster Members Metrics

Metric

Description

Member Name

Availability group cluster member name.

Member Type

Type of member.

- 0 - WSFC node

- 1 - Disk witness

- 2 - File share witness

Member Type Description

Label of the member type.

Member State

State of the cluster member.

- 0 - Offline

- 1 - Online

Member State Description

Label of the member state.

1.35 Index/Table Fragmentation Metrics

The metrics in this category provide information of the defragment level of Microsoft SQL Server 2005. Currently, this metric is not applicable for Microsoft SQL Server 2000. To retrieve data for this metric, DMV "sys.dm_db_index_physical_stats" is queried for each database (for all objects, indices and partitions). The scan level mode to obtain the statistics is DEFAULT or NULL (that is, equivalent to LIMITED), which is the fastest mode and scans the smallest number of pages.

Evaluation and Collection Frequency — Every SUN

Table 1-43 Index/Table Fragmentation Metrics

Metric

Description

Database ID (key column)

Database ID number

Object ID (key column)

Object ID number

Index ID (key column)

Index ID number

Partition Number (key column)

Partition number

Index Type Description (key column)

Index type description, such as CLUSTERED INDEX, and HEAP.

Allocation Unit type Description

Description of the allocation unit type:

- IN_ROW_DATA

- LOB_DATA

- ROW_OVERFLOW_DAT

The LOB_DATA allocation unit contains the data that is stored in columns of type text, ntext, image, varchar(max), nvarchar(max), varbinary(max), and xml. For more information, see Data Types (Transact-SQL).

The ROW_OVERFLOW_DATA allocation unit contains the data that is stored in columns of type varchar(n), nvarchar(n), varbinary(n), and sql_variant that have been pushed off-row. For more information, see Row-Overflow Data Exceeding 8 KB.

Number of Index levels

1 = Heap, or LOB_DATA or ROW_OVERFLOW_DATA allocation unit.

Current Level of Index

Zero for index leaf levels, heaps, and LOB_DATA or ROW_OVERFLOW_DATA allocation units.

Greater than zero for nonleaf index levels. index_level will be the highest at the root level of an index.

The nonleaf levels of indexes are only processed when mode = DETAILED.

Average Fragmentation in Percent

Logical fragmentation for indexes, or extent fragmentation for heaps in the IN_ROW_DATA allocation unit. The value is measured as a percentage and takes into account multiple files. For definitions of logical and extent fragmentation, see Remarks.

Zero for LOB_DATA and ROW_OVERFLOW_DATA allocation units.

NULL for heaps when mode = SAMPLED.

Upload Frequency - After every sample.

Alert Text - Average fragmentation for %indexid% is %avg_fragmentation_in_percent%%% where Database ID is %database_id%, Object ID is %object_id% ,Index ID is %index_id%,Partition Number is %partition_number% and Index type Description is %index_type_desc%. It has crossed warning (%warning_threshold%%%) or critical (%critical_threshold%%%) threshold.

Multiple Thresholds - For this metric you can set different warning and critical threshold values for each unique combination of " Database ID ", " Object ID ", " Index ID ", " Partition Number ", and " Index type Description " objects.

If warning or critical threshold values are currently set for any unique combination of " Database ID ", " Object ID ", " Index ID ", " Partition Number ", and " Index type Description " objects, those thresholds can be viewed on the Metric Detail page for this metric.

To specify or change warning or critical threshold values for each unique combination of "Database ID", "Object ID", "Index ID", "Partition Number", and "Index type Description" objects, use the Edit Thresholds page.

Number of fragments in the leaf level

Number of fragments in the leaf level of an IN_ROW_DATA allocation unit. For more information about fragments, see Remarks.

NULL for nonleaf levels of an index, and LOB_DATA or ROW_OVERFLOW_DATA allocation units.

NULL for heaps when mode = SAMPLED.

Average number of pages in one fragment

Average number of pages in one fragment in the leaf level of an IN_ROW_DATA allocation unit.

NULL for nonleaf levels of an index, and LOB_DATA or ROW_OVERFLOW_DATA allocation units.

NULL for heaps when mode = SAMPLED.

Total number of index or data pages

For an index, the total number of index pages in the current level of the b-tree in the IN_ROW_DATA allocation unit.

For a heap, the total number of data pages in the IN_ROW_DATA allocation unit.

For LOB_DATA or ROW_OVERFLOW_DATA allocation units, total number of pages in the allocation unit.

Average(%) of available data storage space used

Average percentage of available data storage space used in all pages.

For an index, average applies to the current level of the b-tree in the IN_ROW_DATA allocation unit.

For a heap, the average of all data pages in the IN_ROW_DATA allocation unit.

For LOB_DATA or ROW_OVERFLOW DATA allocation units, the average of all pages in the allocation unit.

NULL when mode = LIMITED.

Upload Frequency - After every sample.

Alert Text - Average percentage of available data storage space used in all pages for %indexid% is %avg_page_space_used_in_percent%%% where Database ID is %database_id%, Object ID is %object_id% , Partition Number is %partition_number% and Index type Description is %index_type_desc%.It has fallen below warning (%warning_threshold%%%) or critical (%critical_threshold%%%) threshold.

Multiple Thresholds - For this metric you can set different warning and critical threshold values for each unique combiation of "Database ID", "Object ID", "Index ID", "Partition Number", and "Index type Description " objects.

If warning or critical threshold values are currently set for any unique combination of "Database ID", "Object ID", "Index ID", "Partition Number", and "Index type Description" objects, those thresholds can be viewed on the Metric Detail page for this metric.

To specify or change warning or critical threshold values for each unique combination of "Database ID", " Object ID ", " Index ID ", "Partition Number", and "Index type Description" objects, use the Edit Thresholds page.

Total number of records

Total number of records. For an index, total number of records applies to the current level of the b-tree in the IN_ROW_DATA allocation unit. For a heap, the total number of records in the IN_ROW_DATA allocation unit.

Note: For a heap, the number of records returned from this function might not match the number of rows that are returned by running a SELECT COUNT(*) against the heap. This is because a row may contain multiple records. For example, under some update situations, a single heap row may have a forwarding record and a forwarded record as a result of the update operation. Also, most large LOB rows are split into multiple records in LOB_DATA storage.

For LOB_DATA or ROW_OVERFLOW_DATA allocation units, the total number of records in the complete allocation unit. NULL when mode = LIMITED.

Number of ghost records ready for removal

Number of ghost records ready for removal by the ghost cleanup task in the allocation unit.

Zero for nonleaf levels of an index in the IN_ROW_DATA allocation unit.

NULL when mode = LIMITED.

Number of ghost records retained in an allocation unit

Number of ghost records retained by an outstanding snapshot isolation transaction in an allocation unit.

Zero for nonleaf levels of an index in the IN_ROW_DATA allocation unit.

NULL when mode = LIMITED.

Minimum record size in bytes

For an index, minimum record size applies to the current level of the b-tree in the IN_ROW_DATA allocation unit.

For a heap, the minimum record size in the IN_ROW_DATA allocation unit.

For LOB_DATA or ROW_OVERFLOW_DATA allocation units, the minimum record size in the complete allocation unit.

NULL when mode = LIMITED.

Maximum record size in bytes

For an index, the maximum record size applies to the current level of the b-tree in the IN_ROW_DATA allocation unit.

For a heap, the maximum record size in the IN_ROW_DATA allocation unit.

For LOB_DATA or ROW_OVERFLOW_DATA allocation units, the maximum record size in the complete allocation unit.

NULL when mode = LIMITED.

Average record size in bytes

For an index, the average record size applies to the current level of the b-tree in the IN_ROW_DATA allocation unit.

For a heap, the average record size in the IN_ROW_DATA allocation unit.

For LOB_DATA or ROW_OVERFLOW_DATA allocation units, the average record size in the complete allocation unit.

NULL when mode = LIMITED.

Number of records in a heap that have forward pointers

Number of records in a heap that have forward pointers to another data location. (This state occurs during an update, when there is not enough room to store the new row in the original location.)

NULL for any allocation unit other than the IN_ROW_DATA allocation units for a heap.

NULL for heaps when mode = LIMITED.

Database Name

The name of the database.

Object Name

The name of the object.

Index Name

The name of the index.

1.36 Index Usage

This metric category provides information about index operations.

Default Collection Interval — Every 60 minutes

Table 1-44 Index Usage Metrics

Metric

Description

Database Name

Name of the database.

Database ID

Database identifier

Table or View Name

Name of the table or view.

Table or View ID

Identifier of the table or view.

Index Name

Name of the index.

User Seeks per second

User Seeks per second.

User Scans per second

User Scans per second

User Lookups per second

User Lookups per second

User Updates per second

User Updates per second

System Seeks per second

System Seeks per second

System Scans per second

System Scans per second

System Lookups per second

System Lookups per second

System Updates per second

System Updates per second

Total Operations per second

Total Operations per second

1.37 Last Database Backup

The metrics in this category provide the last data or incremental backup information for all databases.

Default Collection Interval — Every 24 hours

Table 1-45 Last Database Backup Metrics

Metric

Description

Database_name (key column)

Name of the database.

Days Since Last Backup

Date when the last data or incremental backup of the database was initiated.

Last Backup Date

Date when the last backup of the database was initiated.

Last Backup Type

Whether the last backup was a data (full) or incremental backup.

1.38 Latches

The metrics in this category provide counters to monitor latches.

Evaluation and Collection Frequency — Every 15 Minutes

Table 1-46 Latch Wait Time Metrics

Metric

Description

Latch Counter Name

Name of the latch counter

1.39 Latch Wait Time

The metrics in this category provide details about the average Latch Wait time.

Evaluation and Collection Frequency — Every 15 Minutes

Table 1-47 Latch Wait Time Metrics

Metric

Description

Average Latch Wait Time (ms)

Average Latch Wait Time in milliseconds.

1.40 Login

The MSSQL_Login class represents the login authentication records present in a SQL Server installation.

Default Collection Interval — in real time

Table 1-48 Login Metrics

Metric

Description

Name (key column)

User name.

Type

Login type for the user:

- 0 — Other NT user authentication

- 1 — NT group

- 2 — SQL server authentication

1.41 Memory Manager

The Memory Manager object in Microsoft SQL Server provides counters that enable you to monitor overall server memory usage to gauge user activity and resource usage. This can help you identify performance bottlenecks.

1.41.1 For Microsoft SQL Server 2005/2008/2012

Default Collection Interval — Every 15 minutes

Table 1-49 Memory Manager Metrics

Metric

Description

Memory Manager Counter Name (key column)

Performance metric name. See Table 1-50.

Memory Manager Counter Value

Performance metric value.

The Memory Manager Counter Name key column contains several metrics. Table 1-50 provides a list of these metrics and a description for each.

Table 1-50 Memory Manager Counter Name Metrics

Metric

Description

Connection Memory (KB)

Total amount of dynamic memory the server is using for maintaining connections.

Granted Workspace Memory (KB)

Total amount of memory currently granted to executing processes such as hash, sort, bulk copy, and index creation operations.

Lock Memory (KB)

Total amount of dynamic memory the server is using for locks.

Lock Blocks Allocated

Current number of allocated lock blocks. At server startup, the number of allocated lock blocks plus the number of allocated lock owner blocks depends on the SQL Server Locks configuration option. If more lock blocks are needed, the value increases.

Lock Owner Blocks Allocated

Current number of allocated lock owner blocks. At server startup, the number of allocated lock owner blocks plus the number of allocated lock blocks depends on the SQL Server Locks configuration option. If more lock owner blocks are needed, the value increases dynamically.

Lock Blocks

Current number of lock blocks in use on the server (refreshed periodically). A lock block represents an individual locked resource, such as a table, page, or row.

Lock Owner Blocks

Number of lock owner blocks currently in use on the server (refreshed periodically). A lock owner block represents the ownership of a lock on an object by an individual thread. Therefore, if three threads each have a shared (S) lock on a page, there will be three lock owner blocks.

Maximum Workspace Memory (KB)

Maximum amount of memory available for executing processes such as hash, sort, bulk copy, and index creation operations.

Memory Grants Outstanding

Total number of processes per second that have successfully acquired a workspace memory grant.

Memory Grants Pending

Total number of processes per second waiting for a workspace memory grant.

Optimizer Memory (KB)

Total amount of dynamic memory the server is using for query optimization.

SQL Cache Memory (KB)

Total amount of dynamic memory the server is using for the dynamic SQL cache.

Target Server Memory (KB)

Total amount of dynamic memory the server is willing to consume.

Total Server Memory (KB)

Memory allocated to the SQL Server.

1.42 Memory Statistics

The metrics in this category provide information about various memory-related performance issues.

Default Collection Interval — Every 15 minutes

Table 1-51 Memory Statistics Metrics

Metric

Description and User Action

Average Latch Wait Time (ms)

Average latch wait time in milliseconds for latch requests that had to wait.

If this number is high, your server might have resource limitations.

Buffer Cache Hit Ratio (%)

Percentage of pages found in the buffer cache without having to read from disk. The ratio is the total number of cache hits divided by the total number of cache lookups since the SQL Server was started. After a long period of time, the ratio does not change very much.

Because reading from the cache is much less expensive than reading from disk, this ratio should be high. Generally, you can increase the buffer cache hit ratio by increasing the amount of memory available to the SQL Server.

Cache Hit Ratio (%)

Percentage of pages found in the cache without needing to read from disk. The ratio is the total number of cache hits divided by the total number of cache lookups since the SQL Server was started. After a long period of time, the ratio does not change very much.

Because reading from the cache is less expensive than reading from disk, this ratio should be high. The higher this value is, the better. Generally, you can increase the cache hit ratio by increasing the amount of memory available to the SQL Server.

Log Flush Wait Time (ms)

Log cache is very important, because it rolls back a transaction before it is committed if the circumstances warrant. But after a transaction is complete (and no longer can be rolled back), this log cache is immediately flushed to the physical log file. This is a normal procedure.

SELECT queries that do not modify data do not create transactions and do not produce log flushes. Essentially, a log flush occurs when data is written from the log cache to the physical log file. Therefore, a log flush occurs every time a transaction completes, and the number of log flushes that occur are related to the number of transactions performed by the SQL Server.

One way to troubleshoot the disk I/O bottleneck is to capture the Log Flushes/sec counter data and see how busy this mechanism is. If the server experiences a lot of transactions, it will also experience a lot of log flushes, so the value you see for this counter can vary from server to server, depending on how busy it is with action-type queries that create transactions.

Try to identify situations where the number of log flushes per second seems to be significantly higher than the expected number of transactions that you think should be running on a server.

Total Lock Wait Time (ms)

Total wait time in milliseconds for locks in the last second. If the value is high, your server has high resource contention.

1.43 Memory Usage

This metric category provides information about how much memory is used by the server.

Default Collection Interval — Every 15 minutes

Table 1-52 Memory Usage Metrics

Metric

Description

Server Memory (KB)

Amount of memory the server is using.

1.44 Mirroring Monitoring

This metric category provides information about a database mirroring session. You must launch the Microsoft Database Mirroring Monitor tool before this metric category can collect data. Because of this requirement, this metric category is disabled by default.

Table 1-53 Mirroring Monitoring Metrics

Metric

Description

Database Name

Name of the database.

Mirroring Role

Current role that the database plays in the database mirroring session.

Mirroring State

State of the mirror database and the database mirroring session.

Witness Status

Status of the witness in the database mirroring session.

Log Generation Rate (KB)

Rate of log generation in KB/s.

Unsent Log (KB)

Amount of unsent logs in KB.

Send Rate (KB/s)

Send rate in (KB/s)

Unrestored Log (KB)

Amount of unrestored logs in KB.

Recovery Rate (KB/s)

Recovery rate in KB/s

Transaction Delay (ms)

Length of a transaction delay in milliseconds

Transactions per second

Amount of transaction per second.

Average Transaction Delay

Average transaction delay.

Mirroring Role Description

Description of the mirroring role.

Mirroring State Description

Description of the mirroring state.

Witness Status Description

Description of the witness status.

1.45 Mirroring Statistics

This metric category provides statistics about the mirroring session.

Default Collection Interval — Every 15 minutes

Table 1-54 Mirroring Statistics Metrics

Metric

Description

Mirroring Statistics Instance Name

Name of the mirroring instance.

Mirroring Statistics Counter Name

Name of the mirroring statistics counter

Mirroring Statistics Counter Value

Value of the mirroring statistics counter.

1.46 Missing Indexes

This metric category leverages MSSQL Dynamic Management Views to get recommended indexes.

Default Collection Interval — Every 60 minutes

Table 1-55 Missing Indexes Metrics

Metric

Description

Database Name

Name of the database.

Database ID

Database identifier.

Fully Qualified Table Name

Available space for data file

Table ID

Table identifier.

Equality Columns

Columns that are queried when this index would be used.

Inequality Columns

Columns that are queried when this index would be used.

Included Columns

Columns often included in the result set when this index would be used.

Unique Compiles

Number of unique compiles.

Last User Seek Time

Time of the last user seek.

Last User Scan Time

Time of the last user scan.

Last System Seek Time

Time of the last system seek.

Last System Scan Time

Time of the last system scan.

Estimated Performance Benefit

Unitless measure of estimated benefit from this index. Useful only to compare to other recommended indexes.

Generated Index Name

Name generated based on table name and columns involved and included.

User Seeks per second

User Seeks per second

User Scans per second

User Scans per second

System Seeks per second

System Seeks per second

System Scans per second

System Scans per second

1.47 MSSQL Database File

The MSSQL_DatabaseFile class is an extension to the CIM_DataFile class. It contains properties that are relevant to an operating system file that is also a file storing SQL Server database data.

Default Collection Interval — Every 30 minutes

Table 1-56 MSSQL Database File Metrics

Metric

Description

Database File Name (key column)

User name.

Database Name (key column)

Name of the database.

FileGroup Name (key column)

Name of the File Group.

Database File Path

Complete path of the database file.

Datafile Free Space (MB)

Available space for data file

Datafile Used Space (MB)

Used space for data file

File Growth

Growth increment of the operating system file that stores table, index, or log data. When FileGrowthType is in megabytes, the FileGrowth value represents the number of megabytes of disk space to allocate for incremental file growth. When FileGrowthType is percent, the value represents a percentage and must be in the range from 1 through 100.

File Growth Type

Method of incremental allocation applied when an operating system file is extended.

- 0 — Megabyte

- 1 — Percent

- 99 — Invalid

Maximum Size

Upper limit for the size of an operating system file containing table and index data, or maintaining a database transaction log.

Normalized (for autogrowth) Maximum File Size (MB)

Maximum file size of the database.

Primary File

Whether the database file is the one that maintains the database-specific system tables. A Microsoft SQL Server database can have only one primary file.

Space Available in MB

Amount of disk resources, in megabytes, allocated and unused in operating system files.

Database File Space Available (%)

Percentage of space available for the database file. If file autogrowth is enabled, the percentage of space available to the largest possible database file.

Size (MB)

Current size of the database file.

1.48 MSSQL File Group

The MSSQL_FileGroup class represents the groups of operating system files that store a database. A SQL Server filegroup categorizes the operating system files containing data from a single SQL Server database to simplify database administration tasks, such as a backup. A filegroup cannot contain the operating system files of more than one database, though a single database can contain more than one filegroup.

Default Collection Interval — in real time

Table 1-57 MSSQL File Group Metrics

Metric

Description

DatabaseName (key column)

Name of the database.

Filegroup Name (key column)

File Group name.

Default

Whether the file group is the default file group during table or index creation.

File Group Free Space (MB)

Available free space for file group.

File Group Used Space (MB)

Used space for file group.

Read Only

Whether the file group is read only.

Total Size of the File Group (in MB)

Total size of the file group in megabytes.

Type

File group type. A database is created on exactly one file group named PRIMARY. This is the primary file group. After database creation, you can add a file group to the database, called a user-defined file group.

- 0 — User-defined

- 8 — On read-only media

- 16 — Primary

1.49 MSSQL Transaction Log

Note:
This metric is supported for SQLServer Database 2000 targets but not for SQLServer Database 2005 targets.

The MSSQL_TransactionLog class represents the transaction log of a Microsoft SQL Server database. A SQL Server transaction log maintains a record of modifications to the operating system files containing the data of an SQL Server database. The transaction log provides data recovery assistance if a system failure occurs, and a SQL Server database has at least one operating system file that stores transaction log records. A transaction log can be written to more than one operating system file. Each SQL Server database maintains its own transaction log, and the operating system file or files that store log records cannot be shared with another database.

Default Collection Interval — Every 30 minutes

Table 1-58 MSSQL Transaction Log Metrics

Metric

Description

Database Name (key column)

FileGroup name.

Size

Initial size of the database.

Last Backup

Time of the last backup.

Auto-growth Potential Space (MB)

Amount of disk space available for the log to grow into based on its autogrowth settings.

Transaction Log Space Available (%)

Percentage of space available in the database.

Unused Space (MB)

Unused space in the database.

1.50 Processor

The Win32_Processor class represents a device that is capable of interpreting a sequence of machine instructions on a Win32 computer system. On a multiprocessor machine, one instance of this class exists for each processor.

Default Collection Interval — Every 15 minutes

Table 1-59 Processor Metrics

Metric

Description and User Action

Device (key column)

Device ID for the device.

CPU Status

Status of the CPU.

Load Percentage

Usage of the CPU. If the value increases above the critical threshold, this indicates a possible risk to the processor.

1.51 Response

This metrics category provide information about the response of the target SQL Server Instance.

Default Collection Interval — Every 5 minutes

Table 1-60 Response Metrics

Metric

Description and User Action

Process ID

Process ID of the SQL Server process.

Server Name

Name of the instance of the SQL Server.

Software Home

Path of the SQL Server process.

SQL Server Status

Status of the SQL Server process. When the status is not Running, the SQL Server must be started.

1.52 Server Statistics Metrics

The metrics in this category provide information about various server-related performance issues.

Default Collection Interval — Every 15 minutes

Table 1-61 Server Statistics Metrics

Metric

Description

CPU Busy Ratio

CPU utilization.

CPU ms

CPU busy time in milliseconds.

Errors / sec.

Packet error rate in seconds.

IDLE ms

CPU idle time in milliseconds.

IO ms

IO busy time in milliseconds.

Max Connections

Maximum number of connections.

Open Transactions

Total number of transactions.

Packet Error Ratio

The ratio of erroneous packets received to the number of packets received.

Packets Errors

Number of packet errors.

Packets Received

Number of received packets.

Packets Sent

Number of sent packets.

Reads / sec.

Packet read rate in seconds.

Total Errors

Total number of errors.

Total Reads

Total number of reads.

Total Writes

Total number of writes.

Writes / sec.

Packet write rate in seconds.

1.53 Space Usage

The metrics in this category provide information on how the space in each database is used.

Evaluation and Collection Frequency — Every 15 Minutes

Table 1-62 Space Usage Metrics

Metric

Description

Database Name

Name of the database.

Total Size of Database (MB)

Total size of the database.

Unallocated (MB)

Amount of unallocated space in the database.

Transaction Log Total Size (MB)

Total size of the transaction log.

Transaction Log Available (%)

Percent of the transaction log available.

Database File System Available (%)

Percent of the file system available.

Primary Filegroup Available (%)

Percent of the primary filegroup available.

Data (MB)

Amount of space used by data.

Indexes (MB)

Amount of space used by indexes.

Reserved but Unused (MB)

Amount of space that is reserved, but not used yet.

1.54 SQL Cluster Nodes Information

The metrics in this category provide details about the Windows cluster nodes.

Evaluation and Collection Frequency — Every 30 Minutes

Table 1-63 SQL Cluster Nodes Information Metrics

Metric

Description

Node Name

Name of the Windows cluster node.

Index

The name of an index.

1.55 SQL Cluster Number of Nodes

The metrics in this category provide the number of nodes in the cluster.

Evaluation and Collection Frequency — Every 30 Minutes

Table 1-64 SQL Cluster Number of Nodes Metrics

Metric

Description

Number of Nodes

Number of cluster nodes.

1.56 SQL Instance Statistics

This metric category provides statistics about the SQL instance.

Evaluation and Collection Frequency — Every 60 Minutes

Table 1-65 SQL Instance Statistics Metrics

Metric

Description

Combined Database File Size (MB)

Combined database file size of a SQL instance.

Number of databases

Number of databases on a SQL instance.

1.57 SQL Server Process Statistics

The metrics in this category provide the number of nodes in the cluster.

Evaluation and Collection Frequency — Every 15 Minutes

Table 1-66 SQL Server Process Statistics Metrics

Metric

Description

Process Count

Number of processes.

Process State

Specifies whether the process is running or sleeping

1.58 SQL Server Backups

The metrics in this category provide a list of backups that can be restored or deleted through the plug-in job process.

Table 1-67 SQL Server Backups Metrics

Metric

Description

Backup File

Backup file with full path.

Server Name

Server name.

Database Name

Name of database that has been backed up

Backup Finish Date

Backup finish date

Backup Size (byte)

File size of backup

Backup Type

Type of back up.

- D = Database

- I = Database Differential

- L = Log

- F = File or Filegroup.

Media Set Id

Media set ID

File Position

Position of database backup in backup file.

Backup GUID

Unique identifier for the backup.

Differential Base GUID

Unique identifier of the differential base.

1.59 SQL Server Locks

The Locks object in Microsoft SQL Server provides information about SQL Server locks on individual resource types. Locks are held on SQL Server resources, such as rows read or modified during a transaction, to prevent concurrent use of resources by multiple transactions. For example, if an exclusive (X) lock is held on a row within a table by a transaction, no other transaction can modify that row until the lock is released. Minimizing locks increases concurrency, which can improve performance. Multiple instances of the Locks object can be monitored at the same time, with each instance representing a lock on a resource type.

1.59.1 For Microsoft SQL Server 2005/2008/2012

Default Collection Interval — Every 15 minutes

Table 1-68 SQL Server Locks Metrics

Metric

Description

SQL Server Locks Counter Name (key column)

Performance metric name. See Table 1-69.

SQL Server Locks Instance Name (key column)

Instance for the SQL Server Locks Counter Name.

SQL Server Locks Counter Value

Performance metric value.

The SQL Server Locks Counter Name key column contains several metrics. Table 1-69 provides a list of these metrics and a description for each.

Table 1-69 SQL Server Locks Counter Name Metrics

Metric

Description

Average Wait Time (ms)

Average amount of wait time in milliseconds for each lock request that resulted in a wait.

Average Wait Time Base

Denominator ("base") of a fraction that the performance counter Average Wait Time ratio represents.

Lock Requests/sec

Number of new locks and lock conversions per second requested from the lock manager.

Lock Timeouts/sec

Number of lock requests per second that timed out, including internal requests for NOWAIT locks.

Lock Waits/sec

Number of lock requests per second that could not be satisfied immediately and required the caller to wait.

Lock Wait Time (ms)

Total wait time in milliseconds for locks in the last second.

Number of Deadlocks/sec

Number of lock requests per second that resulted in a deadlock.

1.60 SQL Server Process

The MSSQL_Process class represents SQL Server processes. Note that these are not the same as an operating system's notion of a process. These are the processes identified by the SQL Server and assigned a SQL Server process ID by the SQL Server.

Default Collection Interval — Every 15 minutes

Table 1-70 SQL Server Process Metrics

Metric

Description and User Action

Process Handle (key column)

Process ID.

Blocked Process ID

ID of a process being blocked by the process.

CPU Time (ms)

Cumulative CPU usage time of the process.

Client Name

Name of the client application.

Command

Abbreviated indicator of the current command. When no command is current, it has a value of AWAITING COMMAND.

Creation Date

Time that the process began executing.

Database Name

Database currently being used by the process.

Execution State

Current operating condition of the process. Possible values are as shown:

- 0 — Unknown

- 1 — Other

- 2 — Ready

- 3 — Running

- 4 — Blocked

- 5 — Suspended Blocked

- 6 — Suspended Ready

Host Name

Name of the client workstation that started the SQL Server process.

Login

Login used by the process to connect to the SQL Server.

Memory Usage (pages)

Number of pages in the procedure cache that are currently allocated to this process. A negative number indicates that the process is freeing memory allocated by another process.

Process State

Whether the process is running or sleeping.

OS Level Thread ID

Identifier of the OS level thread.

1.61 SQL Server Role

The MSSQL_Role class represents a database role or a SQL Server role. Roles establish groups of users with similar security attributes. Permissions can be granted by role, simplifying security planning and administration.

Default Collection Interval — Real Time

Table 1-71 SQL Server Role Metrics

Metric

Description

Name

Role name.

Full Name

Descriptive title for the role.

1.62 SQL Statistics

The SQL Statistics object in the Microsoft SQL Server provides counters to monitor compilation and the type of requests sent to an instance of the SQL Server. Monitoring the number of query compilations and recompilations and the number of batches received by an instance of the SQL Server indicates how quickly the SQL Server is processing user queries and how effectively the query optimizer is processing the queries.

1.62.1 For Microsoft SQL Server 2005/2008/2012

Default Collection Interval — Every 10 minutes

Table 1-72 SQL Statistics Metrics

Metric

Description

SQL Statistics Counter Name (key column)

Performance metric name. See Table 1-73.

SQL Statistics Counter Value (key column)

Performance metric value.

The SQL Statistics Counter Name key column contains several metrics. Table 1-73 provides a list of these metrics and a description for each.

Table 1-73 SQL Statistics Counter Name Metrics

Metric

Description

Auto-Param Attempts/sec

Number of auto-parameterization attempts per second. Total should be the sum of the failed, safe, and unsafe auto-parameterizations. Auto-parameterization occurs when the SQL Server attempts to reuse a cached plan for a previously executed query that is similar as the current query, but not exactly the same. For more information, see "Auto-parameterization" in the Microsoft SQL Server Introduction.

Batch Requests/sec

Number of Transact-SQL command batches received per second. This statistic is affected by all constraints (such as I/O, number of users, cache size, complexity of requests, and so forth). High batch requests mean good throughput. For more information, see "Batch Processing" in the Microsoft SQL Server Introduction.

Safe Auto-Params/sec

Number of safe auto-parameterization attempts per second.

SQL Compilations/sec

Number of SQL compilations per second. Indicates the number of times the compile code path is entered. Includes compiles due to recompiles. After SQL Server user activity is stable, this value should reach a steady state.

SQL Recompilations/sec

Number of SQL recompiles per second. Counts the number of times recompiles are triggered. Generally, the number of recompiles should be low.

Unsafe Auto-Params/sec

Number of unsafe auto-parameterization attempts per second. The table has characteristics that prevent the cached plan from being shared. These are designated as unsafe. The fewer of these that occur the better.

1.63 Top Queries by CPU Time Metrics

The metrics in this category provide details about the Top Queries by CPU Time.

Evaluation and Collection Frequency — Every 1 Hour

Table 1-74 Top Queries by CPU Time Metrics

Metric

Description

Query Hash (key)

An MD5 hash of the query text.

Query

Text of the query.

Total CPU Time (ms)

Total amount of CPU time used by the query since the SQL Server service started.

CPU Time (ms) per Hour

Amount of CPU time used by the query per hour during the previous collection interval.

1.64 Top Queries by Execution Count

The metrics in this category provide details about the top queries by execution count.

Evaluation and Collection Frequency — Every 1 Hour

Table 1-75 Top Queries by Execution Count Metrics

Metric

Description

Query Hash (key)

An MD5 hash of the query text.

Query

Text of the query.

Total Execution Count

Number of times the query has executed since the SQL Server service started.

Execution Count per Hour

Number of times the query was executed per hour during the previous collection interval.

1.65 Top Queries by Total Blocked Time

The metrics in this category provide details about the top queries by total blocked time.

Evaluation and Collection Frequency — Every 1 Hour

Table 1-76 Top Queries by Total Blocked Time Metrics

Metric

Description

Query Hash (key)

An MD5 hash of the query text.

Query

Text of the query.

Total Blocked Time (ms)

Total amount of time the query has spent waiting for the CPU since the SQL Server service started.

Blocked Time (ms) per Hour

Amount of time the query spent waiting for the CPU per hour during the previous collection interval.

1.66 Top Sessions by CPU Utilization

The metrics in this category provide details about the Top Sessions by CPU Utilization.

Evaluation and Collection Frequency — Every 1 Hour

Table 1-77 Top Queries by Top Sessions by CPU Utilization Metrics

Metric

Description

Session ID (key)

SQL Server session ID.

Login Name

SQL Server login name.

Login Time

Time when the session was established

Session Status

Status of the session.

Total CPU Time (ms)

Total amount of CPU time used by the session since the SQL Server service started

CPU Time (ms) per Hour

Amount of CPU time used by the session per hour during the previous collection interval.

1.67 Top Sessions by Memory Utilization

The metrics in this category provide details about the Top Sessions by Memory Utilization.

Evaluation and Collection Frequency — Every 1 Hour

Table 1-78 Top Sessions by Memory Utilization Metrics

Metric

Description

Session ID (key)

SQL Server session ID.

Login Name

SQL Server login name.

Login Time

Time when the session was established.

Session Status

Time when the session was established.

Memory Used (8 KB Pages)

Number of memory pages being used by this session at the collection time..

1.68 User

The User object exposes the attributes of a single Microsoft SQL Server database user.

Default Collection Interval — Real Time

Table 1-79 User Metrics

Metric

Description and User Action

DatabaseName (key column)

Name of the database.

Name (key column)

User name.

User Status

The status property is a string indicating the current status of the object. Various operational and non-operational statuses can be defined. Operational statuses are OK, Degraded, and Pred Fail. Pred Fail indicates that an element may be functioning properly but predicting a failure in the near future.

System Object

The SystemObject property indicates whether the object is owned by Microsoft. A value of True indicates that the object implementation is owned by Microsoft.

1.69 Windows Cluster Name

The metrics in this category provide details about the Windows cluster.

1.69.1 For Microsoft SQL Server 2005

Default Collection Interval — Every 30 minutes

Table 1-80 Windows Cluster Name Metrics

Metric

Description

Cluster Name (key column)

Name of the Windows cluster.

Cluster Server Name

Name of the cluster server.

1.70 Windows Cluster Nodes

The metrics in this category provide details about Windows cluster nodes.

1.70.1 For Microsoft SQL Server 2005

Default Collection Interval — Every 30 minutes

Table 1-81 Windows Cluster Nodes Metrics

Metric

Description

Node Name (key column)

Name of the Windows cluster node.

Cluster Server Name

Name of the cluster server.

2 IBM DB2 Database Metrics

This chapter provides descriptions for all IBM DB2 Database metric categories, and tables list and describe associated metrics for each category. The tables also provide user actions if any of the metrics for a particular category support user actions. Shaded rows represent key columns for a particular category.

- Viewing Metrics

- Connections

- Database Manager Configuration Information

- Health Indicators/Alarms

- Monitoring Information

- Performance

- Response

- Detailed Response

- Storage Information

- System Configuration Information

- DB2 Diag Log File Monitoring

- HADR Status Metrics

- Top Statements by CPU Time Metrics

- Top Statements by Execution Count Metrics

- Lock Waits Metrics

- Lock Waits by Table Metrics

- Lock Waits by Blocked Application Metrics

- Lock Waits by Blocking Application Metrics

2.1 Viewing Metrics

To view the configuration metrics within the plug-in, navigate to the Last Collected configuration metrics page by selecting Configuration, then Last Collected from IBM DB2.

Figure 2-1 Last Collected Page for IBM DB2 Plug-in

To view the performance and status metrics within the plug-in, navigate to the plug-in's All Metrics page by selecting Monitoring, then All Metrics from IBM DB2.

Figure 2-2 All Metrics Page for the IBM DB2 Plug-in

2.2 Connections

Connections metrics provide the connection details for the database at the agent level and at the Database Manager level. Connections metrics consist of the following categories:

- Agents Connection

- Application Connection

2.2.1 Agents Connection

The metrics in this category return the agent connection statistics, including the configured values and the current parameter values at the database manager snapshot level.

Default Collection Interval — Every 15 minutes

Table 2-1 Agents Connection Metrics

Metric

Description and User Action

Agent Created Due to Empty Pool

Number of agents created due to an empty pool.

Agent Creation Rate

Creation rate for agents in the last interval, which equals:

(agents_created_empty_pool-agents_created_empty_pool)/ __interval

Increase the value of the configuration parameter agents_created_empty_pool.

Agent Creation to Assignment Ratio

Creation to assignment ratio, which equals:

(agents_created_empty_pool /agents_from_pool)

Increase the value of the configuration parameter agents_from_pool.

Agent Limit

Number of agents the database manager can create.

Agent Waiting Top

Maximum number of agents that have waited.

Agents Assignment Rate

Assignment rate for agents in the last interval, which equals:

(agents_from_pool-agents_from_pool) /__interval

Increase the value of the configuration parameter agents_from_pool.

Agents Registered

Number of registered agents.

Agents Waiting on Token

Number of agents waiting on a token.

Coordination Agents Top

Maximum number of coordination agents.

Idle Agents

Number of idle agents.

Maximum Agents Registered

Maximum number of registered agents.

Maximum Coordination Agents

Maximum number of coordination agents the database has created.

Pool Agents

Number of agents from the pool.

2.2.2 Application Connection

The metrics in this category return the current connection information at both the database and database manager snapshot level, including the number of active connections.

Default Collection Interval — Every 15 minutes

Table 2-2 Application Connection Metrics

Metric

Description

Active Applications

Number of active applications.

Active Local Connections

Number of active local connections.

Active Remote Connections

Number of active remote connections.

Applications Connected

Number of applications connected.

Database Name (key column)

Name of the database

Remote Connections

Current number of connections initiated from remote clients to the instance of the database manager that is being monitored.

Local Connections

Number of local applications currently connected to a database within the database instance being monitored.

Total Connections

Total number of connections.

2.3 Database Manager Configuration Information

Database Manager Configuration Information metrics describe a set of database manager configuration parameters. These values are of two types: in hard disk and in memory, which is the type presented here. Database Manager Configuration Information metrics consist of the following categories:

- Capacity

- Connection

- Database Instance

- Logging and Recovery

- Partitioned Database Environment

2.3.1 Capacity

The metrics in this category return the database manager capacity configuration parameters that can impact the throughput on your system.

- Table Name — MGMT_EMX_IBMDB2_DBMCAP

- View Name — MGMT_EMX_IBMDB2_DBMCAP_VIEW

Default Collection Interval — Every 24 hours

Table 2-3 Capacity Metrics

Metric

Description

Application Support Layer Size

The application support layer heap represents a communication buffer between the local application and its associated agent.

Database System Monitor Heap Size

Determines the amount of memory, in pages, to allocate for database monitor data.

Audit Buffer Size

Specifies the size of the buffer used when auditing the database.

Maximum Java Interpreter Heap Size

Determines the maximum size of the heap used by the Java interpreter started to service Java DB2 stored procedures and UDFs.

Maximum Total Files Open

Defines the maximum number of files that can be opened by all agents and other threads executing in a single database manager instance.

Priority of Agents

Controls the priority the operating system scheduler gives to all agents and other database manager instance processes and threads.

Maximum Number of Agents

Indicates the maximum number of database manager agents, whether coordinator agents or subagents, available at any given time to accept application requests.

Maximum Number of Concurrent Agents

Maximum number of database manager agents that can concurrently execute a database manager transaction.

Agent Pool Size

Determines the maximum size of the idle agent pool.

Initial Number of Agents in Pool

Determines the initial number of idle agents created in the Agentpool at DB2START time.

Sort Heap Threshold

The size of the shared sort memory is statically predetermined at the time of the first connection to a database based on the value of the sort heap threshold configuration parameter (sheapthreas).

2.3.2 Connection

The metrics in this category return the parameters that provide information about using DB2 in a client/server environment.

- Table Name — MGMT_EMX_IBMDB2_DBMCON

- View Name — MGMT_EMX_IBMDB2_DBMCON_VIEW

Default Collection Interval — Every 24 hours

Table 2-4 Connection Metrics

Metric

Description

TCP/IP Service Name

Contains the name of the TCP/IP port that a database server uses to communicate to the client.

Search Discovery Communications Protocols

From an administration server perspective, this metric defines the search discovery managers started when DB2ADMIN starts.

2.3.3 Database Instance

The metrics in this category return the parameters that provide information about database manager instances.

- Table Name — MGMT_EMX_IBMDB2_DBMDBINST

- View Name — MGMT_EMX_IBMDB2_DBMDBINST_VIEW

Default Collection Interval — Every 24 hours

Table 2-5 Database Instance Metrics

Metric

Description

Diagnostic Error Capture Level

Determines the type of diagnostic errors recorded in the db2diag.log file.

Diagnostic Directory Data Path

Enables you to specify the fully qualified path for DB2 diagnostic information.

Notify Level Raw

Specifies the type of administration notification messages written to the administration notification log.

Default Database System Monitor Switches

Unique metric that enables you to set several switches, each of which are internally represented by a bit of the metric.

Communications Bandwidth

Value calculated for the communications bandwidth in MB per second.

CPU Speed Raw

The CPU speed, in milliseconds per instruction, used by the SQL optimizer to estimate the cost of performing certain operations.

Maximum Number of Concurrently Active Databases

Specifies the number of local databases that can be concurrently active.

System Administration Authority Group Name

Defines the group name with SYSADM authority for the database manager instance.

Notify Level

If notifylevel_raw equals:

- 0 — No messages

- 1 — Fatal or unrecoverable errors

- 2 — All Immediate action required messages

- 3 — All Important information (no immediate action required)

Otherwise, All Informational messages.

CPU Speed

CPU speed in MIPs, which equals:

1/(cpuspeed_raw*1000)

2.3.4 Logging and Recovery

The metrics in this category save the logging and recovery information. Recovering your environment can be very important to prevent the loss of critical data. A number of parameters are available to help you manage your environment and to ensure that you can adequately recover your data or transactions.

- Table Name — MGMT_EMX_IBMDB2_DBMLOGREC

- View Name — MGMT_EMX_IBMDB2_DBMLOGREC_VIEW

Default Collection Interval — Every 24 hours

Table 2-6 Logging and Recovery Metrics

Metric

Description

Transaction Manager Database Name

Identifies the name of the transaction manager (TM) database for each DB2 instance.

Transaction Resync Interval

Specifies the time interval in seconds for which a transaction manager (TM), resource manager (RM), or sync point manager (SPM) should retry the recovery of any outstanding transactions in doubt found in the TM, RM, or SPM.

Sync Point Manager Name

Identifies the name of the sync point manager (SPM) instance to the database manager.

Sync Point Manager Log File Size

Identifies the sync point manager (SPM) log file size in 4 KB pages.

Sync Point Manager Resync Agent Limit

Identifies the number of agents that can simultaneously perform resync operations.

2.3.5 Partitioned Database Environment

The metrics in this category return parameters about parallel operations and partitioned database environments.

- Table Name — MGMT_EMX_IBMDB2_DBMPARENV

- View Name — MGMT_EMX_IBMDB2_DBMPARENV_VIEW

Default Collection Interval — Every 24 hours

Table 2-7 Partitioned Database Environment Metrics

Metric

Description

Connection Elapsed Time

Specifies the number of seconds within which a TCP/IP connection is to be established between two database partition servers.

Number of FCM Buffers

Specifies the number of 4 KB buffers used for internal communications (messages) both among and within database servers.

Node Connection Retries

max_connretries specifies the number of connection retries that can be made to a database partition server.

Maximum Time Difference Among Nodes

Each database partition server has its own system clock. This metric specifies the maximum time difference, in minutes, that is permitted among the database partition servers listed in the node configuration file.

Start and Stop Timeout

Applicable only in a partitioned database environment.

2.4 Health Indicators/Alarms

Health Indicators/Alarms metrics return the health information and current values for all the snapshot levels of containers, tablespaces, databases and the Database Manager. Health Indicators/Alarms metrics consist of the following categories:

- Container Health Indicator

- Container Health

- Database Health Indicator

- Database Health

- Database Manager Health Indicator

- Database Manager Health

- Tablespace Health Indicator

- Tablespaces Health

2.4.1 Container Health Indicator

The metrics in this category return health indicator information for tablespace containers from a health snapshot of tablespaces in a database.

Default Collection Interval — Every 30 minutes

Table 2-8 Container Health Indicator Metrics

Metric

Description and User Action

Container Health Indicator Additional Info

Additional information present in the Container Health Indicator metrics.

Container Health Indicator Alert State

State of the alert. If alert_state_raw equals:

1. Normal

2. Attention

3. Warning

4. Alarm

A warning or alarm condition indicates that you should examine the Health Indicator Alert Type.

Container Name

Name of the container.

Health Indicator Alert Type

Type of alert. If alert_state_raw equals:

- 3001 — Tablespace Container State

- 3002 — Tablespace Container Utilization

Health Indicator Identifier

Identifier for the alert.

Health Indicator Timestamp

Time when the alert was generated.

Health Indicator Value

Value for the alert.

Node Number

Node at which the alert was generated.

Snapshot Timestamp

Time when the query was executed.

2.4.2 Container Health

The metrics in this category return container information from a health snapshot of a database.

Default Collection Interval — Every 30 minutes

Table 2-9 Container Health Metrics

Metric

Description and User Action

Container Name (key column)

Name of the container.

Tablespace Name (key column)

Name of the tablespace to which the container belongs.

Node Number

Node at which the container resides.

Container Rolled Up Alert State

If alert_state_raw equals:

- 1 — Normal

- 2 — Attention

- 3 — Warning

- 4 — Alarm

Snapshot Timestamp

Time when the query was executed.

2.4.3 Database Collection Health Indicator

The metrics in this category return container information from a health snapshot of a database.

Default Collection Interval — Every 30 minutes

Table 2-10 Database Collection Health Indicator Metrics

Metric

Description and User Action

Health Indicator Object Detail

Description of the object.

Health Indicator Object Name

Name of the object.

Health Indicator Object State

Severity of the alert.

Health Indicator Object State Detail

Type of alert state. A warning or alarm condition indicates that the Health Indicator Alert Type should be examined.

2.4.4 Database Health

The metrics in this category return information from a health snapshot of a database.

Default Collection Interval — Every 30 minutes

Table 2-11 Database Health Metrics

Metric

Description and User Action

Database Name (key column)

Name of the database.

Database Path

Physical path of the database.

Logical Location of Database

Location of the database with respect to the DBM.

Database Rolled Up Alert State

If alert_state_raw equals:

- 1 — Normal

- 2 — Attention

- 3 — Warning

- 4 — Alarm

A warning or alarm condition indicates there are one or more alerts on the database.

Database Alias

Alias name for the database.

Server Platform

Platform where the database is installed.

Snapshot Timestamp

Time when the query was executed.

2.4.5 Database Health Indicator

The metrics in this category return health indicator information from a health snapshot of a database.

Default Collection Interval — Every 30 minutes

Table 2-12 Database Health Indicator Metrics

Metric

Description and User Action

Health Indicator Identifier

Unique identifier for Health Indicator.

Database Health Indicator Alert State

If alert_state_raw equals:

- 1 — Normal

- 2 — Attention

- 3 — Warning

- 4 — Alarm

A warning or alarm condition indicates that the Health Indicator Alert Type should be examined.

Health Indicator Alert Type

If alert_state_raw equals:

- 3001 — Tablespace Container State

- 3002 — Tablespace Container Utilization

Health Indicator Timestamp

Time when the alert was generated.

Health Indicator Value

Value for the alert.

Snapshot Timestamp

Time when the query was executed.

Database Health Indicator Additional Info

Additional information present in the Database Health Indicator metrics.

2.4.6 Database Manager Health

The metrics in this category return information from a health snapshot of the DB2 Database Manager.

Default Collection Interval — Every 30 minutes

Table 2-13 Database Manager Health Metrics

Metric

Description and User Action

Server Instance Name (key column)

Host name where DB2 is installed.

DB2 Startup Time

Time DB2 was last started.

Last Reset

Time DB2 was last reset.

Number of Nodes in DB2 Instance

Number of nodes in the DB2 instance.

Rolled up Alert State

If alert_state_raw equals:

- 1 — Normal

- 2 — Attention

- 3 — Warning

- 4 — Alarm

A warning or alarm condition indicates that the Health Indicator Alert Type should be examined.

Server Instance Name

Host name where DB2 is installed

Snapshot Timestamp

Time when the query was executed.

2.4.7 Database Manager Health Indicator

The metrics in this category return health indicator information from a health snapshot of the DB2 Database Manager.

Default Collection Interval — Every 30 minutes

Table 2-14 Database Manager Health Indicator Metrics

Metric

Description and User Action

Database Name (key column)

Name of the database.

DB Manager Health Indicator Additional Info

Additional information present in the DBM Health Indicator metrics.

DB Manager Health Indicator Alert State

If alert_state_raw equals:

- 1 — Normal

- 2 — Attention

- 3 — Warning

- 4 — Alarm

A warning or alarm condition indicates there are one or more alerts on the database.

Health Indicator Alert Type

If alert_state_raw equals:

- 3001 — Tablespace Container State

- 3002 — Tablespace Container Utilization

Health Indicator Identifier

Identifier for the alert.

Health Indicator Timestamp

Time when the alert was generated.

Health Indicator Value

Value for the alert.

Server Instance Name

Host name where DB2 is installed.

Snapshot Timestamp

Time when the query was executed.

Additional Information

Additional information present in the DBM Health Indicator metrics.

2.4.8 Tablespaces Health

The metrics in this category return health indicator information for tablespaces from a health snapshot of tablespaces in a database.

Default Collection Interval — Every 30 minutes

Table 2-15 Tablespaces Health Metrics

Metric

Description and User Action

Tablespace Name (key column)

Name of the tablespace.

Tablespace Rolled Up Alert State

If alert_state_raw equals:

- 1 — Normal

- 2 — Attention

- 3 — Warning

- 4 — Alarm

Snapshot Timestamp

Time when the query was executed.

2.4.9 Tablespace Health Indicator

The metrics in this category return health indicator information for tablespaces from a health snapshot of tablespaces in a database.

Default Collection Interval — Every 30 minutes

Table 2-16 Tablespace Health Indicator Metrics

Metric

Description and User Action

Tablespace Name (key column)

Name of the tablespace.

Tablespace Health Indicator Alert State

If alert_state_raw equals:

- 1 — Normal

- 2 — Attention

- 3 — Warning

- 4 — Alarm

Health Indicator Alert Type

If alert_state_raw equals:

- 3001 — Tablespace Container State

- 3002 — Tablespace Container Utilization

Health Indicator Identifier

Identifier for the alert.

Health Indicator Timestamp

Time when the alert was generated.

Health Indicator Value

Value for the alert.

Snapshot Timestamp

Time when the query was executed.

Tablespace Health Indicator Additional Info

Additional information present in the Tablespace Health Indicator metrics.

2.5 Monitoring Information

Monitoring Information metrics capture the monitoring information for the database, including general monitoring information, monitored values of the Agent, and monitored values of the database. Monitoring Information metrics consist of the following categories:

- Agent Monitoring

- Database Monitoring

- Database Backup Monitoring

- General Monitoring

2.5.1 Agent Monitoring

The metrics in this category return information about Agents from an application snapshot.

Default Collection Interval — Every 15 minutes

Table 2-17 Agent Monitoring Metrics

Metric

Description and User Action

Agent Cpu Utilization (%)

Total CPU utilization, which is equal to:

Agent_total_cpu_time-_Agent_total_cpu_time )/1000)/__interval)*100

Agent Identifier

Unique ID for each Agent.

Application Average Lock Wait Time (ms)

The average waiting time for locks, which equals:

lock_wait_time / lock_wait

If the average lock wait time is high, you should look for applications that hold many locks, or have lock escalations, with a focus on tuning your applications to improve concurrency, if appropriate. If escalations are causing a high average lock wait time, the values of one or both of the locklist and maxlocks configuration parameters may be too low.

Application Commit SQL Statements Rate

Commit SQL statements reading rate, which equals:

commit_sql_stmts - _commit_sql_stmts/ __interval

You can set the required value for the warning and critical thresholds to monitor any adverse conditions.

Application Dynamic SQL Statements Rate

Dynamic SQL statements reading rate, which equals:

dynamic_sql_stmts - _dynamic_sql_stmts/ __interval

You can set the required value for the warning and critical thresholds to monitor any adverse conditions.

Application Failed SQL Statements Rate

Failed SQL statements reading rate, which equals:

failed_sql_stmts - _failed_sql_stmts/ __interval

You can set the required value for the warning and critical thresholds to monitor any adverse conditions.

Application Identifier

Unique ID for each application.

Application Name

Name of the application.

Application Priority

Priority of Agents working for this application.

Application Rollback SQL Statements Rate

Rollback SQL statements reading rate, which equals:

rollback_sql_stmts - _rollback_sql_stmts/ __interval

You can set the required value for the warning and critical thresholds to monitor any adverse conditions.

Application Row Reading Rate

Rows reading rate in the last interval, which equals:

rows_read - rows_read)/__interval

You can set the required value for the warning and critical thresholds to monitor any adverse conditions.

Application Row Writing Rate

Rows writing rate in the last interval, which equals:

rows_written - rows_written)/__interval

You can set the required value for the warning and critical thresholds to monitor any adverse conditions.

Application Static SQL Statements Rate

Static SQL statements reading rate, which equals:

static_sql_stmts - _static_sql_stmts/ __interval

You can set the required value for the warning and critical thresholds to monitor any adverse conditions.

Application Status

Status of the application corresponding to the value of application_status_raw.

Authorization ID

Authorization ID of the user who invoked the application being monitored. On a DDCS gateway node, this is the user's authorization ID on the host.

Client Database Alias

Alias of the database provided by the application to connect to the database.

Client Name

Name of the Client for the database.

Client Node Number

client_nname in the database manager configuration file at the client node.

Commit SQL Statements

Number of commit SQL statements.

Coordinator Agent Process Identifier

Process ID (UNIX systems) or thread ID (Windows systems) of the coordinator Agent for the application.

Dynamic SQL Statements

Number of dynamic SQL statements.

Execution Identifier

ID that the user specified when logging in to the operating system. This ID is distinct from the Authorization ID, which the user specifies when connecting to the database.

Failed SQL Statements

Number of failed SQL statements.

Host CPU Usage Per Sec (%)

Ratio of CPU time utilized in the last interval, which equals:

elapsed_exec_time_ms - elapsed_exec_time_ms / __interval

You can set the required value for the warning and critical thresholds to monitor any adverse conditions.

IO Waits

IO wait time.

Number of Applications Waiting on Locks

Total number of applications that are currently waiting on locks.

Number of Deadlocks

Total number of deadlocks that have occurred.

Number of Exclusive Lock Escalations

Number of exclusive lock escalations.

Number of Lock Escalations

Number of lock escalations.

Number of Locks Held

Number of locks currently held by an application.

Number of Lock Timeouts

Number of lock timeouts for the application.

Number of Lock Waits

Number of times the application waited for locks.

Number of Rows Read

Total number of rows read.

Number of Rows Written

Total number of rows written.

Number of Sorts

Number of sorts performed by the statement.

Rollback SQL Statements

Number of rollback SQL statements.

Statement Average Sort Time (ms)

Average sort time per sort for the statement, which equals:

stmt_sorts/total_sort_time

Statement Rows Read

Total number of statement rows read

Statement Rows Written

Total number of statement rows written.

Statement Operation

Statement operation value.

Statement Start Time

Start time of the statement.

Statement Text

Whole query or the statement executed.

Statement Total Sort Time (ms)

Total time used for statement sorting.

Statement Total Sorts

Total number of sorts.

Statement Type

Number of requests to perform a direct write of one or more sectors of data.

Static SQL Statements

Number of static SQL statements.

System-CPU Time Used by Agent (ms)

Total system time used by DBM for processing by the Management Agent.

System-CPU Time Used by Statement (ms)

Total system time used by DBM for processing by the statement.

Total CPU Time Used Statement (ms)

Total CPU time, which equals:

stmt_usr_cpu_time + stmt_sys_cpu_time

Total CPU Time Used by Agent (ms)

Total CPU time, which equals:

agent_usr_cpu_time + agent_sys_cpu_time

Total Elapsed Execution Time (ms)

Sum of the host execution times (in milliseconds) for all the statements that were executed for a particular application.

Total Idle Time (ms)

Time spent idle for an application.

Total Lock Wait Time (ms)

Total time the application waited for locks.

Total Sort Time

Total time used for sorting.

User-CPU Time Used by Agent (ms)

Total user time used by DBM for processing by the Agent.

User-CPU Time Used by Statement (ms)

Total user time used by DBM for processing by a Statement.

Statement Hash

Hash of the SQL statement text.

2.5.2 Database Monitoring

The metrics in this category return snapshot information from the database and
detail_log logical data groups.

Default Collection Interval — Every 15 minutes

Table 2-18 Database Monitoring Metrics

Metric

Description

Database Name (key column)

Real name of the database for which information is collected or to which the application is connected. This is the name the database was given when created.

Allocated Database Size (Bytes)

Capacity of the database. (Not available in partitioned databases.)

Commit SQL Statements

Number of applications waiting for a lock on an object in the database. Number of commit SQL statements.

Database Average Lock Wait Time (ms)

Average waiting time for locks, which equals:

lock_wait_time/lock_wait

High wait for an application can mean that the application is degrading performance.

Number of Applications Waiting on Locks

Number of applications waiting for a lock on an object in the database.

Database Commit SQL Statements Rate

Commit SQL statement reading rate, which equals:

commit_sql_stmts - _commit_sql_stmts/ __interval

The required value can be set for the warning and critical thresholds to monitor any adverse conditions.

Database Connection Time

Last time the database was connected.

Database Dynamic SQL Statements Rate

Dynamic SQL statement reading rate, which equals:

dynamic_sql_stmts - _dynamic_sql_stmts/ __interval

The required value can be set for the warning and critical thresholds to monitor any adverse conditions.

Database Failed SQL Statements Rate

Failed SQL statement reading rate, which equals:

failed_sql_stmts - _failed_sql_stmts/ __interval

The required value can be set for the warning and critical thresholds to monitor any adverse conditions.

Logical Location of Database

Location of the database.

- Local — if db_location_raw = 0

- Remote — if db_location_raw = 1

Physical Disk Location of Database

Physical disk location of the database.

Database Rollback SQL Statements Rate

Rollback SQL statement reading rate, which equals:

rollback_sql_stmts - _rollback_sql_stmts/ __interval

The required value can be set for the warning and critical thresholds to monitor any adverse conditions.

Current Database Size (Bytes)

Size of the database in bytes.

Database Space Utilization (%)

Total percentage space utilization in the database, which equals:

(db_size/db_capacity) *100

Database Static SQL Statements Rate

Static SQL statement reading rate, which equals:

static_sql_stmts - _static_sql_stmts/ __interval

The required value can be set for the warning and critical thresholds to monitor any adverse conditions.

Database Status

Status of the database.

Database Deadlock Rate (per sec)

Rate of deadlocks, which equals:

(deadlocks-_deadlocks)/_interval

The required value can be set for the warning and critical thresholds to monitor any adverse conditions.

Dynamic SQL Statements

Number of dynamic SQL statements.

Database Exclusive Lock Escalation Rate (per sec)

Rate of exclusive lock escalations, which equals:

(x_lock_escals-_x_lock_escals)/__interval

The required value can be set for the warning and critical thresholds to monitor any adverse conditions.

Number of Exclusive Lock Escalations

Number of exclusive lock escalations.

Failed SQL Statements

Number of failed SQL statements.

Server Instance Name

Name of the database manager instance for which the snapshot was taken.

Database Internal Deadlock Rollback Rate (per sec)

Rate of internal deadlock rollbacks, which equals:

(int_deadlock_rollbacks-_int_deadlock_rollbacks) /__interval

The required value can be set for the warning and critical thresholds to monitor any adverse conditions.

Number of Lock Escalations

Number of lock escalations.

Database Lock Escalation Rate (per sec)

Rate of lock escalations, which equals:

(lock_escals-_lock_escals)/__interval

The required value can be set for the warning and critical thresholds to monitor any adverse conditions.

Number of Lock Timeouts

Number of lock timeouts for the application.

Database Lock Timeouts Rate (per sec)

Rate of lock timeouts. The required value can be set for the warning and critical thresholds to monitor any adverse conditions.

Number of Locks Held

Number of locks currently held by an application.

Number of Active Current Connections

Total number of active connections in the database.

Number of Current Connections

Total number of currently connected applications.

Number of Deadlocks

Total number of deadlocks that have occurred.

Number of Internal Rollbacks

Total number of internal deadlock rollbacks.

Number of Lock Waits

Number of times the application waited for locks.

Database Percentage of Applications Wating on Locks (%)

Ratio of applications waiting on locks, which equals:

(locks_waiting /appls_cur_cons)*100

If a large number of applications are waiting on locks, this indicates a possible performance degradation.

Rollback SQL Statements

Number of rollback SQL statements.

Static SQL Statements

Number of static SQL statements.

Total Lock Wait Time (ms)

Total time the application waited for locks.

Lock List in Use

Total lock list memory in use.

Last Reset

Time DB2 was last reset.

2.5.3 Database Backup Monitoring

The metrics in this category provide information about the last database backup.

Default Collection Interval — Every 2 hours

Table 2-19 Database Backup Monitoring Metrics

Metric

Description

Database Name (key column)

Real name of the database for which information is collected or to which the application is connected. This is the name the database was given when created.

Server Instance Name

Name of the database manager instance for which the snapshot was taken.

Last Backup Timestamp

Last backup of the database.

Days Since Last Backup

Amount of days since the last database backup.

2.5.4 General Monitoring

The metrics in this category provide basic information about the status of the databases and some of the important configuration values.

Default Collection Interval — Every 15 minutes

Table 2-20 General Monitoring Metrics

Metric

Description and User Action

DB2 Start Time

Date and time that the database manager was started using the db2start command.

Registered Agents

Number of Agents registered in the DBM instance that is being monitored.

Communication Private Memory

Amount of communication private memory.

Allocated Sort Heap Memory

Amount of memory allocated to sort heap.

Sort Heap Threshold (in 4 KB Pages)

Maximum number of private memory pages to be used for private sorts, or the maximum number of shared memory pages to be used for shared sorts. If the sort is a private sort, this parameter affects Agent private memory. If the sort is a shared sort, this parameter affects the database shared memory.

Each sort has a separate sort heap that is allocated as needed by the Database Manager. This sort heap is the area where data is sorted. If directed by the optimizer, a smaller sort heap than the one specified by this metric is allocated using information provided by the optimizer.

DB2 Status

Status of the DB2 instance. If DB2 status equals:

- 0 — Active

- 1 — Quiesce Pending

- 2 — Quiesced

Sort Heap Utilization (%)

Sort heap utilization, which equals:

sort_heap_allocated / sortheap_threshold *100

Increase the value of the sort heap threshold configuration parameter, sheapthres.

2.6 Performance

Performance metrics provide information regarding the performance at various snapshot levels. Performance metrics consist of the following categories:

- IO Agents Performance

- Database Performance

- Bufferpool Performance

- Cache Performance

- Log IO Performance

- Memory Manager Performance

- SortHeap Performance

- Non-Buffered I/O Performance

2.6.1 IO Agents Performance

The metrics in this category provide performance metrics for the Agent at the application snapshot level.

Default Collection Interval — Every 15 minutes

Table 2-21 IO Agents Performance Metrics

Metric

Description and User Action

Database Name (key column)

Real name of the database for which information is collected or to which the application is connected. This is the name the database was given when created.

Cleans for Steals

Number of times a page cleaner was invoked because a synchronous write was needed during the victim buffer replacement for the database.

Cleans for Threshold

Number of times a page cleaner was invoked because a buffer pool had reached the dirty page threshold criterion for the database.

2.6.2 Database Performance

The metrics in this category provide performance metrics for all the bufferpools in the database.

Default Collection Interval — Every 15 minutes

Table 2-22 Database Performance Metrics

Metric

Description and User Action

Database Name (key column)

Real name of the database for which information is collected or to which the application is connected. This is the name the database was given when created.

Average Data Write Rate

Pool data write rate, which equals:

(pool_data_writes/pool_write_time)

Average Index Write Rate

Pool index write rate, which equals:

(pool_index_writes/pool_write_time)

Average Page Read Rate

The rate equals:

(pool_data_p_reads + pool_index_p_reads )/pool_read_time

Average Page Write Rate

The rate equals:

(pool_data_writes + pool_index_writes)/pool_write_time

Database Buffer Pool Data Hit Ratio (%)

The ratio equals:((1-(pool_data_p_reads/pool_data_l_reads))*100))

Database Buffer Pool Hit Ratio (%)

The ratio equals:

(1-((pool_data_p_reads + pool_index_p_reads)/ (pool_data_l_reads + pool_index_l_reads )))*100

The required value can be set for the warning and critical thresholds to monitor any adverse conditions.

Logical Data Read Rate

The rate equals:

(pool_data_l_reads /pool_read_time)

Logical Index Read Rate

The rate equals:

(pool_index_l_reads /pool_read_time)

Physical Data Read Rate

The rate equals:

(pool_data_p_reads/pool_read_time)

Physical Index Read Rate

The rate equals:

pool_index_p_reads/pool_read_time

Physical Data Reads

Number of read requests that required I/O to get data pages into the buffer pool.

Physical Index Reads

Number of physical read requests to get index pages into the buffer pool.

Pool Asynchronous Data Reads

Number of data pages read asynchronously to the buffer pool by prefetchers.

Pool Asynchronous Data Writes

Number of times a buffer pool data page was physically written to disk by either an asynchronous page cleaner or a prefetcher.

Pool Asynchronous Index Reads

Number of index pages read asynchronously to the buffer poll by prefetchers.

Pool Asynchronous Index Writes

Number of times a buffer pool index page was physically written to disk by either an asynchronous page cleaner or a prefetcher.

Pool Asynchronous Read Time (microseconds)

Number of times a buffer pool data page was physically read from disk by an asynchronous page prefetcher.

Pool Asynchronous Write Time (microseconds)

Number of times a buffer pool index page was physically written to disk by either an asynchronous page cleaner or prefetcher.

Pool Data Writes

Number of times the buffer pool data page was physically written to the disk.

Pool Index Writes

Number of times the buffer pool index page was physically written to the disk.

Logical Data Reads

This count includes accesses to data that is already in the buffer pool when the database manager needs to process the page and read into the buffer pool before the database manager can process the page.

Logical Index Reads

Indicates the number of logical read requests to get index pages into the buffer pool.

Pool Read Time (microseconds)

Provides the total amount of elapsed time spent processing read requests that caused data or index pages to be physically read from disk to buffer pool.

Pool Write Time (microseconds)

Total amount of time spent physically writing data or index pages from the buffer pool to disk.

Synchronous Data Read Rate

Total synchronous read rate, which equals:

((pool_read_time-pool_async_read_time == 0) ? 0 :((pool_data_p_ reads-pool_async_data_reads )/(pool_read_time-pool_async_read_ time)))

Synchronous Data Write Rate

Pool data synchronous write rate, which equals:

((pool_data_writes - pool_async_data_writes )/(pool_write_time-pool_async_write_time))

Synchronous Index Read Rate

Index synchronous read rate, which equals:

(pool_data_p_reads-pool_async_index_reads )/(pool_read_time-pool_async_read_time)

Synchronous Index Write Rate

Index synchronous write rate, which equals:

((pool_index_writes - pool_async_index_writes )/(pool_write_time-pool_async_write_time))

2.6.3 Bufferpool Performance

The metrics in this category provide performance metrics for the individual bufferpools in the database.

Default Collection Interval — Every 15 minutes

Table 2-23 Bufferpool Performance Metrics

Metric

Description and User Action

Buffer Pool Name (key column)

Name of the buffer pool.

Individual Buffer Pool Hit Ratio (%)

Buffer pool hit ratio, which equals:

(1-((pool_data_p_reads + pool_index_p_reads)/
(pool_index_l_reads + pool_index_l_reads )))*100)

The required value can be set for the warning and critical thresholds to monitor any adverse conditions.

Database Name

Real name of the database for which information is collected or to which the application is connected. This is the name the database was given when created.

Database Alias

Alias for the database.

Database Path

Physical location of the database.

Pool Asynchronous Data Reads

Number of data pages read asynchronously to the buffer poll by prefetchers.

Pool Asynchronous Index Reads

Number of index pages read asynchronously to the buffer poll by prefetchers.

Pool Asynchronous Index Writes

Number of times a buffer pool index page was physically written to disk by either an asynchronous page cleaner or prefetcher.

Pool Asynchronous Read Time (microseconds)

Number of times a buffer pool data page was physically read from disk by an asynchronous page prefetcher.

Pool Asynchronous Write Time (microseconds)

Number of times a buffer pool index page was physically written to disk by either an asynchronous page cleaner or prefetcher.

Pool Data Writes

Number of times the buffer pool data page was physically written to the disk.

Pool Index Writes

Number of times the buffer pool index page was physically written to the disk.

Logical Data Reads

This count includes accesses to data that is already in the buffer pool when the database manager needs to process the page and read into the buffer pool before the database manager can process the page.

Logical Index Reads

Indicates the number of logical read requests to put index pages into the buffer pool.

Physical Index Reads

Indicates the number of physical read requests to put index pages into the buffer pool.

Physical Data Reads

Number of read requests that required I/O to put data pages into the buffer pool.

Pool Read Time (microseconds)

Provides the total amount of elapsed time spent processing read requests that caused data or index pages to be physically read from the disk to the buffer pool.

Pool Write Time (microseconds)

Total amount of time spent physically writing data or index pages from the buffer pool to the disk.

2.6.4 Cache Performance

The metrics in this category provide performance information for the package and the catalog cache of the database.

Default Collection Interval — Every 15 minutes

Table 2-24 Cache Performance Metrics

Metric

Description and User Action

Database Name (key column)

Real name of the database for which information is collected or to which the application is connected. This is the name the database was given when created.

Catalog Cache Hit Ratio (%)

Catalog cache hit ratio, which equals:

1-(cat_cache_inserts /cat_cache_lookups))*100

The hit ratio is a percentage indicating how well the catalog cache is helping to avoid actual accesses to the catalog on disk. A high ratio indicates it is successful in avoiding actual disk I/O accesses.

Catalogue Cache Heapfull

The number of times that an insert into the catalog cache failed due to a heap-full condition in the database heap.

Catalog Cache Inserts

Number of inserts performed. The hit ratio is 1-(CCI/CCL).

Catalog Cache Lookups

Number of times the catalog cache was referenced to obtain table description information.

Catalog Cache Overflows

Number of times that the catalog cache overflowed the bounds of its allocated memory.

Package Cache Hit Ratio (%)

Package cache hit ratio, which equals:

1-(pkg_cache_inserts /pkg_cache_lookups))*100

The hit ratio is a percentage indicating how well the package cache is helping to avoid reloading packages and sections for static SQL from the system catalogs as well as helping to avoid recompiling dynamic SQL statements. A high ratio indicates it is successful in avoiding these activities.

Package Cache Inserts

Number of inserts performed. The hit ratio is 1-(PCI/PCL).

Package Cache Lookups

Number of times the package cache was referenced to obtain a section or a package.

Package Cache Max Used (Bytes)

Largest size reached by the package cache.

Package Cache Overflows

Number of times that the package cache overflowed the bounds of its allocated memory.

2.6.5 Log IO Performance

The metrics in this category provide performance information for the log input and output including the number of reads and writes in the logs.

Default Collection Interval — Every 15 minutes

Table 2-25 Log IO Performance Metrics

Metric

Description

Database Name (key column)

Real name of the database for which information is collected or to which the application is connected. This is the name the database was given when created.

Cleans Logging Threshold

Number of times a page cleaner was invoked because the logging space used had reached a predefined criterion for the database.

Log Reads

Number of log reads.

Log Writes

Number of log writes.

2.6.6 Memory Manager Performance

The metrics in this category provide the values of the workspace provided and the locklist set in the database.

Default Collection Interval — Every 15 minutes

Table 2-26 Memory Manager Performance Metrics

Metric

Description

Database Name (key column)

Real name of the database for which information is collected or to which the application is connected. This is the name the database was given when created.

Private Workspace Max Used

Largest size that can be reached by private workspace.

Shared Workspace Max Used

Largest size that can be reached by shared workspace.

Locklist Current Value

Current value for the total amount of lock list memory that is in use.

2.6.7 SortHeap Performance

The metrics in this category display the various performance values associated with the sortheap.

Default Collection Interval — Every 15 minutes

Table 2-27 SortHeap Performance Metrics

Metric

Description and User Action

Database Name (key column)

Real name of the database for which information is collected or to which the application is connected. This is the name the database was given when created.

Active Sorts

Number of sorts in the database that currently have an allocated sort heap.

Average Active Sorts Rate

Rate of active sorts in the last interval, which equals:

(active_sorts-_active_sorts )/__interval

The required value can be set for the warning and critical thresholds to monitor any adverse conditions.

Average Hash Join Overflow Rate

Rate of hash join overflow in the last interval, which equals:

(hash_join_overflow-_hash_join_overflow) /__interval

The required value can be set for the warning and critical thresholds to monitor any adverse conditions.

Average Hash Join Rate

Rate of hash joins in the last interval, which equals:

(total_hash_joins-_total_hash_joins )/__interval

The required value can be set for the warning and critical thresholds to monitor any adverse conditions.

Average Hash Join Small Overflow Rate

Rate of the small hash join overflow in the last interval, which equals:

(hash_join_small_overflow-_hash_join_small_overflow )/__interval

The required value can be set for the warning and critical thresholds to monitor any adverse conditions.

Average Hash Join Small Overflow Ratio

Ratio of the small hash join overflow.

Average Hash Loops Rate

Rate of hash loops in the last interval, which equals:

(total_hash_loops-_total_hash_loops)/ __interval

The required value can be set for the warning and critical thresholds to monitor any adverse conditions.

Average Piped Sorts Rejected Rate

Rate of piped sorts rejection in the last interval, which equals:

((piped_sorts_requested-piped_sorts_accepted)-(_piped_sorts_requested-_piped_sorts_accepted)) /__interval

The required value can be set for the warning and critical thresholds to monitor any adverse conditions.

Average Post Threshold Joins Rate

Rate of post threshold joins in the last interval, which equals:

(post_threshold_ hash_joins-_post_threshold _hash_joins)/__interval

The required value can be set for the warning and critical thresholds to monitor any adverse conditions.

Average Post Threshold Sorts Rate

Rate of post threshold sorts in the last interval, which equals:

(post_threshold_sorts-_post_threshold_sorts ) /__interval

The required value can be set for the warning and critical thresholds to monitor any adverse conditions.

Average Sort Heap Pages Used

Average sort heap space used, which equals:

(sort_heap_allocated /active_sorts)

The required value can be set for the warning and critical thresholds to monitor any adverse conditions.

Average Sort Time (ms)

Average time per sort, which equals:

(total_sort_time/total_sorts)

The required value can be set for the warning and critical thresholds to monitor any adverse conditions.

Average Sorts Overflow Rate

Rate of sort overflow in the last interval, which equals:

(sort_overflow-_sort_overflow )/__interval

The required value can be set for the warning and critical thresholds to monitor any adverse conditions.

Average Sorts Overflow Ratio

Ratio of sort overflow.

Average Sorts Rate

Rate of sorts in the last interval, which equals:

(total_sorts-_total_sorts )/__interval

The required value can be set for the warning and critical thresholds to monitor any adverse conditions.

Piped Sorts Accepted

Number of piped sorts that have been accepted.

Piped Sorts Requested

Number of piped sorts that have been requested.

Post Threshold Hash Joins

Total number of times that a hash join heap request was limited due to concurrent use of shared or private sort heap space.

Post Threshold Sorts

Number of sorts that have requested heaps after the sort heap threshold has been exceeded.

Sort Heap Pages Allocated

Total number of allocated pages of sort heap space for all sorts at the level chosen and at the time the snapshot was taken.

Sort Overflows

Total number of sorts than ran out of sort heap and may have required disk space for temporary storage.

Total Hash Join Overflows

Number of times that hash join data exceeded the available sort heap space.

Total Hash Join Small Overflow

Number of times that hash join data exceeded the available sort heap space by less than 10%.

Total Hash Joins

Total number of hash joins executed.

Total Hash Loops

Total number of hash loops executed.

Total Sorts

Number of sorts that have been executed.

Total Sort Time (ms)

Time spent in sorts.

2.6.8 Non-Buffered I/O Performance

The metrics in this category display the various performance values related to the non-buffered I/O activities that do not use the buffer pool.

Default Collection Interval — Every 15 minutes

Table 2-28 Non-Buffered IO Performance Metrics

Metric

Description

Database Name (key column)

Real name of the database for which information is collected or to which the application is connected. This is the name the database was given when created.

Average Data Read Rate

Direct read rate, which equals:

direct_reads/direct_read_time

The required value can be set for the warning and critical thresholds to monitor any adverse conditions.

Average Direct Write Rate

Direct write rate, which equals:

direct_writes/direct_write_time

The required value can be set for the warning and critical thresholds to monitor any adverse conditions.

Direct Read Requests

Number of requests to perform a direct read of one of more sectors of data.

Direct Read Time (ms)

Elapsed time in milliseconds required to perform the direct reads.

Direct Reads

Number of read operations that do not use the buffer pool.

Direct Write Requests

Number of requests to perform a direct write of one or more sectors of data.

Direct Write Time (ms)

Elapsed time in milliseconds required to perform the direct writes.

Direct Writes

Number of write operations that do not use the buffer pool.

2.7 Response

The metrics in this category provide information about the response of the IBM DB2 database in the instance.

Default Collection Interval — Every 5 minutes

Table 2-29 Response Metrics

Metric

Description

Database Name

Name of the database.

DB Status

Status of the database:

- 0 — Active

- 1 — Quiesce pending

- 2 — Quiesced

- 3 — Roll forward

DB Status Value

Status value of the database.

DB2 Status

Status value of the DB2 instance.

DB2 Status Value

Status of the DB2 instance:

- 0 — Active

- 1 — Quiesce pending

- 2 — Quiesced

Server Instance Name

Database instance type.

Status

Status of the database. The database is up if the status is 0. Otherwise, it is down.

2.8 Detailed Response

This metric category provides information about the response of the instance.

Default Collection Interval — Every 5 minutes

Table 2-30 Detailed Response Metrics

Metric

Description

Database Name

Name of the database.

DB2 Status

Status value of the DB2 instance.

Table Type

Type of table to which the file belongs.

DB Status

Status of the database.

Server Instance Name

Name of the server instance.

2.9 Storage Information

Storage Information metrics provide information about the storage objects, such as the tablespace and data files. Storage Information metrics consist of the following categories:

- Data Files Storage

- Log Storage

- Tablespace Storage

2.9.1 Data Files Storage

The metrics in this category provide information about the file properties for the database data storage files.

Default Collection Interval — Every 2 hours

Table 2-31 Data Files Storage Metrics

Metric

Description

Data File Identifier (key column)

Unique identifier for the data file.

Table Name

Name of the particular table in the database where the file resides.

Table Schema

Schema of the table in which the file resides.

Table Type

Type of table to which the file belongs.

Page Reorganizations

Number of page reorganizations.

Overflow Accesses

Number of overflow accesses.

2.9.2 Log Storage

The metrics in this category provide information about the log storage properties for the database.

Default Collection Interval — Every 30 minutes

Table 2-32 Log Storage Metrics

Metric

Description and User Action

Database Name (key column)

Real name of the database for which information is collected or to which the application is connected. This is the name the database was given when created.

Allocated Secondary Log Size

Allocated size of the secondary log.

Log Space Utilization (%)

Space utilization of log files.

Total Log Available (in 4Kb Pages)

Total number of pages available for logging.

Total Log Used (in 4Kb Pages)

Total number of pages used for logging.

2.9.3 Tablespace Storage

The metrics in this category provide information about the individual tablespace properties for all database tablespaces.

Default Collection Interval — Every 15 minutes

Table 2-33 Tablespace Storage Metrics

Metric

Description

Tablespace Identifier (key column)

Identifier for the tablespace.

Tablespace Name (key column)

Name of the tablespace.

Bufferpool Identifier

Bufferpool identifier for the tablespace.

Extent Size

Extent size for the tablespace.

Number of Containers

Number of containers.

Number of Ranges

Number of ranges.

Page Size

Page size for the tablespace.

Prefetch Size

Prefetch size for the tablespace.

Tablespace Free Pages

Number of pages in a tablespace that will become free if all pending transactions are committed or rolled back, and new space is requested for an object.

Tablespace State

State of the tablespace.

Tablespace Total Pages

Total number of pages in a tablespace.

Tablespace Type

Type of tablespace.

Tablespace Usable Pages

Total number of pages in a tablespace minus overhead pages.

Tablespace Used Pages

Total number of pages currently used (not free) in a tablespace.

Tablespace Utilization (%)

Tablespace utilization in percentage.

2.10 System Configuration Information

System Configuration Information metrics collect the information related to the database software. Each installation of the database software provides an instance to store the data. System Configuration Information metrics consist of the following categories:

- Database System Information

- Instance Information

- Partition Information

- Product Information

- Registry Settings

2.10.1 Database System Information

The metrics in this category provide information about the various system configuration metrics for the database system, including the name and operating system properties.

- Table Name — MGMT_EMX_IBMDB2_DBSYS

- View Name — MGMT_EMX_IBMDB2_DBSYS_VIEW

Default Collection Interval — Every 24 hours

Table 2-34 Database System Information Metrics

Metric

Description

Server Name

Name of the database server.

OS Type

Type of the operating system.

OS Version

Version of the operating system.

OS Release

Release of the operating system.

Total Number of CPUs

Total number of CPUs for the operating system on which the database is installed.

Total Number of Configured CPUs

Total number of configured CPUs on which the database is installed.

Total Memory (mb)

Amount of memory.

2.10.2 Instance Information

The metrics in this category return the parameters that provide information about database instances.

- Table Name — MGMT_EMX_IBMDB2_DBINST

- View Name — MGMT_EMX_IBMDB2_DBINST_VIEW

Default Collection Interval — Every 24 hours

Table 2-35 Instance Information Metrics

Metric

Description

Instance Name (key column)

Name of the instance.

Number of DB Partitions

Number of database partitions.

Bit Size of Current Instance

Bit size of the current instance (32 or 64).

Release Number

Internal release number, as returned by the db2level command; 9 for example, 03030106.

Service Level

Service level, as returned by the db2level command; for example, DB2 v8.1.1.80.

Build Level

Build level, as returned by the db2level command; for example, n041021.

Program Temporary Fix

Program temporary fix (PTF) identifier, as returned by the db2level command; for example, U498350.

Fix Pack Number

FixPak number, as returned by the db2level command.

2.10.3 Product Information

The metrics in this category provide information about the installed IBM DB2 database product.

- Table Name — MGMT_EMX_IBMDB2_DBPRO

- View Name — MGMT_EMX_IBMDB2_DBPRO_VIEW

Default Collection Interval — Every 24 hours

Table 2-36 Product Information Metrics

Metric

Description

Database Path

Installation path of the database.

Product

Installed product.

Version

Version of the installed product.

2.10.4 Partition Information

The metrics in this category return parameters about parallel operations and partitioned database environments.

- Table Name — MGMT_EMX_IBMDB2_DBPART

- View Name — MGMT_EMX_IBMDB2_DBPART_VIEW

Default Collection Interval — Every 24 hours

Table 2-37 Partition Information Metrics

Metric

Description

Partition Number (key column)

Partition number where the database is installed.

Host Name

Host name of the machine where the db2 database software is installed.

Logical Port

TCP/IP port number to communicate with the database.

Switch Name

Name of the switch where the database is connected.

2.10.5 Registry Settings

The metrics in this category provide information about the various registry parameters for the database.

- Table Name — MGMT_EMX_IBMDB2_DBREGSET

- View Name — MGMT_EMX_IBMDB2_DBREGSET_VIEW

Default Collection Interval — Every 24 hours

Table 2-38 Registry Settings Metrics

Metric

Description

Database Registry Variable (key column)

Name of the DB2 registry variable.

Current Value

Current setting of the DB2 registry variable.

Is Aggregate

Indicates whether or not the DB2 registry variable is an aggregate variable. Possible return values are 0 if it is not an aggregate variable, and 1 if it is an aggregate variable.

Aggregate Name

Name of the aggregate if the DB2 registry variable is currently getting its value from a configured aggregate. If the registry variable is not being set through an aggregate, or is set through an aggregate but has been overridden, the value of AGGREGATE_NAME is NULL.

Level

Values are:

Instance — If level1_raw = I
Global — If level1_raw = G
Database Partition — If level1_raw = N

Otherwise, the value is Environment.

2.11 DB2 Diag Log File Monitoring

The metrics in this category provide information about the IBM DB2 database Diagnostic Log file.

Note:
This metric category is supported only for local monitoring, that is, when the IBM DB2 database on a host is monitored by an Oracle Management Agent that is running on the same host.

Default Collection Interval — Every 5 minutes

Table 2-39 DB2 Diag Log File Monitoring Metrics

Metric

Description

Log File Match Count

Number of times a pattern was found in the log file.

Server

Name of the server where IBM DB2 is running.

Instance

Name of the IBM DB2 instance.

DB

Name of the IBM DB2 database.

Function

Name of the function present in the last log entry.

Last Occurrence Time Stamp

Time of the last log entry that shares a common function name with other entries that satisfy a particular pattern.

Message

Message present in the log file.

2.12 HADR Status Metrics

The metrics in this category provide details about the HADR and HADR cluster status and configuration.

Default Collection Interval — Every 30 minutes

Table 2-40 HADR Status Metrics

Metric

Description

Database Name

Database Name monitor element

HADR Connect Status

Text identifier detailing the status of the HADR connection:

- CONGESTED

- CONNECT

- DISCONNECT

HADR Connect Time

The last time the connect status changed (the time it first became congested or disconnected).

HADR Heartbeat

The number of missed heartbeats on the HADR connection. Typically, the higher the value, the worse the condition.

HADR Local Host

The number of missed heartbeats on the HADR connection. Typically, the higher the value, the worse the condition.

HADR Local Service

The number of missed heartbeats on the HADR connection. Typically, the higher the value, the worse the condition.

HADR Primary Log File

The number of missed heartbeats on the HADR connection. Typically, the higher the value, the worse the condition.

HADR Primary Log LSN

The current log position of the primary HADR database. The log sequence number (LSN) is a byte offset in the database log stream.

HADR Primary Log Page

The current page number in the current log file

HADR Remote Host

The remote host name

HADR Remote Instance

The remote HADR instance name

HADR Remote Service

The remote HADR service name

HADR Role

Text identifier detailing the role of the monitored database. Possible values are:

- PRIMARY

- STANDAR

- STANDBY

If the role is standard, then ignore all other metrics

HADR Standby Log

The current log file on the standby HADR database.

HADR State

Text identifier detailing the state of HADR. Possible values are:

- DISCONNECTED

- LOCAL_CATCHUP

- PEER

- REM_CATCH_PEN

- REM_CATCHUP

HADR Sync Mode

Text identifier detailing sync mode. Possible values are:

- ASYNC

- NEARSYNC

- SYNC

HADR Timeout

The number of seconds before the HADR Database Server considers that a communication attempt has failed,

2.13 Top Statements by CPU Time Metrics

The metrics in this category provide details about the SQL statements that use the most processor time.

Default Collection Interval - Every 30 minutes

Table 2-41 Top Statements by CPU Time Metrics

Metric

Description

Statement Hash

A hash of the SQL statement text.

Statement

The SQL statement executed.

Total CPU Time (s)

The amount of time the statement was executing while Enterprise Manager collected data.

CPU Time per hour (s)

The average amount of time the statement is executing, per hour.

2.14 Top Statements by Execution Count Metrics

The metrics in this category provide details about the SQL statements that are executed most frequently.

Default Collection Interval - Every 30 minutes

Table 2-42 Top Statements by Execution Count Metrics

Metric

Description

Statement Hash

A hash of the SQL statement text.

Statement

The SQL statement executed.

Total Execution Count

The number of times the statement executed while Enterprise Manager collected data.

Total Execution Count per Hour

The average number of times the statement executes, per hour.

2.15 Lock Waits Metrics

The metrics in this category provide information about database locks.

Default Collection Interval - Every 30 minutes

Table 2-43 Lock Waits Metrics

Metric

Description

Lock Request Agent ID

The ID of the agent waiting on a lock.

Lock Name

The name of the lock being waited on.

Lock Object Type

The object type of the lock being waited on.

Locked Schema

The schema containing the locked table. No value if the lock does not reference a table.

Locked Table

The locked table being waited on. No value if the lock does not reference a table.

Lock Wait Duration (s)

The time, in seconds, this agent has been waiting for access.

Application Requesting Lock

The name of the application wanting to acquire the lock.

User Requesting Lock

The authorization ID of the session that wants to acquire the lock.

Application Holding Lock

The application holding the lock blocking access.

User Holding Lock

The authorization ID of the session holding the lock blocking access

SQL Statement Requesting Lock

The SQL statement text being executed by the application requesting the lock. Only available on on versions of DB2 9.7 and later.

SQL Statement Holding Lock

The SQL statement text associated with the application holding the lock. This is not necessarily the statement responsible for the lock. Only available on versions of DB2 9.7 and later.

2.16 Lock Waits by Table Metrics

The metrics in this category describe the tables being most waited on.

Default Collection Interval - Every 30 minutes

Table 2-44 Lock Waits by Table Metrics

Metric

Description

Schema

The schema containing the locked table.

Table

The locked table.

Total Lock Wait Duration

The sum of the wait times for every agent waiting to access the table.

2.17 Lock Waits by Blocked Application Metrics

The metrics in this category describe the applications that are waiting the longest for locks.

Default Collection Interval - Every 30 minutes

Table 2-45 Lock Waits by Blocked Application Metrics

Metric

Description

Application

The application waiting on a lock.

Application ID

Application identifier

Total Lock Wait Duration

The sum of the wait times for every agent in the application.

2.18 Lock Waits by Blocking Application Metrics

The metrics in this category describe the applications that are holding a lock on which other applications are waiting.

Default Collection Interval - Every 30 minutes

Table 2-46 Lock Waits by Blocking Application Metrics

Metric

Description

Application

The application waiting on a lock.

Application ID

Application identifier.

Total Lock Wait Duration

The sum of the wait times for every application waiting on the lock.

3 Sybase Adaptive Server Enterprise Database Metrics

This chapter provides descriptions for Sybase Adaptive Server Enterprise Database Plug-In metric categories, and the tables list and describe associated metrics for each category.

- About Sybase Adaptive Server Enterprise Database Metrics

- Viewing Metrics

- Adaptive Server Engines Statistics

- Adaptive Server State

- Adaptive Server Statistics

- Cache Pools Statistics

- Cached Procedures Statistics

- Connection Statistics

- Currently Executing Queries

- Currently Executing SQL Text Information

- Data Caches Statistics

- Database Indexes

- Database Logins

- Database Login Roles

- Database Objects

- Database Segments

- Database Thresholds

- Database Transactions

- Databases Instances

- Deadlock Statistics

- Device Data and IO Log Statistics

- Devices Statistics

- General Statistics

- Locks Information

- Most Recent Error Messages

- Most Recently Executed Statement Statistics

- Network I/O Statistics

- Open Databases Statistics

- Open Objects Statistics

- Procedure Cache Statistics

- Process Network IO Activity

- Process Objects Information

- Process Statistics

- Process Tracking Details

- Processes Activity Statistics

- Response

- Recently Executed SQL Text

- Running Procedures Statistics

- Segment Usages

- Server-Wide Worker Threads Statistics

- Top SQL Statements by CPU Time

- Top SQL Statements by Wait Time

- Top SQL Statements by Memory Usage

- Top Ten Big Cached Objects

- Top Ten Frequently Accessed Cached Objects

- Transaction Logs

- Wait Class Event Information

- Wait Events Information

- Waiting Events Statistics

- Waiting Process Statistics

- Configuration Metrics

3.1 About Sybase Adaptive Server Enterprise Database Metrics

This chapter covers the metric categories that appear for all releases of System Monitoring Plug-In for Sybase Adaptive Server Enterprise Database. If you see a release number against a metric name, then it indicates that the metric appears only for that particular release of the plug-in. Otherwise, the metric appears for all releases of the plug-in. For example, if you see For Release 6, then it indicates that the metric System Monitoring Plug-in Metric Reference Manual for Non Oracle Database Management appears only for System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 6 (1.0.1.0.0).

Note that in Release 9, the metric collection is disabled for the following metrics:

- Database Indexes

- Database Objects

- Database Thresholds

- Database Transactions

Note that in Release 11, the metric collection is disabled for the following metrics:

- Database Indexes

- Database Objects

- Database Thresholds

- Database Transactions

- Database Segments

- Process Objects Information

- Process Statistics

- Process Tracking Details

- Segment Usages

- Running Procedure Statistics

- Wait Class Event Information

- Wait Events Information

- Wait Process Statistics

To enable these metrics:

1. In Enterprise Manager Cloud Control, on the plug-in Home page, from the Related Links section, click Metric and Policy Settings.

2. On the Metric and Policy Settings page, from the View list, select All metrics. In the table that lists all the metrics, in the Collection Schedule column, you will see Disabled mentioned for some metrics.

3. Click Disabled. Enterprise Manager Cloud Control displays the Edit Collection Settings page.

4. On the Edit Collection Settings page, in the Collection Schedule section, click Enable and then Continue. Enterprise Manager Cloud Control displays the Metric and Policy Settings page with the metric enabled.

5. On the Metric and Policy Settings page, click OK.

3.2 Viewing Metrics

To view configuration metrics within the plug-in, navigate to the last collected configuration metrics page by selecting Configuration, then Last Collected from Sybase ASE.

Figure 3-1 Viewing Configuration Metrics

To view the performance and status metrics within the plug-in by navigating to the All Metrics page by selecting Monitoring, then All Metrics from Sybase ASE.

Figure 3-2 Viewing All Metrics Page

3.3 Adaptive Server Engines Statistics

The metrics in this category provide statistics regarding Adaptive Server engines.

Collection Frequency - Every 30 minutes.

Table 3-1 Adaptive Server Engines Statistics Metrics

Metric

Description and User Action

Number of CPU that the engine is affinited to

The number of CPUs that the engine affinited to.

For Release 6

Number of handled connections

For Release 7

Engine's connections handle rate (per sec)

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 6 (1.0.1.0.0)

Number of connections handled since last collection.

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 7 (1.1.1.0.0)

Rate at which the connections were handled since last collection, that is, the number of connections handled since last collection divided by the elapsed time between two collections.

For Release 6

Number of context switches

For Release 7

Engine's context switch rate (per sec)

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 6 (1.0.1.0.0)

Number of context switches handled since last collection.

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 7 (1.1.1.0.0)

Rate of context switches handled since last collection, that is, the number of context switches handled since last collection divided by the elapsed time between two collections.

For Release 6

Total Time Engine Running (seconds)

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 6 (1.0.1.0.0)

Total time (in seconds) the engine has been running.

Current KPID

Kernel process identifier for the currently executing process.

For Release 6

\Total time engine has been in idle spin mode (seconds)

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 6 (1.0.1.0.0)

Time (in seconds) the engine has been in idle spin mode.

OS Process ID

OS Process ID.

Number of affinited processes to engine

Number of processes that have been affinitied to this engine.

Engine came online (Date)

Date that the engine came online.

Engine Status

Status of the engine (online, offline, and so on).

Engine went offline (Date)

Date that the engine went offline.

For Release 6

Total time engine executing system database services (seconds)

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 6 (1.0.1.0.0)

Time (in seconds) the engine has been executing system database services.

Engine CPU (for System DB Services) Utilization %

Engine CPU Utilization (System DB Services).

For Release 6

Total time engine executing user commands (seconds)

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 6 (1.0.1.0.0)

Time (in seconds) the engine has been executing user commands.

Engine CPU (for User Commands) Utilization %

Engine CPU Utilization (for User Commands)

Engine Number

Number of the Sybase ASE engine

Number of processes affinited to engine

Number of processes with affinity to the engine

Previous KPID

Kernel process identifier for the previous executing process.

3.4 Adaptive Server State

The metrics in this category provide information regarding the overall state of Adaptive Server.

Collection Frequency - 30 minutes

Table 3-2 Adaptive Server State Metrics

Metric

Description

Default Warning Threshold

Default Critical Threshold

Alert Text

Number of processes waited longer than lock wait threshold

Number of processes that have waited longer than LockWaitThreshold seconds.

Not Defined

Not Defined

The total number of processes waited longer than lock wait threshold are %LockWaits%. It has crossed warning (%warning_threshold%) or critical (%critical_threshold%) threshold.

Number of deadlocks occured since last collection

The number of deadlocks that have occured since the last collection.

Not Defined

Not Defined

The total total number of occured deadlocks are %NumDeadlocks%. It has crossed warning (%warning_threshold%) or critical (%critical_threshold%) threshold.

Table 3-3 Adaptive Server State Metrics

Metric

Description

Checkpoints

Reports whether any checkpoint is currently running.

Number of active inbound connections

Number of active inbound connections.

Monitoring counters clearing date

Date and time the monitor counters were last cleared.

Number days Adaptive Server has been running

Number of days Adaptive Server has been running

Diagnostic Dumps

Reports whether sybmon is performing a shared memory dump

Lock wait threshold (seconds)

Time (in seconds) that processes must have waited for locks in order to be reported.

Max Recovery time per database (minutes)

The maximum time (in minutes), per database, that Adaptive Server uses to complete its recovery procedures in case of a system failure. Also, the current Run Value for recovery interval in minutes.

Total number of occurred deadlocks

Total number of deadlocks that have occurred.

Adaptive Server started date

Date and time that Adaptive Server was started.

3.5 Adaptive Server Statistics

The metrics in this category provide statistics about the ASE adaptive server.

Collection frequency - Every 30 minutes

Table 3-4 Adaptive Server Statistics Metrics

Metric

Description and User Action

For Release 6

Number of logins or attempted logins to SQL Server

For Release 7

Attempted logins (per sec)

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 6 (1.0.1.0.0)

The number of logins or attempted logins to the Server since last collection.

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 7 (1.1.1.0.0)

Rate at which the logins or attempted logins were made to the Server, that is, the number of logins or attempted logins to the Server since last collection divided by the elapsed time between two collection.

Sybase ASE (Overall) CPU Utilization (%)

Overall CPU Utilization (%) of Sybase ASE.

Sybase ASE (Overall) IO Utilization (%)

Overall IO Utilization (%) of Sybase ASE.

For Release 6

Number of errors detected by SQL Server while reading and writing packets

For Release 7

Detected packet (rcvd/sent) error rate (per sec)

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 6 (1.0.1.0.0)

The number of errors detected by the server while reading and writing packets since last collection.

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 7 (1.1.1.0.0)

Rate at which the errors were detected by the server while reading and writing packets since last collection, that is, the number of errors detected by the server while reading and writing packets since last collection divided by the elapsed time between two collection.

For Release 6

Number of input packets read by SQL Server

For Release 7

Input packets read rate by AS (per sec)

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 6 (1.0.1.0.0)

The number of input packets read by the server since last collection.

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 7 (1.1.1.0.0)

Rate at which the input packets were read by the server since last collection, that is, the number of input packets read by the server since last collection divided by the elapsed time between two collection.

For Release 6

Number of output packets written by SQL Server

For Release 7

Output packets write rate by AS (per sec)

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 6 (1.0.1.0.0)

The number of output packets written by the server since last collection.

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 7 (1.1.1.0.0)

Rate at which the output packets were written by the server since last collection, that is, the number of output packets written by the server since last collection divided by the elapsed time between two collection.

For Release 6

Number of connections handled since last collection

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 6 (1.0.1.0.0)

The number of connections handled since last collection.

For Release 6

Number of errors detected by SQL Server while reading and writing

For Release 7

Detected disk read/write error rate (per sec)

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 6 (1.0.1.0.0)

The number of errors detected by SQL Server while reading and writing since last collection.

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 7 (1.1.1.0.0)

Rate at which errors detected by the server while reading and writing since last collection, that is, the number of errors detected by the server while reading and writing divided by the elapsed time between two collection.

For Release 6

Number of disk reads by SQL Server

For Release 7

Read rate from disk (per sec)

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 6 (1.0.1.0.0)

The number of disk reads by the server since last collection.

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 7 (1.1.1.0.0)

Rate at which the disk was read by the server since last collection, that is, number of disk reads by the server since last collection divided by the elapsed time between two collection.

For Release 6

Number of disk writes by SQL Server

For Release 7

Write rate to disk (per sec)

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 6 (1.0.1.0.0)

The number of disk writes by the server since last collection.

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 7 (1.1.1.0.0)

Rate at which the disk was written by the server since last collection, that is, the number of disk writes by the server since last collection divided by the elapsed time between two collection.

3.6 Cache Pools Statistics

The metrics in this category provide statistics for all pools allocated for all caches.

Collection frequency - Every 60 minutes

Table 3-5 Cached Pools Statistics Metrics

Metric

Description

Allocated bytes for pool (Bytes)

Number of bytes that have been allocated for the pool.

For Release 6

Number of buffers in the least recently used portion

For Release 7

Buffers Fetch & Replacement Rate at LRU Portion of Pool (per sec)

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 6 (1.0.1.0.0)

The number of buffers that were fetched and replaced in the least recently used portion of the pool since last collection.

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 7 (1.1.1.0.0)

Rate at which the buffers were fetched and replaced in the least recently used portion of the pool since last collection, that is, the number of buffers that were fetched and replaced since last collection divided by the elapsed time between two collections.

For Release 6

Number of buffers in the most recently used portion

For Release 7

Buffers fetch & replacement rate at MRU portion of pool(per sec)

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 6 (1.0.1.0.0)

The number of buffers that were fetched and replaced in the most recently used portion of the pool since last collection.

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 7 (1.1.1.0.0)

Rate at which the buffers were fetched and replaced in the most recently used portion of the pool since last collection, that is, the number of buffers that were fetched and replaced since last collection divided by the elapsed time between two collections.

For Release 6

Number of pages read into the pool

For Release 7

Page read rate into the pool (per sec)

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 6 (1.0.1.0.0)

Number of pages read into the pool since last collection.

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 7 (1.1.1.0.0)

Rate at which the pages were read into the pool since last collection, that is, the number of pages read into the pool since last collection divided by the elapsed time between two collections.

For Release 6

Number of bytes that are currently being used within the pool

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 6 (1.0.1.0.0)

Number of bytes that are currently being used within the pool.

For Release 6

Number of buffers that have been read from disk into the pool

For Release 7

Buffers read rate from the disk into the pool (per sec)

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 6 (1.0.1.0.0)

Number of buffers that have been read from the disk into the pool since last collection.

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 7 (1.1.1.0.0)

Rate at which the buffers were read from the disk into the pool since last collection, that is, the number of buffers that were read from the disk into the pool since last collection divided by the elapsed time between two collections.

For Release 6

Number of dirty buffer retrievals

For Release 7

Dirty buffer retrievals rate from the pool (per sec)

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 6 (1.0.1.0.0)

Number of dirty buffer retrievals since last collection.

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 7 (1.1.1.0.0)

Rate at which the dirty buffers were retrieved since last collection, that is, the number of dirty buffer retrievals since last collection divided by the elapsed time between two collections.

Cache ID

Cache identifier.

IO Buffer Size

Size (in bytes) of the I/O buffer for the pool.

Cache Name

Name of the cache.

3.7 Cached Procedures Statistics

The metrics in this category provide statistics for all procedures currently stored in the procedure cache.

For every collection, only the top 10 rows sorted on Memory Usage are selected.

Collection frequency - Every 30 minutes

Table 3-6 Cached Procedures Statistics Metrics

Metric

Description

Procedure Compiled Date

Date that the procedure was compiled.

DB Name

Name of the database.

Memory Usage (KB)

Number of kilobytes of memory used by the procedure.

Procedure Name

Name of the object.

Procedure Type

The type of procedure (stored procedure, trigger, and so on).

Owner Name

Name of the object owner.

Procedure ID

Unique identifier for the procedure.

Owner UID

Owner user ID.

DBID

Database identifier.

Query Plan ID

Unique identifier for the query plan.

3.8 Connection Statistics

The metrics in this category provide information regarding the number of available and used connections.

Collection Frequency - Every 30 minutes

Table 3-7 Connection Statistics Metrics

Metric

Description

Default Warning Threshold

Default Critical Threshold

Alert Text

Connections Used (%)

The percent of allowable connections used.

Not Defined

Not Defined

The total number of connections has crossed warning (%warning_threshold%) or critical (%critical_threshold%) threshold.

Table 3-8 Connection Statistics Metrics

Metric

Description

Maximum Connections

The configured maximum allowable connections to the Sybase ASE.

Number of Connections

The current number of connections to the Sybase ASE..

3.9 Currently Executing Queries

The metrics in this category provide information for currently executing statements.

Table 3-9 Currently Executing Queries Metrics

Metric

Description

Procedure stack frame ID

Stack frame of the procedure, if a procedure.

CPU Usage (ms) by Query

Number of milliseconds of CPU used by the statement.

Line number of the statement in SQL batch

Line number of the statement in SQL batch.

Number of buffers read from cache

Number of buffers read from cache.

Procedure Memory Usage (KB)

Number of kilobytes of memory used for execution of the statement.

Network packets size (bytes)

Size (in bytes) of the network packet currently configured for the session.

Number of network packets received by Adaptive Server

Number of network packets received by Adaptive Server.

Number of network packets sent by Adaptive Server

Number of network packets sent by Adaptive Server.

Number of pages modified by the statement

Number of pages modified by the statement.

Number of buffers read from disk

Number of buffers read from disk.

Stored plan ID

Unique identifier for the stored plan for the procedure.

Number of altered plans

The number of plans altered at execution time.

Procedure ID

Unique identifier for the procedure.

Execution start time

Date when the statement began execution.

Total Waited time during execution (ms)

Number of milliseconds the task has waited during execution of the statement.

SPID

Session process identifier.

KPID

Kernel process identifier.

DBID

Database identifier.

Batch ID

Batch identifier.

Key Value

Unique identifier for the current executing query.

3.10 Currently Executing SQL Text Information

The metrics in this category provide the SQL text that is currently being executed.

Table 3-10 Currently Executing SQL Text Information Metrics

Metric

Description

SQL Text

SQL text.

SPID

Session process identifier.

KPID

Kernel process identifier.

SQL Batch ID

Identifier for specific SQL batch.

Line Number in SQL Batch

Line Number in SQL Batch

Sequence in Line

Indicates the position of this portion of the SQL text.

3.11 Data Caches Statistics

The metrics in this category provide statistics relating to Adaptive Server data caches.

Collection frequency - Every 60 minutes

Table 3-11 Data Caches Statistics Metrics

Metric

Description and User Action

Number of Buffer Pools Within the Cache

The number of buffer pools within the cache.

Number of partitions currently configured for the cache

Number of partitions currently configured for the cache.

For Release 6

Cache searches directed to the cache

For Release 7

Cache searches directed to the cache (per sec)

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 6 (1.0.1.0.0)

Number of buffers that were fetched and replaced in the most recently used portion of the pool, since last collection.

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 7 (1.1.1.0.0)

Rate at which the buffers were fetched and replaced in the most recently used portion of the pool, that is, the number of buffers that were fetched and replaced since last collection divided by the elapsed time between two collections.

Data Cache Hit Ratio (%)

Percentage of Data Cache Hit Ratio.

For Release 6

Number of buffers retrieved from the cache

For Release 7

Buffers retrieval rate from the data cache (per sec)

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 6 (1.0.1.0.0)

Number of buffers retrieved from the cache since last collection.

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 7 (1.1.1.0.0)

Rate at which the buffers were retrieved from the cache since last collection, that is, the number of buffers retrieved from the cache since last collection divided by the elapsed time between two collections.

Miss Ratio (%)

Percentage Cache Miss Ratio.

For Release 6

Number of buffers read into the cache from disk

For Release 7

Buffers read rate from the disk to data cache (per sec)

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 6 (1.0.1.0.0)

Number of buffers read into the cache from disk since last collection.

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 7 (1.1.1.0.0)

Rate at which the buffers were read into the cache from disk since last collection, that is, the number of buffers read into the cache from disk since last collection divided by the elapsed time between two collections.

For Release 6

Number of buffers written from the cache to disk

For Release 7

Buffers write rate from the data cache to disk (per sec)

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 6 (1.0.1.0.0)

Number of buffers written from the cache to disk since last collection.

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 7 (1.1.1.0.0)

Rate at which buffers were written from the cache to disk, that is, the number of buffers written from the cache to disk divided by the elapsed time between two collections.

Is relaxed replacement policy

Whether the cache is using relaxed cache replacement strategy.

For Release 6

Number of dirty buffer retrievals

For Release 7

Dirty buffer retrievals rate from data cache (per sec)

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 6 (1.0.1.0.0)

Number of dirty buffer retrievals since last collection.

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 7 (1.1.1.0.0)

Rate at which dirty buffers were retrieved since last collection, that is, the number of dirty buffer retrievals since last collection divided by the elapsed time between two collections.

Cache ID

Cache identifier.

Cache Name

Name of the cache.

3.12 Database Indexes

The metrics in this category provide information about each clustered index, one row for each nonclustered index, one row for each table that has no clustered index, and one row for each table that contains text or image columns.This table also contains one row for each function-based index or index created on a computed column.

Collection Frequency - Every 24 hours

Table 3-12 Database Indexes Metrics

Metric

Description and User Action

Creation date

Creation date.

Character set ID

Character set ID with which the index was created; 0 if there is no character data in the keys.

Index ID

ID of an index, or ID of table to which index belongs.

Number of keys for a clustered index

Number of keys for a clustered index on an allpages-locked table; number of keys, plus 1 for all other indexes. The upload frequency is after every sample. The operator is >. For this metric, you can set different warning and critical threshold values for each "Index/Table Name" object. If warning or critical threshold values are currently set for any "Index/Table Name" object, those thresholds can be viewed on the Metric Detail page for this metric. To specify or change warning or critical threshold values for each "Index/Table Name" object, use the Edit Thresholds page.

Maximum size of a row

Maximum size of a row.

Maximum number of rows per page

Maximum number of rows per page.

Minimum size of a row

Minimum size of a row.

For Release 6

Partition Type

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 6 (1.0.1.0.0)

\1 - range 2 - hash 3 or NULL - [default] round robin 4 - list

Segment number

Number of segment in which object resides.

Sort order ID

Sort order ID with which the index was created; 0 if there is no character data in the keys.

Index/Table Name

The name of an index, or the name of a table to which the index belongs.

Total Size (MB)

Total size of the index.

Used Space (MB)

Space used by the index.

Free Space (MB)

Free space in the index.

Space Utilization (%)

Percentage of database index space utilization.

3.13 Database Logins

The metric in this category provide information regarding each valid Adaptive Server user account.

Data from this metric is uploaded only if the severity has reached WARNING state.

Collection Frequency - Every 5 hours

Table 3-13 Database Logins Metrics

Metric

Description and User Action

CPU and I/O Last Cleared Date

Date totcpu and totio were last cleared.

DB Name

Name of database in which to put user when connection established.

Full Name

Full name of the user.

User Default Language

Default language of the user.

Number of failed login attempts

Number of failed login attempts; reset to 0 by a successful login. The upload frequency is after every sample. The operator is >. Alert text is - The total number of failed login attempts for %SUID% are %LoginCount%. It has crossed warning (%warning_threshold%) or critical (%critical_threshold%) threshold. For this metric you can set different warning and critical threshold values for each "SUID" object. If warning or critical threshold values are currently set for any "SUID" object, those thresholds can be viewed on the Metric Detail page for this metric. To specify or change warning or critical threshold values for each "SUID" object, use the Edit Thresholds page.

Login Name

Login name of the user.

Date the password was last changed

Date the password was last changed.

Login Status

Status of the login account. The upload frequency is after every sample. The operator is >. Alert text is - The login status for %SUID% is %Status%. For this metric you can set different warning and critical threshold values for each "SUID" object. If warning or critical threshold values are currently set for any "SUID" object, those thresholds can be viewed on the Metric Detail page for this metric. To specify or change warning or critical threshold values for each "SUID" object, use the Edit Thresholds page.

CPU Accumulated Time (ticks)

CPU time accumulated by login.

I/O Accumulated Time (ticks)

I/O accumulated by login

SUID

System user ID.

Accumulated I/O

Accumulated I/O for a certain SUID.

3.14 Database Login Roles

The metrics in this category provide information regarding each instance of a server login possessing a system role. One row is added for each role granted to each login. For example, if a single server user is granted sa_role, sso_role, and oper_role, three rows are added to sysloginroles associated with that user's system user ID (suid). Data from this metric is uploaded only if the severity has reached WARNING state.

Collection Frequency - Every 24 hour

Table 3-14 Database Login Roles Metrics

Metric

Description

Default Warning Threshold

Default Critical Threshold

Alert Text

Role Name

Name of the role.

Not Defined

Not Defined

User %Name% (with SUID %SUID%) has been granted server role of %RoleName%.

Table 3-15 Database Login Roles Metrics

Metric

Description and User Action

SUID

System user ID.

Name

Name of the role.

Server Role ID

Server role identifier.

3.15 Database Objects

The metrics in this category provide information regarding each table, view, stored procedure, extended stored procedure, log, rule, default, trigger, check constraint, referential constraint, computed column, function-based index key, and (in tempdb only) temporary object, and other forms of compiled objects. It also contains one row for each partition condition ID when object type is N.

Collection Frequency - Every 24 hours

Table 3-16 Database Objects Metrics

Metric

Description

Default Warning Threshold

Default Critical Threshold

Alert Text

Number of changes in the schema

Count of changes in the schema of an object (incremented if a rule or default is added).

For this metric, you can set different warning and critical threshold values for each "Object Name" object. If warning or critical threshold values are currently set for any "Object Name" object, those thresholds can be viewed on the Metric Detail page for this metric. To specify or change warning or critical threshold values for each "Object Name" object, use the Edit Thresholds page.

Not Defined

Not Defined

The total number of changes in the schema are %SchemaCnt%. It has crossed warning (%warning_threshold%) or critical (%critical_threshold%) threshold.

Table 3-17 Database Objects Metrics

Metric

Description and User Action

Stored Procedure ID of a Delete Trigger

Stored procedure ID of a delete trigger if the entry is a table. Table ID if the entry is a trigger.

Stored Procedure ID of a Insert Trigger

Stored procedure ID of a table's insert trigger if the entry is a table.

For Release 6

Object ID

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 6 (1.0.1.0.0)

Object ID.

Object Type

One of the following object types: C – computed column D – default F – SQLJ function L – log N – partition condition P – Transact-SQL or SQLJ procedure PR – prepare objects (created by Dynamic SQL) R – rule RI – referential constraint S – system table TR – trigger U – user table V – view XP – extended stored procedure.

Object Name

The name of the object.

Login Name of user who created the object

Login name of user who created the object.

3.16 Database Segments

The metrics in this category provide information regarding each segment (named collection of disk pieces). In a newly created database, the entries are: segment 0 (system) for system tables; segment 2 (logsegment) for the transaction log; and segment 1 (default) for other objects.

Collection Frequency - Every 24 hours

Table 3-18 Database Segments Metrics

Metric

Description

Default Warning Threshold

Default Critical Threshold

Alert Text

Status

ndicates which segment is the default segment.

If warning or critical threshold values are currently set for any "Segment Name" object, those thresholds can be viewed on the Metric Detail page for this metric. To specify or change warning or critical threshold values for each "Segment Name" object, use the Edit Thresholds page.

Not Defined

Not Defined

The Segment status is %Status%.

Table 3-19 Database Segments Metrics

Metric

Description and User Action

Segment Name

Segment name.

For Release 6

Segment Number

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 6 (1.0.1.0.0)

Segment number

3.17 Database Thresholds

The metrics in this category provide information regarding each threshold defined for the database.

Collection Frequency - Every 12 hours

Table 3-20 Database Thresholds Metrics

Metric

Description

Default Warning Threshold

Default Critical Threshold

Alert Text

Size of threshold (Logical Pages)

Size of threshold in logical pages. For this metric you can set different warning and critical threshold values for each object.

Not Defined

Not Defined

The size of threshold is %FreeSpace%". It has crossed warning (%warning_threshold%) or critical (%critical_threshold%) threshold.

Table 3-21 Database Thresholds Metrics

Metric

Description and User Action

For Release 6

Process Name

For Release 7

Procedure executed when unused pages count falls below threshold

Name of the procedure that is executed when the number of unused pages on segment falls below free_space.

Segment

Segment number.

SUID

The server user ID of the user who added the threshold or modified it most recently.

3.18 Database Transactions

The metrics in this category provide information about Adaptive Server transactions.

Collection Frequency - Every 12 hours

Table 3-22 Database Transactions Metrics

Metric

Description

Connection State

Value indicating the connection state.

Transaction Failover State

Value indicating the transaction failover state.

Lock Owner ID

Lock owner ID.

Length of Transaction Name

Length of transaction name.

Transaction Server Process ID

Server process ID, or 0 if the process is detached.

Remote Server Name

Name of the remote server.

Transaction State

Value indicating the current state of the transaction.

Transaction Status

Status of the transaction.

Transaction Type

Value indicating the type of transaction.

Transaction Key

Unique identifier for a transaction.

Transaction Start Time

Start time of the transaction.

Coordination Method or Protocol

Value indicating the coordination method or protocol.

Starting database of the transaction

Starting database of the transaction

Transaction Name

Name of the transaction.

3.19 Databases Instances

The metrics in this category provide information about the database instances.

Default Collection Interval — Every 1 Hour

Table 3-23 Databases Instances Metrics

Metric

Description

Data Space Utilization (%)

Name of the configuration parameter.

Free Data Space (MB)

Default value assigned to this parameter.

Log Space Utilization (%)

Most recent value of the configuration parameter set by sp_configure.

Total Data Space (MB)

Value used by Sybase Adaptive Server. It changes after you modify a parameter's value with sp_configure. For static parameters, it changes after you restart Sybase Adaptive Server.

Total Log Space (MB)

Unit of measurement. For example, bytes, number, and so on.

Used Data Space(MB)

Indicates whether the parameter is dynamic or static. For static parameters, Sybase Adaptive Server needs to be restarted. For dynamic parameter, it need not be started.

Database Name

The name of the database.

Free Log Space (MB)

Memory used by the parameter.

Used Log Space (MB)

Log space used in the instance.

3.20 Deadlock Statistics

The metrics in this category provide information pertaining to the most recent deadlocks that have occurred in Adaptive Server. You can tune the maximum number of messages returned with deadlock pipe max messages.

Collection frequency - Every 45 minutes

Table 3-24 Deadlock Statistics Metrics

Metric

Description and User Action

Application holding the lock

Name of the application holding the lock.

BatchID for the SQL executed by the process holding the lock

In Release 9, this metric name has changed to:

Lock Held Process executed SQL BatchID

Unique batch identifier for the SQL code being executed by the process holding the lock when it was blocked by another process (not when it acquired the lock).

Command being executed that caused the lock to be held

The command being executed that caused the lock to be held.

Context ID for the process holding the lock

In Release 9, this metric name has changed to:

Lock Held Process Context ID

Unique context identifier for the process holding the lock when it was blocked by another process (not when it acquired the lock).

Release 8 or lower

SPID of the parent process of the process holding the lock

SPID of the parent process of the process holding the lock.

Release 8 or lower

KPID of process holding the lock

KPID of process holding the lock.

Line number within the batch of the statement of the process

In Release 9, this metric name has changed to:

Lock Held Process executing Line Number within the batch

Line number within the batch of the statement of the process.

Type of lock being held

In Release 9, this metric name has changed to:

Holding lock type

Type of lock being held.

DBID where the stored procedure that caused the lock to be held resides

In Release 9, this metric name has changed to:

Lock held stored procedure's DBID

DBID where the stored procedure that caused the lock to be held resides.

Object ID of stored procedure holding the lock

In Release 9, this metric name has changed to:

Lock held stored procedure's Object ID

Unique object identifier for the stored procedure that caused the lock to be held, if applicable.

Release 8 or lower

SPID of process holding the lock

SPID of process holding the lock.

Transaction in which the lock was acquired

In Release 9, this metric name has changed to:

Transaction holding the lock

The name of the transaction in which the lock was acquired.

User name for whom lock being held

In Release 9, this metric name has changed to:

User holding the lock

Name of the user for whom the lock is being held.

Object DBID

Unique database identifier for database where the object resides.

Object Name

Name of the object.

Page number for which the lock requested

In Release 9, this metric name has changed to:

Lock Requested Page Number

Page number for which the lock requested, if applicable.

Deadlock resolved time

Time at which the deadlock was resolved.

Row number for which the lock was requested

In Release 9, this metric name has changed to:

Lock Requested Row Number

Row number for which the lock was requested, if applicable.

Release 8 or lower

SPID of the parent process of the process waiting for the lock

SPID of the parent process of the process waiting for the lock.

Release 8 or lower

KPID of the process waiting for the lock

KPID of the process waiting for the lock.

Type of lock requested

Type of lock requested.

Release 8 or lower

SPID of the process waiting for the lock

SPID of the process waiting for the lock.

Amount of time (ms) the waiting process was blocked

In Release 9, this metric name has changed to:

Blocked Process Waiting Time (ms)

Amount of time in milliseconds that the waiting process was blocked before the deadlock was resolved.

Name of the user for whom the lock is being requested

In Release 9, this metric name has changed to:

User requested/waiting for the lock

Name of the user for whom the lock is being requested.

Deadlock ID

Unique identifier for the deadlock.

Held Process FID/SPID/KPID

FID / SPID / KPID of the process holding the lock.

Victim KPID

KPID of the victim process for the deadlock.

Wait Process FID/SPID/KPID

FID / SPID / KPID of the process waiting for the lock.

3.21 Device Data and IO Log Statistics

The metrics in this category provide device I/O statistics broken down into data and log I/O for normal and temporary databases on each device.

Collection frequency - Every 30 minutes

Table 3-25 Device Data and IO Log Statistics Metrics

Metric

Description and User Action

Number of IO Operations

Total number of I/O operations.

For this metric, you can set different warning and critical threshold values for each unique combination of "Device Logical Name" and "IO Category" objects. If warning or critical threshold values are currently set for any unique combination of "Device Logical Name" and "IO Category" objects, those thresholds can be viewed on the Metric Detail page for this metric. To specify or change warning or critical threshold values for each unique combination of "Device Logical Name" and "IO Category" objects, use the Edit Thresholds page. See Editing Thresholds for information on accessing the Edit Thresholds page.

IO Waiting time (ms)

Amount of time (in milliseconds) spent waiting for I/O requests to be satisfied.

Device Logical Name

Logical name of the device.

IO Category

I/O category of the device.

3.22 Devices Statistics

The metrics in this category provide statistical information relating to devices.

Collection Frequency - Every 30 minutes.

Table 3-26 Device Statistics Metrics

Metric

Description and User Action

For Release 6

Number of APF device reads from device

For Release 7

APF read rate from the device (per sec)

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 6 (1.0.1.0.0)

Number of APF reads from the device since last collection.

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 7 (1.1.1.0.0)

Rate at which the APF reads were made from the device since last collection, that is, the number of APF reads from the device since last collection divided by the elapsed time between two collections.

For Release 6

Number of IO requests

For Release 7

I/O request rate to a mirrored device [if mirrored] (per sec)

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 6 (1.0.1.0.0)

Number of IO requests since last collection.

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 7 (1.1.1.0.0)

Rate at which the IO requests were made since last collection, that is, the number of IO requests since last collection divided by the elapsed time between two collections.

For Release 6

Number of force task waits for IO Synchronization

For Release 7

Forced task waits rate for IO sync. to mirrored device (per sec)

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 6 (1.0.1.0.0)

Number of tasks forced to wait for synchronization of an I/O request since last collection.

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 7 (1.1.1.0.0)

Rate at which the tasks were forced to wait for synchronization of an I/O request since last collection, that is, the number of tasks forced to wait for synchronization of an I/O request since last collection divided by the elapsed time between two collections.

IO Time (ms)

Total amount of time (in milliseconds) spent waiting for I/O requests to be satisfied since last collection.

For Release 6

Number of reads from device (excluding APF)

For Release 7

Read rate from the device [excluding APF] (per sec)

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 6 (1.0.1.0.0)

Number of reads from the device (excluding APF).

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 7 (1.1.1.0.0)

Rate at which the reads were made from the device since last collection, that is, the number of reads from device since last collection divided by the elapsed time between two collections.

For Release 6

Number of writes to device

For Release 7

Write rate to the device (per sec)

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 6 (1.0.1.0.0)

Number of writes to the device since last collection.

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 7 (1.1.1.0.0)

Rate at which the writes were made to the device since last collection, that is, the number of writes to the device since last collection divided by the elapsed time between two collections.

Device Logical Name

Logical name of the device.

Full Hierarchic file name of device

Full hierarchial file name of the device.

3.23 General Statistics

The metrics in this category provide general statistics.

For this metric, you can set different warning and critical threshold values for each "Number of Transactions" object. If warning or critical threshold values are currently set for any "Number of Transactions" object, those thresholds can be viewed on the Metric Detail page for this metric. To specify or change warning or critical threshold values for each "Number of Transactions" object, use the Edit Thresholds page.

Collection frequency - Every 30 minutes

Table 3-27 General Statistics Metrics

Metric

Description

Default Warning Threshold

Default Warning Threshold

Alert Text

Number of Active Locks

Number of active locks.

Not Defined

Not Defined

The total number of active locks are %active_locks%. It has crossed warning (%warning_threshold%) or critical (%critical_threshold%) threshold.

Number of Active Page Locks

Number of active page locks.

Not Defined

Not Defined

The total number of active page locks are %active_page_locks%. It has crossed warning (%warning_threshold%) or critical (%critical_threshold%) threshold.

Number of Active Table Locks

Number of active table locks.

Not Defined

Not Defined

The total number of active table locks are %active_table_locks%. It has crossed warning (%warning_threshold%) or critical (%critical_threshold%) threshold.

Number of Active Users

Number of active users.

Not Defined

Not Defined

The total number of active users are %active_users%. It has crossed warning (%warning_threshold%) or critical (%critical_threshold%) threshold.

Number of External Transactions

Number of external transactions.

Not Defined

Not Defined

The total number of external transactions are %extrn_trans%. It has crossed warning (%warning_threshold%) or critical (%critical_threshold%) threshold.

Number of Local Transactions

Number of local transactions.

Not Defined

Not Defined

The total number of local transactions are %local_trans%. It has crossed warning (%warning_threshold%) or critical (%critical_threshold%) threshold.

Number of Transactions

The number of transactions.

Not Defined

Not Defined

The total number of transactions are %transactions%. It has crossed warning (%warning_threshold%) or critical (%critical_threshold%) threshold.

Number of Servers

Number of servers.

Not Defined

Not Defined

The total number of servers are %servers%. It has crossed warning (%warning_threshold%) or critical (%critical_threshold%) threshold.

Number of Transaction Logs

Number of transaction logs.

Not Defined

Not Defined

The total number of transaction logs are %trans_logs%. It has crossed warning (%warning_threshold%) or critical (%critical_threshold%) threshold.

Number of User Tables

Number of user tables.

Not Defined

Not Defined

The total number of user tables are %user_tables%. It has crossed warning (%warning_threshold%) or critical (%critical_threshold%) threshold.

3.24 Locks Information

The metrics in this category provide information about a list of all locks that are being held, and those that have been requested, by any process, for every object.

Collection frequency - Every 45 minutes

Table 3-28 Locks Information Metrics

Metric

Description

Default Warning Threshold

Default Critical Threshold

Alert Text

Lock State

Whether the lock has been granted [Granted, Requested]. The upload frequency is after every sample and the operator is >. Alert text is - The lock state is %LockState%.

For this metric, you can set different warning and critical threshold values for each unique combination of "SPID", "KPID", "DBID", "Lock ID", and "Object ID" objects. If warning or critical threshold values are currently set for any unique combination of "SPID", "KPID", "DBID", "Lock ID", and "Object ID" objects, those thresholds can be viewed on the Metric Detail page for this metric. To specify or change warning or critical threshold values for each unique combination of "SPID", "KPID", "DBID", "Lock ID", and "Object ID" objects, use the Edit Thresholds page.

Not Defined

Not Defined

The lock state is %LockState%.

Table 3-29 Locks Information Metrics

Metric

Description

Lock context

Lock context (bit field). These values are the same as the values for the context column in syslocks.

Lock Level

The type of object for which the lock was requested (&rsquor;PAGE', &rsquor;ROW', and so on).

Lock Type

Type of lock [&rsquor;exclusive table', &rsquor;shared page', and so on

Locked page number

Page that is locked when LockLevel = &rsquor;PAGE'.

Parent SPID

Parent process ID.

Locked row number

Locked row numberRow that is locked when LockLevel = &rsquor;ROW'.

Lock wait time

The time (in seconds) that the lock request has not been granted.

SPID

Session process identifier.

KPID

Kernel process identifier.

DBID

Database identifier.

Lock ID

Lock identifier.

Object ID

Object identifier.

3.25 Most Recent Error Messages

The metrics in this category provide statistics pertaining to the most recent server error messages. The error messages shown could be from the time the information was last collected based on one of the following (whichever is the latest):

- Automatic collection interval set in Cloud Control

- Manual refresh done to collect real-time information during the set collection intervals

- Query manually run on the monSysStatement MDA table during the set collection interval

- Data from this metric is uploaded only if the severity has reached WARNING state.

Collection Frequency - Every 1 hour

Table 3-30 Most Recent Error Messages Metrics

Metric

Description

For Release 9

Error Message

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 9 (1.2.1.1.0)

Error message that was displayed.

For Release 9

Error Severity

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 9 (1.2.1.1.0)

Severity of the Error.

The upload frequency is after every sample. The operator is >=. The alert text will be " %ErrorMessage%. For this metric, you can set different warning and critical threshold values for each unique combination of ”SPID/KPID/FamilyID”, ”Engine Number”,”Error Number” and ”Error timestamp” objects. If warning or critical threshold values are currently set for any unique combination of ”SPID/KPID/FamilyID”, ”Engine Number”,”Error Number” and ”Error timestamp” objects, those thresholds can be viewed on the Metric Detail page for this metric. This alert is Stateless alert.

For Release 9

State

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 9 (1.2.1.1.0)

State of the error.

SPID/KPID/FamilyID

Session process identifier / Kernel process identifier / Family identifier

Engine Number

Engine on which the process was running.

Error Number

Error message number.

Error Timestamp

Time stamp when the error occurred.

3.26 Most Recently Executed Statement Statistics

The metrics in this category provide statistics pertaining to the most recently executed statements. The statements shown could be from the time the information was last collected based on one of the following (whichever is the latest):

- Automatic collection interval set in Cloud Control.

- Manual refresh done to collect real-time information during the set collection intervals.

- Query manually run on the monSysStatement MDA table during the set collection interval.

Collection Frequency - 12 hours

Table 3-31 Most Recently Executed Statement Statistics Metrics

Metric

Description

Procedure stack frame ID

Current procedure nesting level when executing the statement.

CPU Usage (ms)

Number of milliseconds of CPU used by the statement.

Execution finish time

Date when the statement finished execution.

Line number of the statement in SQL batch

Line number of the statement in SQL batch.

Number of buffers read from cache

Number of buffers read from cache.

Memory Usage (KB)

Number of kilobytes of memory used for execution of the statement.

Network packet size (bytes)

Size (in bytes) of the network packet currently configured for the session.

Number of network packets received by Adaptive Server

Number of network packets received by Adaptive Server.

Number of network packets sent by Adaptive Server

Number of network packets sent by Adaptive Server.

Number of pages modified by the statement

Number of pages modified by the statement.

Number of buffers read from disk

Number of buffers read from disk.

Stored Plan ID

Unique identifier for the stored plan for the procedure.

Number of alerted plans

The number of plans altered at execution time.

Procedure ID

Unique identifier for the procedure.

Execution start time

Date when the statement began execution.

Total waited time during execution (ms)

Number of milliseconds the task has waited during execution of the statement.

SPID

Session process identifier.

KPID

Kernel process identifier.

DBID

Database identifier.

SQL Batch ID

SQL Batch identifier.

Total Waited time during execution (ms)

Number of milliseconds the task has waited during execution of the statement.

Key Value

Unique identifier for the recently executed statement.

3.27 Network I/O Statistics

The metrics in this category provide network I/O statistics.

Collection frequency - Every 30 minutes

Table 3-32 Network I/O Statistics Metrics

Metric

Description

For Release 6

Number of bytes received

For Release 7

Incoming network IO traffic rate (bytes/sec)

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 6 (1.0.1.0.0)

Number of bytes received since last collection.

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 7 (1.1.1.0.0)

Rate at which the bytes were received since last collection, that is, the number of bytes received since last collection divided by the elapsed time between two collections.

For Release 6

Number of bytes Sent

For Release 7

Outgoing network IO traffic rate (bytes/sec)

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 6 (1.0.1.0.0)

Number of bytes sent since last collection.

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 7 (1.1.1.0.0)

Rate at which the bytes were sent since last collection, that is, the number of bytes sent since last collection divided by the elapsed time between two collections.

For Release 6

Number of Packets Received

For Release 7

Incoming network IO traffic rate (packets/sec)

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 6 (1.0.1.0.0)

Number of packets received since last collection.

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 7 (1.1.1.0.0)

Rate at which the packets were received since last collection, that is, the number of packets received since last collection divided by the elapsed time between two collections.

For Release 6

Number of Packets Sent

For Release 7

Outgoing network IO traffic rate (packets/sec)

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 6 (1.0.1.0.0)

Number of packets sent.

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 7 (1.1.1.0.0)

Rate at which the packets were sent since last collection, that is, the number of packets sent since last collection divided by the elapsed time between two collections.

3.28 Open Databases Statistics

The metrics in this category provide state and statistical information pertaining to databases that are currently in use.

Collection frequency - Every 1 hour

Table 3-33 Open Databases Statistics Metrics

Metric

Description

For Release 6

Number of append log semaphore requests

For Release 7

Append log semaphore request rate (per sec)

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 6 (1.0.1.0.0)

Number of semaphore requests when attempting to append to the database transaction log.

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 7 (1.1.1.0.0)

Rate at which the semaphore requests were made since last collection, that is, the number of semaphore requests made since last collection (when attempting to append to the database transaction log) divided by the elapsed time between two collections.

For Release 6

Number of append log semaphore waits

For Release 7

Append log semaphore Waits rate (per sec)

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 6 (1.0.1.0.0)

Number of times a task had to wait for the append log semaphore to be granted.

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 7 (1.1.1.0.0)

Rate at which a task had to wait for the append log semaphore to be granted since last collection, that is, the number of times a task had to wait for the append log semaphore to be granted divided by the elapsed time between two collections.

For Release 7

Append log semaphore Waits (%)

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 7 (1.1.1.0.0)

Percentage of tasks that had to wait for the append log semaphore to be granted since last collection.

Is backup in progress

Whether a backup is currently in progress for the database.

Last backup start time

Date that the last backup started for the database.

Is last backup failed

Whether the last backup of the database failed.

Is transaction log full

Whether the database transaction log is full.

DBID

Database identifier.

DB Name

The name of the database.

3.29 Open Objects Statistics

The metrics in this category provide statistics for all open objects.

Collection frequency - Every 12 hours

Table 3-34 Open Objects Statistics Metrics

Metric

Description

For Release 6

Number of APF buffers read

For Release 7

APF buffers read (OpenObject) rate (per sec)

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 6 (1.0.1.0.0)

Number of APF buffers read since last collection.

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 7 (1.1.1.0.0)

Rate at which the APF buffers were read since last collection, that is, the number of APF buffers read since last collection divided by the elapsed time between two collections.

LastOptSelectDate

Last date.

Last Used Date

Last used date.

For Release 6

Number of lock requests on object

For Release 7

Lock request rate for the object (per sec)

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 6 (1.0.1.0.0)

Number of requests for a lock on the object since last collection.

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 7 (1.1.1.0.0)

Rate at which the requests were made for a lock on the object since last collection, that is, the number of requests for a lock on the object since last collection divided by the elapsed time between two collections.

For Release 6

Number lock waits

For Release 7

Lock waits rate for the object (per sec)t

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 6 (1.0.1.0.0)

Number of times a task waited for a lock for the object since last collection.

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 7 (1.1.1.0.0)

Rate at which a task waited for a lock for the object since last collection, that is, the number of times a task waited for a lock for the object since last collection divided by the elapsed time between two collections.

For Release 7

Lock waits % for the object

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 7 (1.1.1.0.0)

Percentage of lock waits.

For Release 6

OptSelectCount

For Release 7

Object selection rate for plan during compilation (per sec)

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 6 (1.0.1.0.0)

Number of objects selected for plan during compilation (per sec).

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 7 (1.1.1.0.0)

Rate at which the objects were selected for plan during compilation.

For Release 6

Total number of buffers read

For Release 7

Buffers read (OpenObject) rate (per sec)

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 6 (1.0.1.0.0)

Total number of buffers read since last collection.

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 7 (1.1.1.0.0)

Rate at which the buffers were read since last collection, that is, the number of buffers read since last collection divided by the elapsed time between two collections.

For Release 6

Object Name

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 6 (1.0.1.0.0)

Name of the object.

For Release 6

Number of times the object is accessed

For Release 7

Object access rate (per sec)

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 6 (1.0.1.0.0)

Number of times the object was accessed since last collection. The upload frequency is after every sample. The operator is >. Alert text is - The total number of times the object accessed are %Operations%. It has crossed warning (%warning_threshold%) or critical (%critical_threshold%) threshold.

For this metric, you can set different warning and critical threshold values for each unique combination of "DBID", "Object ID", and "Index ID" objects. If warning or critical threshold values are currently set for any unique combination of "DBID", "Object ID", and "Index ID" objects, those thresholds can be viewed on the Metric Detail page for this metric. To specify or change warning or critical threshold values for each unique combination of "DBID", "Object ID", and "Index ID" objects, use the Edit Thresholds page.

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 7 (1.1.1.0.0)

Rate at which the object was accessed since last collection, that is, the number of times the object was accessed since last collection divided by the elapsed time between two collections.

For Release 6

Total number of pages read

For Release 7

Page read (OpenObject) rate (per sec)

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 6 (1.0.1.0.0)

Total number of pages read since last collection.

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 7 (1.1.1.0.0)

rate at which the pages were read since last collection, that is, the number of pages read since last collection divided by the elapsed time between two collections.

For Release 6

Total number of pages written to disk

For Release 7

Page write (OpenObject) rate to disk (per sec)

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 6 (1.0.1.0.0)

Total number of pages written to the disk since last collection.

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 7 (1.1.1.0.0)

Rate at which the pages were written to the disk since last collection, that is, the number of pages written to the disk since last collection divided by the elapsed time between two collections.

For Release 6

Number of buffers read from disk

For Release 7

Buffers read (OpenObject) rate from disk (per sec)

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 6 (1.0.1.0.0)

Total number of buffers read from the disk since last collection.

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 7 (1.1.1.0.0)

Rate at which the buffers were read from the disk since last collection, that is, the number of buffers read from the disk since last collection divided by the elapsed time between two collections.

For Release 6

Number of buffers written to disk

For Release 7

Buffers write (OpenObject) rate to disk (per sec)

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 6 (1.0.1.0.0)

Total number of buffers written to the disk since last collection.

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 7 (1.1.1.0.0)

Rate at which the buffers were written to the disk since last collection, that is, the number of buffers written to the disk since last collection divided by the elapsed time between two collections.

For Release 6

Number of deleted rows

For Release 7

Row deletion (OpenObject) rate (per sec)

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 6 (1.0.1.0.0)

Number of rows deleted since last collection.

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 7 (1.1.1.0.0)

Rate at which the rows were deleted since last collection, that is, the number of rows deleted since last collection divided by the elapsed time between two collections.

For Release 6

Number of inserted rows

For Release 7

Row insertion (OpenObject) rate (per sec)

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 6 (1.0.1.0.0)

Number of rows inserted since last collection.

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 7 (1.1.1.0.0)

Rate at which the rows were inserted since the last collection, that is, number of rows inserted divided by the elapsed time between two collections.

For Release 6

Number of updated rows

For Release 7

Row updates (OpenObject) rate (per sec)

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 6 (1.0.1.0.0)

Number of updates.

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 7 (1.1.1.0.0)

Rate at which the rows were updated since last collection, that is, the number of rows updated since last collection divided by the elapsed time between two collections.

For Release 6

Used Count

For Release 7

Object usage rate in plan during execution (per sec)

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 6 (1.0.1.0.0)

Number of times the object was used in the plan during compilation since last collection.

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 7 (1.1.1.0.0)

Rate at which the object was used in the plan during compilation since last collection, that is, the number of times the object was used in the plan during compilation since last collection divided by the elapsed time between two collections.

DBID

Database identifier.

DB Name

The name of the database.

Index ID

Index identifier.

Object ID

Object identifier.

3.30 Procedure Cache Statistics

The metrics in this category provide statistics relating to Adaptive Server procedure cache.

Collection frequency - Every 30 minutes

Table 3-35 Procedure Cache Statistics Metrics

Metric

Description

Procedure Cache Hit Ratio (%)

Procedure cache hit ratio percentage. The upload frequency is after every sample and the operator is >. Alert text is - Procedure Cache hit ratio is %HitRatio%. It has crossed warning (%warning_threshold%) or critical (%critical_threshold%) threshold.

For Release 6

Number of stored procedures loaded into cache

For Release 7

Stored procedures load rate into procedure cache (per sec)

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 6 (1.0.1.0.0)

Number of stored procedures loaded into the cache since last collection.

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 7 (1.1.1.0.0)

Rate at which the stored procedures were loaded into the cache since last collection, that is, the number of stored procedures loaded into the cache since last collection divided by the elapsed time between two collections.

Miss Ratio (%)

Procedure Cache Miss Ratio.

For Release 6

Number of stored procedures requested

For Release 7

Stored procedures request rate from procedure cache (per sec)

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 6 (1.0.1.0.0)

Number of stored procedures requested since last collection.

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 7 (1.1.1.0.0)

Rate at which the stored procedures where requested since last collection, that is, the number of stored procedures required since last collection divided by the elapsed time between two collections.

For Release 6

Number of stalls for a free procedure cache buffer

For Release 7

Procedure cache stalls rate (per sec)

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 6 (1.0.1.0.0)

Number of times a process had to wait for a free procedure cache buffer when installing a stored procedure into the cache.

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 7 (1.1.1.0.0)

Rate at which a process had to wait for a free procedure cache buffer since last collection, that is, the number of times a process had to wait for a free procedure cache buffer divided by the elapsed time between two collections.

For Release 6

Number of times a procedure normalized

For Release 7

Procedures normalize and write back rate to sysproced's(per sec)

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 6 (1.0.1.0.0)

Number of times a procedure was normalized and the tree written back to sysprocedures since last collection.

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 7 (1.1.1.0.0)

Rate at which a procedure was normalized since last collection, that is, the number of times a procedure was normalized and the tree written back to sysprocedures since last collection divided by the elapsed time between two collections.

For Release 11

Procedure Cache Used Memory(MB)

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 11 (1.3.1.0.0)

Memory used in the procedure cache

3.31 Process Network IO Activity

The metrics in this category provide network I/O activity information for each process.

Collection frequency - Every 30 minutes

Table 3-36 Network IO Activity Metrics

Metric

Description

For Release 6

Number of bytes received

For Release 7

Incoming network IO traffic (process) rate (bytes/sec)

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 6 (1.0.1.0.0)

Number of bytes received since last collection.

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 7 (1.1.1.0.0)

Rate at which the bytes were received since last collection, that is, the number of bytes received since last collection divided by the elapsed time between two collections.

For Release 6

Number of bytes sent

For Release 7

Outgoing network IO traffic (process) rate (bytes/sec)

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 6 (1.0.1.0.0)

Number of bytes sent since last collection.

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 7 (1.1.1.0.0)

Rate at which the bytes were sent since last collection, that is, the number of bytes sent since last collection divided by the elapsed time between two collections.

Network packet size

Network packet size the session is currently using.

For Release 6

Number of packets received

For Release 7

Incoming network IO traffic (process) rate (packets/sec)

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 6 (1.0.1.0.0)

Number of packets received since last collection.

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 7 (1.1.1.0.0)

Rate at which the packets were received since last collection, that is, the number of packets received since last collection divided by the elapsed time between two collections.

For Release 6

Number of packets sent

For Release 7

Outgoing network IO traffic (process) rate (packets/sec)

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 6 (1.0.1.0.0)

Number of packets sent since last collection.

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 7 (1.1.1.0.0)

Rate at which the packets were sent since last collection, that is, the number of packets sent since last collection divided by the elapsed time between two collections.

KPID

Kernel process identifier.

Login Name

Login user name.

SPID

Session process identifier.

3.32 Process Objects Information

The metrics in this category provide statistical information regarding objects that have been accessed by processes.

Collection frequency - Every 30 minutes

Table 3-37 Process Objects Information Metrics

Metric

Description

DB Name

Name of the database.

KPID

Kernel process identifier.

For Release 6

Number of buffers read from cache

For Release 7

Buffers read (process object) rate from cache (per sec)

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 6 (1.0.1.0.0)

Number of buffers read from the cache since last collection.

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 7 (1.1.1.0.0)

Rate at which the buffers were read from the cache since last collection, that is, the number of buffers read from cache since last collection divided by the elapsed time between two collections.

Object Name

Name of the object.

Object Type

Object type.

For Release 6

Number of APF buffers read from disk

For Release 7

APF buffers read (process object) rate from disk (per sec)

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 6 (1.0.1.0.0)

Number of APF buffers read from the disk since last collection.

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 7 (1.1.1.0.0)

Rate at which the APF buffers were read from the disk since last collection, that is, the number of APF buffers read from the disk since last collection divided by the elapsed time between two collections.

For Release 6

Number of buffers read from disk

For Release 7

Buffers read (process object) rate from disk (per sec)

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 6 (1.0.1.0.0)

Number of buffers read from the disk since last collection.

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 7 (1.1.1.0.0)

Rate at which the buffers were read from the disk since last collection, that is, the number of buffers read from the disk since last collection divided by the elapsed time between two collections.

SPID

Session process identifier.

3.33 Process Statistics

The metrics in this category provide detailed statistics about processes that are currently executing or waiting.

Collection frequency - Every 30 minutes

Table 3-38 Process Statistics Metrics

Metric

Description

Application Name

Application name.

Blocking SPID

Session process identifier of the process holding the lock that this process has requested, if waiting for a lock.

Requested Lock ID

Unique lock identifier for the lock that this process has requested, if waiting for a lock.

Command

Category of process or command that the process is currently executing.

DBID

Unique identifier for the database being used by the current process.

DB Name

Name of process for the database being used by the current process.

Process Engine Group Name

Engine group for the process.

Engine Number

Unique identifier of the engine on which the process is executing.

Process Execution Class

Execution class for the process.

FID

The SPID of the parent process, if it exists.

Statement position in SQL batch

Line number of the current statement within the SQL batch.

Login Name

Login user name.

Master Transaction ID

Unique transaction identifier for the current transaction, if in a transaction.

Number of child processes

Number of child processes, if executing a parallel query.

Process Priority

Priority at which the process is executing.

Elapsed time since connection established (seconds)

Number of seconds since this connection was established.

Process waiting time (seconds)

Amount of time in seconds that the process has been waiting, if the process is currently in a wait state.

Wait Event ID

Unique identifier for the event that the process is waiting for, if the process is currently in a wait state.

KPID

Kernel process identifier.

Procedure stack frame

Stack frame of the procedure.

SPID

Session process identifier.

SQL Batch ID

SQL batch identifier.

3.34 Process Tracking Details

The metrics in this category provide information enabling processes to be tracked to an application, user, client machine, and so on.

Collection frequency - Every 30 minutes

Table 3-39 Process Tracking Details Metrics

Metric

Description

Client Host Name

Host name of client

Client application OS PID

OS process identifier of the client application

Application Name

Application name.

Client Host IP

IP Address for client host.

KPID

Kernel process identifier.

Login Name

Login user name.

SPID

Session process identifier.

3.35 Processes Activity Statistics

The metrics in this category provide detailed statistics about process activity.

Collection frequency - Every 30 minutes

Table 3-40 Process Activity Statistics Metrics

Metric

Description

For Release 6

Number of transactions committed by the process

For Release 7

Transaction commit (process) rate (per sec)

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 6 (1.0.1.0.0)

Number of transactions committed by the process since last collection.

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 7 (1.1.1.0.0)

Rate at which the transactions were committed by the process since last collection, that is, the number of transactions committed by the process since last collection divided by the elapsed time between two collections.

CPU Usage by process (ms)

CPU time (in milliseconds) used by the process.

For Release 6

Number of pages where data was retrieved using an index

For Release 7

Page retrieval rate (per sec) for data using an index

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 6 (1.0.1.0.0)

Number of pages where data was retrieved using an index since last collection.

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 7 (1.1.1.0.0)

Rate at which the data was retrieved using an index since last collection, that is, the number of pages where data was retrieved using an index since last collection divided by the elapsed time between two collections.

Number of locks currently held by the process

Number of locks currently held by the process.

For Release 6

Number of buffers read from cache

For Release 7

Buffers read (process) rate from cache (per sec)

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 6 (1.0.1.0.0)

Number of buffers read from the cache since last collection.

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 7 (1.1.1.0.0)

Rate at which the buffers were read from the cache since last collection, that is, the number of buffers read from the cache since last collection divided by the elapsed time between two collections.

Allocated memory (bytes)

Amount of memory (in bytes) allocated to the process.

For Release 6

Number of pages read

For Release 7

Page read (process) rate (per sec)

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 6 (1.0.1.0.0)

Number of pages read since last collection.

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 7 (1.1.1.0.0)

Rate at which the pages were read since last collection, that is, the number of pages read since last collection divided by the elapsed time between two collections.

For Release 6

Number of pages written

For Release 7

Page write (process) rate (per sec)

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 6 (1.0.1.0.0)

Number of pages written since last collection.

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 7 (1.1.1.0.0)

Rate at which the pages were written since last collection, that is, the number of pages written since last collection divided by the elapsed time between two collections.

For Release 6

Number of buffers read from disk

For Release 7

Buffer read (process) rate from disk (per sec)

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 6 (1.0.1.0.0)

Number of buffers read from the disk since last collection.

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 7 (1.1.1.0.0)

Rate at which the buffers were read from the disk since last collection, that is, the number of buffers read from the disk since last collection divided by the elapsed time between two collections.

For Release 6

Number of buffers written to disk

For Release 7

Buffer write (process) rate to disk (per sec)

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 6 (1.0.1.0.0)

Number of buffers written to the disk since last collection.

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 7 (1.1.1.0.0)

Rate at which the buffers were written to the disk since last collection, that is, the number of buffers written to the disk since last collection divided by the elapsed time between two collections.

For Release 6

Number of transactions rolled back by the process

For Release 7

Transactions roll-back (process) rate (per sec)

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 6 (1.0.1.0.0)

Number of transactions rolled back by the process since last collection.

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 7 (1.1.1.0.0)

Rate at which the transactions were rolled back by the process since last collection, that is, the number of transactions rolled back by the process since last collection divided by the elapsed time between two collections.

Number of pages where data was retrieved without an index

Number of pages where data was retrieved without an index.

For Release 6

Number of temporary table objects accessed

For Release 7

Temporary tables (process) creation/access rate (per sec)

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 6 (1.0.1.0.0)

Number of temporary table objects accessed since last collection.

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 7 (1.1.1.0.0)

Rate at which the temporary table objects were accessed since last collection, that is, the number of temporary table objects accessed since last collection divided by the elapsed time between two collections.

For Release 6

Number of transactions started by the process

For Release 7

Transactions start (process) rate (per sec)

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 6 (1.0.1.0.0)

Number of transactions started by the process since last collection.

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 7 (1.1.1.0.0)

Rate at which the transactions were started by the process since last collection, that is, the number of transactions started by the process since last collection divided by the elapsed time between two collections.

For Release 6

Number of bytes written to the user log cache for the process

For Release 7

Write rate (bytes/sec) to the ULC for the process

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 6 (1.0.1.0.0)

Number of bytes written to the user log cache for the process since last collection.

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 7 (1.1.1.0.0)

Rate at which the bytes were written to the user log cache since last collection, that is, the number of bytes written to the user log cache for the process since last collection divided by the elapsed time between two collections.

Current usage (bytes) of the User log cache by the process

The current usage (bytes) of the User log cache by the process.

For Release 6

Total number of times the user log cache was flushed

For Release 7

User Log Cache flush rate (per sec)

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 6 (1.0.1.0.0)

Total number of times the user log cache was flushed since last collection.

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 7 (1.1.1.0.0)

Rate at which the user log cache was flushed since last collection, that is, number of times the user log cache was flushed since last collection divided by the elapsed time between two collections.

For Release 6

Number of times the user log cache was flushed

For Release 7

User Log Cache flush rate [due to ULC full] (per sec)

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 6 (1.0.1.0.0)

Number of times the user log cache was flushed because it was full since last collection.

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 7 (1.1.1.0.0)

Rate at which the user log cache was flushed since last collection, that is, the number of times the user log cache was flushed since last collection divided by the elapsed time between two collections.

Maximum ever usage (bytes) of the user log cache

The maximum ever usage (in bytes) of the user log cache by the process.

Waited time by process (ms)

Time (in milliseconds) the process has spent waiting.

KPID

Kernel process identifier.

Login Name

Login user name.

Page retrieval rate (per sec) for data without an index

Page retrieval rate (per sec) for data without an index

SPID

Session process identifier.

The current usage (bytes) of the User log cache by the process

The current usage (bytes) of the User log cache by the process

The maximum ever usage (bytes) of the user log cache

The maximum ever usage (bytes) of the user log cache

3.36 Response

This metrics in this category provide information about the response of the target Sybase ASE instance.

Default Collection Interval — Every 5 minutes

Table 3-41 Response Metrics

Metric

Description

Status

Status of the Sybase ASE instance.

3.37 Recently Executed SQL Text

The metrics in this category provide the most recent SQL text that has been executed, or is currently being executed. The maximum number of rows returned can be tuned with SQL text pipe max messages.

Collection Frequency - 12 hours

Table 3-42 Recently Executed SQL Text Metrics

Metric

Description

For Release 7

SQL Batch ID

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 7 (1.1.1.0.0)

Batch ID of the currently being executed SQL text.

For Release 7

SQL Text position in bat

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 7 (1.1.1.0.0)

Position of the currently being executed SQL test.

For Release 7

Server UID

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 7 (1.1.1.0.0)

UIS of the server.

For Release 7

Server UID

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 7 (1.1.1.0.0)

UIS of the server.

For Release 6

SQL Text

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 6 (1.0.1.0.0)

SQL Text

SPID

Session process identifier.

KPID

Kernel process identifier.

Login Name

Login user name.

Server User ID - Batch ID - SQL Text Position in Batch

Server User identifer / Batch identifier of the currently being executed SQL test / Position of the currently being executed SQL test.

3.38 Running Procedures Statistics

The metrics in this category provide a list of all procedures that are being executed by processes.

Collection frequency - Every 30 minutes

Table 3-43 Running Procedures Statistics Metrics

Metric

Description

Compile date

Compile date of the procedure.

Stack frame

Stack frame of the procedure.

DB Name

Name of the database that contains the procedure.

Memory Usage (KB)

Number of kilobytes of memory used by the procedure.

Procedure Name

Name of the procedure.

Procedure Type

Type of the procedure.

Owner Name

Name of the owner.

Query Plan ID

Unique identifier for the query plan.

DBID

The identifier of the database that contains the procedure.

KPID

Kernel process identifier.

Object ID

Object identifier.

Owner UID

Owner user ID.

SPID

Session process identifier.

3.39 Segment Usages

The metrics in this category provide details about the segment usages.

By default, this metric is disabled.

For enabling this metric, monitoring user should have permission on each database.

All these metric categories provide the following details:

Table 3-44 Segment Usages Metrics

Metric

Description

Segment Free Space (MB)

Name of the configuration parameter.

Segment Size (MB)

Default value assigned to this parameter.

Segment Space Utilization (%)

Memory used by the parameter.

Segment Used Space (MB)

Most recent value to which the configuration parameter has been set with sp_configure.

3.40 Server-Wide Worker Threads Statistics

The metrics in this category provide server-wide statistics related to worker threads.

Collection Frequency - Every hour

Table 3-45 Server-Wide Worker Threads Statistics Metrics

Metric

Description

Max number of worker processes ever in use

The maximum number of worker processes that have ever been in use.

Max number of worker processes ever in use

The maximum number of worker processes that have ever been in use.

Max parallel degree

The maximum degree of parallelism that can be used (the current Run Value for max parallel degree) in configuration parameter.

Max scan parallel degree

The maximum degree of parallelism that can be for a scan (the current Run Value for max scan parallel degree) in configuration parameter).

For Release 6

Number of attempted parallel queries

For Release 7

Attempted parallel queries (SysWorkerThread) rate (per sec)

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 6 (1.0.1.0.0)

Number of attempted parallel queries since last collection.

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 7 (1.1.1.0.0)

Rate at which the parallel queries were attempted since last collection, that is, the number of attempted parallel queries since last collection divided by the elapsed time between two collections.

For Release 6

Number of altered plans

For Release 7

Altered plans (SysWorkerThread) rate (per sec)

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 6 (1.0.1.0.0)

Number of altered plans since last collection.

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 7 (1.1.1.0.0)

Rate at which altered plans were made since last collection, that is, the number of altered plans since last collection divided by the elapsed time between two collections.

Number of active worker processes

Number of worker processes active. The upload frequency is after every sample. The operator is >. Alert text is - The total number active worker processes are %ThreadsActive%. It has crossed warning (%warning_threshold%) or critical (%critical_threshold%) threshold.

Total memory configured for worker processes (Bytes)

The amount of memory configured for use by worker processes.

Max configured worker processes

Configured maximum number of worker processes.

Total memory in use by worker processes (Bytes)

The amount of memory currently in use by worker processes.

Max memory ever used by worker processes (Bytes)

The maximum amount of memory ever used by worker processes.

3.41 Top SQL Statements by CPU Time

The metrics in this category provide information regarding the SQL statements that have been using the most CPU time.

Collection Frequency - Every 30 minutes

Table 3-46 Top SQL Statements by CPU Time Metrics

Metric

Description

SQL Hash

An MD5 hash of the statement text.

SQL Text

The statement text that executed.

Database ID

The ID of the database in which the statement executed.

Database Name

The name of the database in which the statement executed.

Last Start Time

The last time the statement started executing.

CPU Time (ms) per Hour

The amount of time the statement has spent executing as an hourly rate.

3.42 Top SQL Statements by Wait Time

The metrics in this category provide information regarding the SQL statements that have been spending the most time waiting.

Collection Frequency - Every 30 minutes

Table 3-47 Top SQL Statements by Wait Time Metrics

Metric

Description

SQL Hash

An MD5 hash of the statement text.

SQL Text

The statement text that executed.

Database ID

The ID of the database in which the statement executed.

Database Name

The name of the database in which the statement executed.

Last Start Time

The last time the statement started executing.

Wait Time (ms) per Hour

The amount of time the statement has spent executing, as an hourly rate.

3.43 Top SQL Statements by Memory Usage

The metrics in this category provide information regarding the SQL statements that have been using the most memory.

Collection Frequency - Every 30 minutes

Table 3-48 Top SQL Statements by Memory Usage

Metric

Description

SQL Hash

An MD5 hash of the statement text.

SQL Text

The statement text that executed.

Database ID

The ID of the database in which the statement executed.

Database Name

The name of the database in which the statement executed.

Last Start Time

The last time the statement started executing.

Average Memory Usage (KB)

The amount of memory used by the SQL statement.

3.44 Top Ten Big Cached Objects

The metrics in this category provide statistics for all objects and indexes with pages currently in a data cache.

Only top 10 rows sorted on size are returned every time this metric is collected.

Collection frequency - Every 60 minutes

Table 3-49 Top Ten Big Cached Objects Metrics

Metric

Description

Cached Object Size

Number of kilobytes of the cache the object is occupying.

Cache Name

Name of the cache.

DB Name

Name of the database.

Object Name

Name of the object.

Object Type

Type of object.

Owner Name

Name of the object owner.

For Release 6

Number of Processes Currently Accessing the Object (per sec)

For Release 7

Process Access Rate of the Object (per sec)

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 6 (1.0.1.0.0)

Number of processes currently accessing the object.

For System Monitoring Plug-In for Sybase Adaptive Server Enterprise (ASE) Release 7 (1.1.1.0.0)

Rate at which the processes are currently accessing the object, that is, the number of processes that have started accessing the object since last collection divided by the elapsed time between two collections.

Cache ID

Cache identifier.

DBID

Database identifier.

Index ID

Index identifier.

Object ID

Object identifier.

Owner UID

Owner user ID.

3.45 Top Ten Frequently Accessed Cached Objects

The metrics in this category provide statistics for all objects and indexes with pages currently in a data cache.

Only top 10 rows sorted on the frequency of access are returned every time this metric is collected.

Collection frequency - Every 60 minutes

Table 3-50 Top Ten Frequently Accessed Cached Objects Metrics

Metric

Description

Cached Object Size

Number of kilobytes of the cache the object is occupying.

Cache Name

Name of the cache.

DB Name

Name of the database.

Object Name

Name of the object.

Object Type

Type of object.

Owner Name

Name of the object owner.

Process Access Rate of the Object (per sec)

Rate at which the processes are currently accessing the object, that is, the number of processes that have started accessing the object since last collection divided by the elapsed time between two collections.

Cache ID

Cache identifier.

Cache Name

Name of the cache.

Cached Object Size (KB)

Number of kilobytes of the cache the object is occupying.

DB Name

Name of the database.

DBID

Database identifier.

Index ID

Index identifier.

Object Name

The name of the object.

Object ID

Object identifier.

Owner UID

Owner user ID.

3.46 Transaction Logs

The metrics in this category provide information regarding the utilization of the transaction log for each database.

Collection Frequency - Every 30 minutes

Table 3-51 Transaction Logs Metrics

Metric

Description

Database Name

The configured maximum allowable connections to the Sybase ASE.

Total Size (MB)

The current number of connections to the Sybase ASE.

Used Space (MB)

The percent of allowable connections used.

Free Space (MB)

The amount of free space in the transaction log.

Space Utilization (%)

The amount of free space in the transaction log.

3.47 Wait Class Event Information

The metrics in this category provide information and textual description for all of the wait classes (for example, waiting for a disk read to complete). All wait events have been grouped into wait classes that classify the type of event that a process is waiting for.

Collection Frequency - Every 720 hours

Table 3-52 Wait Class Event Information Metrics

Metric

Description

Description

Description of the wait event class.

Wait Class Event ID

Wait Class Event identifier.

3.48 Wait Events Information

The metrics in this category provide information and textual description for every possible situation where a process is forced to wait within Adaptive Server.

Collection Frequency - Every 720 hours

Table 3-53 Wait Events Information Metrics

Metric

Description

Description

Description of the wait event type.

Wait Event ID

Unique identifier for the wait event.

Wait Class ID

Wait Class identifier.

3.49 Waiting Events Statistics

The metrics in this category provide a server-wide view of where processes are waiting for an event.

Collection Frequency - Every 1 hour

Table 3-54 Waiting Events Statistics Metrics

Metric

Description

Wait Event ID

Unique identifier for the wait event.

Total number of task waits

Number of times tasks that have waited for the event.

Amount of waiting time for an event (ms)

Amount of time (in milliseconds) that tasks have spent waiting for the event. Calculated based on collection schedule, default of 1 hour.

Wait Type

The category of the wait event.

Waits Per Hour for the Event

The hourly occurrence rate of the wait event.

3.50 Waiting Process Statistics

The metrics in this category provide a server-wide view of where processes are waiting for an event.

Collection Frequency - 30 minutes

Table 3-55 Waiting Process Statistics Metrics

Metric

Description

Default Warning Threshold

Default Critical Threshold

Alert Text

Total number of waits for event

Number of times the process has waited for the event.

Not Defined

Not Defined

The total total number of waits for event are %Waits%. It has crossed warning (%warning_threshold%) or critical (%critical_threshold%) threshold.

Table 3-56 Waiting Process Statistics Metrics

Metric

Description

Total waiting time for event (ms)

Amount of time (in milliseconds) that the process has waited for the event.

SPID

Session process identifier.

KPID

Kernel process identifier.

Wait Event ID

Unique identifier for the wait event.

3.51 Configuration Metrics

Configuration metrics consist of the following categories.

3.51.1 Charsets Information

The metrics in this category provide details about the charsets.

Default Collection Interval — Every 24 hours

Table 3-57 Charsets Information Metrics

Metric

Description

Entity Type

Type of charset entity.

ID

Unique ID assigned to the entity.

Charset ID

Unique ID assigned to the charset.

Status

Status of the charset.

Charset Name

Name of the charset.

Description

A brief description of the charset.

Sort File

Name of the associated sort file.

3.51.2 Configuration Parameters

This section is a grouping of the following configuration parameters metric categories:

- Backup/Recovery Configuration Parameters

- Cache Manager Configuration Parameters

- Component Integration Services Configuration Parameters

- Configuration Options Configuration Parameters

- DTM Administration Configuration Parameters

- Diagnostics Configuration Parameters

- Disk I/O Configuration Parameters

- Error Log Configuration Parameters

- Extended Stored Procedure Configuration Parameters

- General Information Configuration Parameters

- Java Services Configuration Parameters

- Languages Configuration Parameters

- Lock Manager Configuration Parameters

- Memory Use Configuration Parameters

- Meta-Data Caches Configuration Parameters

- Monitoring Configuration Parameters

- Network Communication Configuration Parameters

- O/S Resources Configuration Parameters

- Physical Memory Configuration Parameters

- Physical Resources Configuration Parameters

- Processors Configuration Parameters

- Rep Agent Thread Administration Configuration Parameters

- Security Related Configuration Parameters

- SQL Server Administration Configuration Parameters

- Unicode Configuration Parameters

- User Environment Configuration Parameters

- Maximum Number of Connections Configuration Parameters

The default collection interval for all these metric categories is — Every 24 hours

All these metric categories provide the following details:

Table 3-58 Configuration Parameters Metrics

Metric

Description

Parameter Name

Name of the configuration parameter.

Default Value

Default value assigned to this parameter.

Memory Used

Memory used by the parameter.

Config Value

Most recent value to which the configuration parameter has been set with sp_configure.

Run Value

Value being used by Adaptive Server. It changes after you modify a parameter's value with sp_configure and, for static parameters, after you restart Adaptive Server.

Unit

Unit of measurement. For example, bytes, number, and so on.

Type

Indicates whether the parameter is dynamic or static. For static The Adaptive Server needs to be restarted if it is a static parameter. and not if it is a dynamic parameter.

3.51.3 Database Instances

The metrics in this category provide details about the database instances.

The default collection interval for all these metric categories is — Every 1 hour

All these metric categories provide the following details:

Table 3-59 Database Instances Metrics

Metric

Description

Data Space Utilization (%)

Name of the configuration parameter.

Free Data Space (MB)

Default value assigned to this parameter.

Free Log Space (MB)

Memory used by the parameter.

Log Space Utilization (%)

Most recent value of the configuration parameter set by sp_configure.

Total Data Space (MB)

Value being used by Sybase Adaptive Server. It changes after you modify a parameter's value with sp_configure and for static parameters, after you restart Sybase Adaptive Server.

Total Log Space (MB)

Unit of measurement. For example, bytes, number, and so on.

Used Data Space(MB)

Indicates whether the parameter is dynamic or static. For static parameters, Sybase Adaptive Server needs to be restarted. For dynamic parameter, it need not be started.

Used Log Space (MB)

Used log space by the database instance.

3.51.4 Installed Scripts

The metrics in this category provide details about the installed scripts.

Default Collection Interval — Every 24 hours

Table 3-60 Installed Scripts Metrics

Metric

Description

Script Name

Name of the installed script.

Version

Version of the installed script.

Status

Current status of the script.

3.51.5 Sybase ASE Version

The metrics in this category provide details about the version of Sybase ASE.

Default Collection Interval — Every 24 hours

Table 3-61 Sybase ASE Version Metrics

Metric

Description

Version

Version of Sybase ASE.

3.51.6 System Databases

The metrics in this category provide details about the databases used.

Default Collection Interval — Every 24 hours

Table 3-62 System Databases Metrics

Metric

Description

Database Name

Name of the database used.

Database Size

Size of the database used.

Owner Name

Name of the owner of the database.

Database ID

Unique ID of the database.

Date of Creation

Date when the database was created.

Status

Current status of the database.

3.51.7 System Listeners

The metrics in this category provide details about the system listeners.

Default Collection Interval — Every 24 hours

Table 3-63 System Listeners Metrics

Metric

Description

ASE Server Name and Port

Name of the ASE Server and the associated port.

Network Protocol

Network protocol used.

4 Microsoft SQL Server Reports

This chapter provides a list of out-of-box reports available for System Monitoring Plug-In for Microsoft SQL Server.

Table 4-1 Microsoft SQL Server Reports

Report Name

Report Elements

Microsoft SQLServer System Configuration

- Instance Information

- Registry

- Security

- Server Parameters

Microsoft SQLServer System Database Configuration

- Database

- Database Settings

Microsoft SQLServer System Memory Statistics

- Server Statistics

- Buffer Cache Hit Ratio (%)

- Cache Hit Ratio (%)

- Average Latch Wait Time (ms)

- Total Lock Wait Time (ms)

Microsoft SQLServer System Space Usage

- Database Space Usage

- Top 5 Databases by Space Used (%)

- Database Files

Microsoft SQLServer System Performance

- Host CPU load percentage

- Top SQL Server Processes by CPU Time

- Memory Manager

- Top Server Processes by Memory Usage

Microsoft SQLServer System Process Info and Locks

- Summary

- Process States

- Process Info

- Process Locks

- Lock Analysis

Microsoft SQLServer System Cache and Buffer

- Memory Status

- Buffer Performance

- Buffer Allocation

- Cache Performance

- Memory Allocation

- Memory Allocation Chart

Microsoft SQLServer System Database Backups and Jobs

- Database Backups

- Database Jobs

Microsoft SQLServer System Cluster

- Cluster Nodes Summary

- Nodes in Cluster

- SQL Cluster Nodes Summary

- Cluster Resources and Activity

Microsoft SQLServer System Statistics

- Server Statistics

- Rate of Errors

- Packet Error Ratio

- Rate of Reads

- Rate of Writes

- Database Statistics

- Database Statistics Summary

- Server Statistics

Microsoft SQLServer Session Performance

- Top 5 Sessions by CPU Time

- Top 5 Sessions by Memory Utilization

Microsoft SQLServer Query Performance

- Top 5 Queries by Execution

- Top 5 Queries by Blocked Time

- Top 5 Queries by CPU Time

Figure 4-1 shows the System Performance report available for Microsoft SQL Server:

Figure 4-1 Microsoft SQL Server System Performance Report

5 IBM DB2 Database Reports

This chapter provides a list of out-of-box reports available for System Monitoring Plug-In for IBM DB2 Database.

Table 5-1 IBM DB2 Database Reports

Report Name

Report Elements

IBM DB2 Database System Configuration

- System Configuration

- Product Overview

- Instances

- Partitions

- Registry Settings

IBM DB2 Database DB Manager Configuration

- DB Manager Capacity

- DB Manager Database Instance

- DB Manager Log and Recovery

- DB Manager Partitioned DB Environment

- DB Manager Connections

IBM DB2 Database DB Disk Storage Statistics

- Disk Space Utilization

- Disk Space Utilization Summary

- Disk Space Utilization Details

IBM DB2 Database Bufferpool and Non-Buffered IO Statistics

- Bufferpool Activity Summary

- Non Buffered IO Activity Summary

- Reads per sec

- Index Read Rate

- Index and Data Write Rate

- Non Buffered IO

IBM DB2 Database Cache Statistics

- Package Cache Summary

- Catalog Cache Summary

- Catalog Cache OverflowsCatalog Cache Hit RatioPackage Cache Overflows Package Cache Hit Ratio

- Catalog Cache Heapfull

IBM DB2 Database Sort Heap and Hash Join Statistics

- Total Sorts and Hash Joins

- Active Sorts

- Active Sorts Summary

- Average Sort Space Used

- Average Sort Time Per Sort

- Sorts Overflow Ratio

- Hash Join Small Overflows and Overflows

- Hash Join Small Overflows to Overflows Ratio

IBM DB2 Database DB Manager Sorts Statistics

- Database Manager Sorts Summary

- Post Threshold Sorts and Joins

- Piped Sorts Rejection Rate

IBM DB2 Database Locks Statistics

- Lock Summary

- Average Lock Wait Time

- Locks Held and Waiting

- Application Escalations and Timeouts

- Deadlocks and Internal Deadlock Rollbacks

IBM DB2 Database DB Manager Agents and Connections Statistics

- Agent Configuration

- Agent Pool Activity

- Agents Creation to Assignment Ratio

- Idle Agents

- Agents Waiting on Token

- Remote Connections

- Remote Connections Summary

- Local Connections

- Local Connections Summary

IBM DB2 Database Applications Lock Performance

- Top 10 Applications Based on Average Lock Wait Time (ms)

- Top 10 Applications Based on Number of Locks Held

- Top 10 Applications Based on Number of Lock Timeouts

IBM DB2 Database CPU Usage

- Top 10 Applications Based on Total CPU Usage (ms)

- Top 10 Applications Based on Total Idle Time (ms)

IBM DB2 Database Applications Row Accesses and Sorts Performance

- Top 10 Applications Based on Rows Read

- Top 10 Applications Based on Rows Written

- Top 10 Applications Based on Time Spent in Sorts (ms)

IBM DB2 Database SQL Statement Performance

- Top 10 Statements Based on Rows Read

- Top 10 Statements Based on Rows Written

- Top 10 Statements Based on Average Sort Time (ms)

- Top 10 Statements Based on CPU Usage (ms)

IBM DB2 Database DB Health

- Database Health Information

- Database Health Indicator

- Database Collection Health Indicator

IBM DB2 Database DB Manager Health

- Database Manager Health Information

- Database Manager Health Indicator

IBM DB2 Database Tablespace Health

- Tablespace Health Information

- Tablespace Health Indicator

IBM DB2 Database Container Health

- Container Health Information

- Container Health Indicator

IBM DB2 Database Tablespace Statistics

- Top 5 Tablespaces by Space Available (%)

- Tablespaces Summary

IBM DB2 Statement and Lock Wait Analysis

- Top 5 Statements by Execution

- Top 5 Statements by CPU Time

- Lock Wait Times by Blocking Application

Figure 5-1 shows the DB Manager Agents and Connections Statistics report available for IBM DB2 Database:

Figure 5-1 IBM DB2 Database Cache Statistics Report

6 Sybase Adaptive Server Enterprise Database Reports

This chapter provides a list of out-of-box reports available for System Monitoring Plug-In for Sybase (ASE) Database.

Table 6-1 Sybase (ASE) Database Reports

Report Name

Report Elements

Sybase ASE Performance Statistics

- CPU Utilization

- IO Utilization

- ASE State Summary

- ASE State Summary...

Sybase ASE Engines Statistics

- System CPU (by all Engine's) Utilization

- User CPU (by all Engine's) Utilization

- Top 10 Engines by System CPU Utilization

- Top 10 Engines by User CPU Utilization

Sybase ASE Databases Space Statistics

- Top 10 Databases by Log Space Utilization

- Top 10 Databases by Data Space Utilization

- Databases Backup Related Summary

Sybase ASE Device IO Statistics

- User Data Devices IO Operations Vs Waiting time

- User Log Devices IO Operations Vs Waiting Time

- User Data Devices IO Summary

- User Log Devices IO Summary Tempdb Data Devices IO Operations Vs Waiting Time

- Tempdb Log Devices IO Operations Vs Waiting Time

- Tempdb Data Devices IO Summary

- Tempdb Log Devices IO Summary

- Device Reads Rate Vs Writes Rate Vs APF Reads Rate

- Devices Semaphore Requests Rate Vs Waits Rate Device IO Operations Summary

Sybase ASE Data Cache Statistics

- Data Cache Hit Rate History

- Data Cache Hit Rates Summary

- Data Cache Memory Usage

- Cached Object Accesses by Processes

- Top 10 Big Objects in Data Cache

- Top 10 Popular Objects in Data Cache

Sybase ASE Procedure Cache Statistics

- Procedure Cache Hit Rate History

- Procedure Cache Memory Usage History

- Top 10 Objects in Procedure Cache by Memory Usage

Sybase ASE Network IO Monitoring Report

- Incoming Traffic History

- Outgoing Traffic History

- Incoming Packet Traffic History

- Outgoing Packet Traffic History

Sybase ASE Database Log Statistics

- Overall Append Log Requests Rate Vs Waits Rate

- Tempdb Append Log Requests Rate Vs Waits Rate

- Overall Append Log Waits Percentage

- Tempdb Append Log Waits Percentage

- Databases Log Related Summary

Sybase ASE User Statistics

- Attempted Logins History

- Top 5 User SQL Statistics Summary

- Top 3 Users by CPU Time

- Top 3 Users by DiskIO

- Top 3 Users by CPU Time - Summary

- Top 3 Users by DiskIO Time - Summary

- Top 3 Users by Incoming Network Traffic

- Top 3 Users by Outgoing Network Traffic

- Top 3 Users by Incoming Network Traffic - Summary

- Top 3 Users by Outgoing Network Traffic - Summary

Sybase ASE Process Statistics

- Top 10 Processes by CPU Time

- Top 10 Processes by Waiting Time

- Top 10 Processes by Incoming Network Traffic

- Top 10 Processes by Outgoing Network Traffic

- Top 10 Processes by Memory Usage

- Top 10 Processes by User Log Cache(ULC) Writes Top 10 Processes by Transactions Rate

- Top 10 Processes by DiskIO Rate

Sybase ASE SQL Statistics

- Top SQL Statements by CPU Time

- Top SQL Statements by Memory Usage

- Top SQL Statements by Wait Time

Sybase ASE Open Objects Statistics

- Top 5 Hot Objects by Logical Reads

- Top 5 Hot Objects by Physical Reads

- Top 5 Hot Objects by APF Reads

- Top 5 Hot Objects by Page Reads

- Top 5 Hot Objects by Physical Writes

Sybase ASE Deadlock Statistics

- Top 10 Processes by Locks Held

- Deadlock Detail Table

Sybase ASE Worker Threads Statistics

- Attempted Parallel Queries History

- Altered Plans History

- Worker Threads Summary

Sybase ASE Configuration

- Sybase ASE Version

- System Listeners

- Information about Databases

- Installed Scripts

- Charsets Information

- Backup/Recovery Configuration Parameters

- Cache Manager Configuration Parameters

- Component Integration Services Configuration Parameters

- Configuration Options Configuration Parameters

- DTM Administration Configuration Parameters

- Diagnostics Configuration Parameters

- Disk I/O Configuration Parameters

- Error Log Configuration Parameters

- Extended Stored Procedure Configuration Parameters

- General Information Configuration Parameters

- Java Services Configuration Parameters

- Languages Configuration Parameters

- Lock Manager Configuration Parameters

- Memory Use Configuration Parameters

- Meta-Data Caches Configuration Parameters

- Monitoring Configuration Parameters

- Network Communication Configuration Parameters

- O/S Resources Configuration Parameters

- Physical Memory Configuration Parameters

- Physical Resources Configuration Parameters

- Processors Configuration Parameters

- Rep Agent Thread Administration Configuration Parameters

- Security Related Configuration Parameters

- SQL Server Administration Configuration Parameters

- Unicode Configuration Parameters

- User Environment Configuration Parameters

Sybase ASE Error Statistics

- Errors Production Rate (per hour)

- Recent Error Messages Summary

- Recent Error Messages with Severity 10 to 16

- Recent Error Messages with Severity 17 to 18

- Recent Error Messages with Severity 19 to 26

Sybase ASE Transaction Log Statistics

- Top 5 Transaction Logs by Current Space Utilization (%)

- Top 5 Transaction Logs by Recent Space Utilization (%)

- Top 5 Transaction Logs by Current Total Space (MB)

- Top 5 Transaction Logs by Recent Total Space (MB)

- Top 5 Transaction Logs by Current Free Space (MB)

- Top 5 Transaction Logs by Recent Free Space (MB)

- Top 5 Transaction Logs by Current Used Space (MB)

- Top 5 Transaction Logs by Recent Used Space (MB)

- Transaction Log Summary

Figure 6-1 shows the SQL Statistics report available for Sybase Adaptive Server Enterprise Database:

Figure 6-1 Performance Statistics Reports

Oracle Legal Notices

Copyright Notice

Copyright © 1994-2016, Oracle and/or its affiliates. All rights reserved.

License Restrictions Warranty/Consequential Damages Disclaimer

This software and related documentation are provided under a license agreement containing restrictions on use and disclosure and are protected by intellectual property laws. Except as expressly permitted in your license agreement or allowed by law, you may not use, copy, reproduce, translate, broadcast, modify, license, transmit, distribute, exhibit, perform, publish, or display any part, in any form, or by any means. Reverse engineering, disassembly, or decompilation of this software, unless required by law for interoperability, is prohibited.

Warranty Disclaimer

The information contained herein is subject to change without notice and is not warranted to be error-free. If you find any errors, please report them to us in writing.

Restricted Rights Notice

If this is software or related documentation that is delivered to the U.S. Government or anyone licensing it on behalf of the U.S. Government, then the following notice is applicable:

U.S. GOVERNMENT END USERS: Oracle programs, including any operating system, integrated software, any programs installed on the hardware, and/or documentation, delivered to U.S. Government end users are "commercial computer software" pursuant to the applicable Federal Acquisition Regulation and agency-specific supplemental regulations. As such, use, duplication, disclosure, modification, and adaptation of the programs, including any operating system, integrated software, any programs installed on the hardware, and/or documentation, shall be subject to license terms and license restrictions applicable to the programs. No other rights are granted to the U.S. Government.

Hazardous Applications Notice

This software or hardware is developed for general use in a variety of information management applications. It is not developed or intended for use in any inherently dangerous applications, including applications that may create a risk of personal injury. If you use this software or hardware in dangerous applications, then you shall be responsible to take all appropriate fail-safe, backup, redundancy, and other measures to ensure its safe use. Oracle Corporation and its affiliates disclaim any liability for any damages caused by use of this software or hardware in dangerous applications.

Trademark Notice

Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.

Intel and Intel Xeon are trademarks or registered trademarks of Intel Corporation. All SPARC trademarks are used under license and are trademarks or registered trademarks of SPARC International, Inc. AMD, Opteron, the AMD logo, and the AMD Opteron logo are trademarks or registered trademarks of Advanced Micro Devices. UNIX is a registered trademark of The Open Group.

Third-Party Content, Products, and Services Disclaimer

This software or hardware and documentation may provide access to or information about content, products, and services from third parties. Oracle Corporation and its affiliates are not responsible for and expressly disclaim all warranties of any kind with respect to third-party content, products, and services unless otherwise set forth in an applicable agreement between you and Oracle. Oracle Corporation and its affiliates will not be responsible for any loss, costs, or damages incurred due to your access to or use of third-party content, products, or services, except as set forth in an applicable agreement between you and Oracle.

Alpha and Beta Draft Documentation Notice

If this document is in preproduction status:

This documentation is in preproduction status and is intended for demonstration and preliminary use only. It may not be specific to the hardware on which you are using the software. Oracle Corporation and its affiliates are not responsible for and expressly disclaim all warranties of any kind with respect to this documentation and will not be responsible for any loss, costs, or damages incurred due to the use of this documentation.

Private Alpha and Beta Draft Documentation Notice

If this document is in private preproduction status:

The information contained in this document is for informational sharing purposes only and should be considered in your capacity as a customer advisory board member or pursuant to your beta trial agreement only. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, and timing of any features or functionality described in this document remains at the sole discretion of Oracle.

This document in any form, software or printed matter, contains proprietary information that is the exclusive property of Oracle. Your access to and use of this confidential material is subject to the terms and conditions of your Oracle Master Agreement, Oracle License and Services Agreement, Oracle PartnerNetwork Agreement, Oracle distribution agreement, or other license agreement which has been executed by you and Oracle and with which you agree to comply. This document and information contained herein may not be disclosed, copied, reproduced, or distributed to anyone outside Oracle without prior written consent of Oracle. This document is not part of your license agreement nor can it be incorporated into any contractual agreement with Oracle or its subsidiaries or affiliates.

Documentation Accessibility

For information about Oracle's commitment to accessibility, visit the Oracle Accessibility Program website at http://www.oracle.com/pls/topic/lookup?ctx=acc&id=docacc.

Access to Oracle Support

Oracle customers that have purchased support have access to electronic support through My Oracle Support. For information, visit http://www.oracle.com/pls/topic/lookup?ctx=acc&id=info or visit http://www.oracle.com/pls/topic/lookup?ctx=acc&id=trs if you are hearing impaired.