Page History

...

• Overview
• CPU
• Load
• I/O (Performance)
• I/O (Load)
• Memory (Page Faults)
• Memory (Allocation)
• Memory (Buffer Pools)
• Network
• Locking
• Wait Events (SQL Server 2005 only)
• Latch Events (SQL Server 2005 only)
• CPU Pressure (SQL Server 2005 only)

About getting an overview of an Instance based on statistical data

The Overview displays predefined overtime graphs, which can indicate where performance problems lie. The predefined graphs contain counters reported by SQL Server and counters collected by the Precise for SQL Server Collector from perfmon counters. The following graphs are displayed:

...

See “About session states” on page 36.

About viewing CPU usage statistics for an Instance

The CPU view displays statistics that report CPU usage. The following graphs are displayed:

• CPU. Reports Non-SQL Server CPU time usage and SQL Server CPU time usage.
• Processor Queue Length. Displays the Processor Queue Length counter.

About viewing Load statistics for an Instance

The Load view displays statistics that report the instance load. The following graphs are displayed:

• Load. Reports Batch Requests/sec, Transactions/sec and Logins/sec.
• SQL Compilations. Reports SQL Re-Compilations/sec and SQL Compilations/sec.

About viewing I/O (Performance) statistics for an Instance

The I/O Performance view displays statistics that report on the I/O performance of all the machine's physical disks. The following graphs are available:

• Disk sec/IO Operations. Displays Avg. Disk sec/Transfer, Avg Disk sec/Write, and Avg Disk sec/Read.
• Disk Queue Length. Displays the Avg. Disk Queue Length counter.

About viewing I/O (Load) statistics for an Instance

The I/O Load view displays statistics that report on Server I/O load and SQL Server I/O load. The following graphs are available:

• SQL Server I/O Load. Displays Page Writes/sec and Page Reads/sec.
• Disk Operations/sec. Displays Avg Disk Writes/sec and Avg Disk Reads/sec.
• I/O. Displays % Disk Write Time and % Disk Read Time.

About viewing Memory (Page Faults) statistics for an Instance

The Memory Page Faults view displays statistics that report instance paging versus memory usage. The following graphs are displayed:

• Server Memory. Reports Non SQL Server Memory and SQL Server Memory.
• Paging. Reports Non SQL Server Page Faults and SQL Server Page Faults.

About viewing Memory (Allocation) statistics for an Instance

The Memory Allocation view displays statistics that report on memory allocation. The following graphs are available:

• SQL Server Memory. Displays SQL Cache Memory, Optimizer Memory, Connection Memory, Lock Memory, Granted Workspace Memory, and Memory in Buffer Pool.
• Memory Allocation in Buffer Pool. Free Pages, Stolen Pages, and Database pages.

About viewing Memory (Buffer Pools) statistics for an Instance

The Memory Buffer Pools view displays statistics that report on Buffer Pool allocation and Hit Ratios. The following graphs are available:

• Cache and Database Pages. Displays Trigger Plans Cache Pages, Procedure Plans Cache Pages, Prepared SQL Plans Cache Pages, Ad hoc Plans Cache Pages and Database Pages.
• Hit Ratios. Displays Trigger Plans Hit Ratio, Procedure Plans Hit Ratio, Prepared SQL Plans Hit Ratio, Ad hoc SQL Plans Hit Ratio, and Buffer Cache Hit Ratio.

About viewing Network statistics for an Instance

The Network view displays statistics that report on network activity. The following graphs are available:

• Packets/sec. Displays Max Packets received/sec and Max Packets Sent/sec.
• Output Queue Length. Displays the Max Output Queue Length counter.
• Bytes/sec. Displays Max Bytes Received/sec and Max Bytes Sent/sec.

About viewing Locking statistics for an Instance

The Locking view displays statistics that report on locks and latches. The following graphs are available:

• Latch Waits/sec. Displays the Latch Waits/sec counter.
• Lock Timeouts/sec. Displays the Lock Timeouts/sec counter.
• Table Lock Escalations. Displays the Table Lock Escalations counter.
• Deadlocks and Lock Timeouts. Displays the Deadlocks and Lock Timeouts counter.

About viewing Wait Events for an instance

The Wait Event view displays statistics on the top ten wait events for the selected instance.

About Latch Events for an instance

The Latch Event view displays statistics on the top ten latch events for the selected instance.

About CPU Pressure for an instance

The CPU Pressure view displays statistics on CPU pressure for the selected instance. The stacked graphs show the breakdown of total wait time into the following:

...

After drilling down to a database entity, the following views are available: 

• Overview

■    Overview

■    Transactions

■    Log Flushes

...

• Transactions
• Log Flushes
• Log Growths or Shrinks

■    Replication

■    Backup or Restore

...

• Replication
• Backup or Restore

About getting an overview of the resource consumption and log activity of a database

The Overview displays overtime graphs, that represent resource consumption and log activity of the selected database. The overtime graphs contain counters reported by SQL Server and counters collected by the Precise for SQL Server Collector.

