Page History
...
The information displayed in the Statistics tab refers to the time period you selected in the Time Frame selector. The entities displayed in the Association area are related to the selected entity displayed in the Main area.
About the Main area in the Statistics tab
The Main area shows comprehensive information on the selected entity. You can choose from several views to examine the entity from different angles. For example, the Instance entity provides information on CPU usage and pressure, memory allocation, I/O activity, network activity, locking, and wait and latch events.
About the Association area in the Statistics tab
The Association area displays relevant information on the entities associated with the selected entity (displayed in the Tab Heading in the Main area) in a table format. For example, it is possible to associate to all Databases or Disks that are related to a specific Instance, by selecting an entity from the Association controls list. Notice that some entities, show additional information on the associated entities in different tabs. The tabs are located above the Association area table. Clicking on a tab displays different table columns showing different information for the associated entities.
...
| Anchor | ||||
|---|---|---|---|---|
|
The Statistics tab displays information on the following entities:
...
- Tier (when All Instances is selected)
...
- Instance
...
- Databases
■ Databases
■ Disks
■ Network Card
...
- Disks
- Network Card
- Performance Counter or Counter Instance
...
- Wait Events
...
- Latch Events
For an explanation of the counters displayed in the Statistics tab, see the Microsoft performance monitor (perfmon) tool
.
About the Tier entity
Choosing All Instances from the Instances list displays information on the Tier entity.
The Tier entity provides statistical information on all SQL Server instances in the application, in the following views:
■ Overview
■ Instance Grouping
- Overview
- Instance Grouping
About viewing a summary of statistical information on top instance consumers
The overview of the Tier entity provides statistical information on the top six instance consumers of various resources and enables you to compare them.
The Tier area shows the top six consumers, as follows:
- Top 6 Instances (% CPU)
...
- . Displays the top six CPU consuming instances.
- Top 6 Instances (Paging)
...
- . Displays top six instances with the most page faults.
...
...
- Top 6 Instances (Batch Requests/sec)
...
- . Displays top six instances with the highest batch request rate.
...
...
About viewing statistical information on instance grouping
The Instance Grouping view displays performance counters broken down into instance groups (the instance groups are defined by the user). For example, you can view the avg SQL Server CPU usage in the Production group.
For an explanation on how to define instance groups, see the Precise Installation Guide. The following table describes the information displayed in the Instance Grouping table.
Table 9-1 Instance 1 Instance Grouping table
| Column |
|---|
...
| Description |
|---|
...
| Group |
...
| Displays the name of the group. |
...
| Instances | Displays the number of instances linked to the group. |
| SQL Server |
...
| CPU | Displays the average SQL Server CPU Usage (%) of all the instances in the specified group. |
| Transactions/ |
...
| sec | Displays the average number of Transactions/sec started for the databases of the instances in the specified group. |
| Batch Requests/ |
...
| sec | Displays the average number of Batch Requests/sec received by the instances in the specified group. |
| Page Faults/ |
...
| sec | Displays the average number of Page Faults/sec executed by the instances in the specified group. |
...
About viewing instances associated with an Tier in the Association area
You can view information on the Instances associated with the selected Tier in the Association Area.
The following table describes the information displayed in the tabs in the Association area.
Table 9-2 Viewing 2 Viewing information on associated instances
| Tab |
|---|
...
| Description |
|---|
...
| CPU |
...
| Displays counters that report on the average and maximum CPU resources used by the SQL Server and the entire Server. |
| Load |
...
| Displays counters that report on instance load such as Login/sec, and Transactions/sec. Both average and maximum values are displayed. |
| I/ |
...
| O | Displays counters that report on I/O performance, such as the average time it takes to read from the disk, and I/O load, such as the average number of read operations on the disk, per second. |
...
| Memory | Displays counters that report on memory utilization by SQL Server, such as SQL Server Memory |
...
| MB (max) vs. Page Faults per second |
...
| . | |
| Network | Displays statistics that report on network activity, such as Output Queue Length and Packets sent or received, per second. |
...
See “About the Main area” on page 25.See and “About the Association area” on page 25.
About the Instance entity
The instance entity displays statistical information for the entire instance. Precise for SQL Server collects hundreds of counters that are relevant to the entire instance. The Instance entity displays predefined graphs that display several counters in each. Each graph contains counters that have some relation to each other.
After drilling down to an instance entity, the following views are available for the Instance entity:
- Overview
...
- CPU
■ CPU
■ Load
...
- Load
- I/O (Performance)
...
- I/O (Load)
...
- Memory (Page Faults)
...
- Memory (Allocation)
...
- Memory (Buffer Pools)
...
- Network
■ Network
■ Locking
...
- 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:
- In MS-
...
- SQL. Displays resource consumption counters collected by the Precise for SQL Server Collector such as, Using
...
- CPU, I/O wait, and Lock wait.
...
- CPU. Displays statistics that reports on CPU usage broken down into SQL Server CPU usage and Non SQL Server CPU usage.
...
- Paging. Displays memory paging statistics that are broken down into SQL Server Page Faults/sec and Non SQL Server Page Faults/sec.
...
- Load. Displays instance load statistics such as, Batch Requests/sec, Transactions/sec and Logins/sec.
- I/
...
- O. Displays I/O load statistics, such as, the percentage of time that has elapsed while the disks are busy servicing read and write requests.
...
- Network. Displays network load statistics, such as Maximum Packets Received/sec and Maximum Packets
...
- Sent/sec.
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:
...
- Wait for Resource
...
- Wait for CPU
...
- % Wait for CPU
If the CPU wait time exceeds the resource wait time, consider adding additional CPU or faster CPUs, or perform SQL tuning.
...
CPU wait time is the amount of time the session waited, from the time the resource became available until the time the session resumed running.
About the Database entity
The Database entity displays SQL Server database statistics. Precise for SQL Server collects many counters that are relevant to the database. The database entity displays predefined graphs that display several counters in each. This enables you to immediately view relevant counters, grouped according to topic.
...