The following information is displayed for the selected database: 

• General

...

• Details. Displays general information regarding the selected database entity.

...

• In MS-

...

• SQL. Displays database resource consumption counters collected by the Precise for SQL Server Collector, such as, Using CPU, I/O wait, Lock wait.

...

• Transactions. Displays Transactions/sec and Active Transactions.
• Flush Wait

...

• Time. Displays the Log Flush Wait Time counter.
• Log

...

• Flushes. Displays Log Flushes/sec, Log Flush Waits/sec and Log Cache Reads/sec.

See “About session states” on page 36.

About viewing Database Transactions statistics

The Transactions view displays a graph that shows the database's transaction activity, such as Transactions/sec and Active Transactions.

About viewing Database Log Flushes statistics

The Log Flushes view displays statistics that report log performance. The following graphs are available:

• Log

...

• Flushes. Displays Log Flushes/sec, Log Flush Waits/sec and Log Cache Reads/sec.
• Flush Wait

...

• Time. Displays the Log Flush Wait Time counter.

About viewing Database Log Growths or Shrinks statistics

The Log Growths or Shrinks view displays statistics that report on the log activity. The following graphs are available:

• Log Growths and

...

• Shrinks. Displays Log Growths, Log Shrinks and Log Truncations.
• Log Bytes

...

• Flushed. Displays Log Bytes Flushed/sec counter.

About viewing Database Replication statistics

The Replication view displays statistics that provide information on the replication activity such as number of transactions marked for replication and number of transactions read out from the transaction log and delivered to the distribution database.

About viewing Database Backup or Restore statistics

The Backup or Restore view displays counter that reports on the performance of the backup and restore operations.

About viewing statistical information on the Database entity

You can view information on the entities associated with the selected Database entity in the Association area. For example, you can analyze counters that report on the database's transaction activity and log activity. It is possible to associate to the Database entity from an Instance entity.

See “About the Main area” and “About the Association area” on page 25.

About the Disks entity

The Disk entity displays predefined graphs that report on disk activity and performance in the Overview view. This enables you to immediately view relevant counters, grouped according to topic.

About getting an overview of Disk performance and I/O load

Displays predefined overtime graphs, which represent I/O load and performance of the selected disk. The following graphs are available:

• I/O

...

• Wait. Displays I/O wait on the selected disk.
• SQL Server I/O

...

• Load. Displays Page Read/sec and Page Write/sec.
• Disk Queue

...

• Length. Displays Avg. Disk Queue Length counter.
• Disk sec/IO

...

• Operations. Displays Avg Disk sec/Transfer, Avg Disk sec/Read and Avg Disk sec/Write.
• I/O

...

• Activity. Displays Avg Disk Reads/sec and Avg Disk Writes/sec.
• I/

...

• O. Displays % Disk Write Time and % Disk Read Time.

About viewing information on entities associated with the selected Disk

You can view information on the entities associated with the selected Disk entity in the Association area. It is possible to associated to the Disk entity an Instance entity. The counters displayed in the Association area report the disk's performance and load.

See “About the Main area” and “About the Association area” on page 25.

About the Network Cards entity

The Network Card entity displays predefined graphs that report the network's activity, load and latency, in the Overview view. This enables you to immediately view relevant counters, grouped according to topic.

About getting an overview of Disk Network load

The Overview displays statistics that report on the network load. The following graphs are available:

• Packets/

...

• sec. Displays Packets Received/sec and Packets Sent/sec.
• Output Queue

...

• Length. Displays the average Output Queue Length counter.
• Bytes/

...

• sec. Displays Bytes Received/sec and Bytes Sent/sec.

About viewing information on entities associated with the selected Network Card

You can view information on the entities associated with the selected Network Card entity in the Association area. It is possible to associate to the Network Card entity from an Instance entity. The counters displayed in the Association area report the network card's activity, load and latency.

See “About the Main area” and “About the Association area” on page 25.

About the Performance Counter and Counter Instance entities

The performance counter and counter instance entities display statistical information on the selected counter in the Overview view. Precise for SQL Server collects counters reported by the SQL Server and counters that are relevant to the entire machine. These counters determine which aspects of the objects to monitor. For example, the SQL Server Locks object contains the Lock Requests/sec counter.

Additional counters reported by Precise for SQL Server include:■    Access

• Access Methods

...

• Backup Device

...

• Buffer Manager

...

• Buffer Partition

...

• Cache Manager

...

• Databases

...

• Exec Statistics

...

• General Statistics

...

• Latches

...

• Locks

...

• Memory

...

• Memory Manager

...

• Network Interface

...

• Physical Disk

■    Plan Cache

■    Process

■    Processor

■    Replication Agents

...

• Processor
• Replication Agents
• Replication Dist.

...

• Replication Log Reader

...

• Replication Merge

...

• Replication Snapshot

...

• SQL Statistics

...

• System

...

• Transactions

...

• User Setable

About getting an overview of Performance Counters or Counter Instances

...