This section includes the following topics:
The Statistics tab displays statistical information on all SQL Server instances. In addition several OS statistics are also presented to enable you to examine SQL Server and OS statistics together. The tab can be used to monitor your system's current state as well as historical statistical information.
The Statistics tab enables you to provide answers to the following types of questions: "Is SQL Server the major CPU consumer in my server?" or "I am aware that SQL Server suffers from I/O wait. Is it possible to examine the disks' average queue length?"
The statistics tab provides hundreds of raw performance counters (All perfmon counters that are relevant to SQL Server and a few additional OS counters are collected). It is possible to view current status in one-minute intervals or historical information roll-up at a higher summary level.
If you are using SQL Server 2005, you will have access to additional statistics on wait events and latch events. Viewing these statistics will let you diagnose additional performance issues in the In MS-SQL graph. You will be able to view how much time the SQL Server waited for each wait group. Based on the information displayed in the CPU Pressure graph, you will be able to determine if the system is experiencing CPU pressure that may be alleviated by faster CPUs, by adding additional CPUs, or by SQL tuning.
While examining a single counter is very important, the real value obtained from working with the Statistics tab lies in examining the ready-to-use set of graphs that display several related counters.
Use the Statistics tab to periodically monitor the health of your system—for example, view the statistics on CPU utilization or Hit Ratios. Alternatively, you can use the Statistics tab to fully analyze a performance problem reported by the Collector. For example, if the Collector identifies a massive Using CPU State it may be necessary to check the CPU queue length or SQL compilation counters to check related problems that can influence the Using CPU State parameter.
The Statistics tab displays information on a selected entity and its associated entities. When you open the Statistics tab from the Dashboard tab, the tab is launched, in context, and depending upon your selection in the All SQL Server Instances table (All or Instance), the information is displayed on the Tier or Instance level. When you open the Statistics tab from the Activity tab, the tab is launched, in context, if the selected entity exists in the Statistics tab. If you open the Statistics tab from another tab, the historical settings (meaning those settings which were selected when you left the tab, such as the last entity you drilled down to) are taken into account and the information displayed the last time you viewed this tab is displayed (similar to clicking the History button and returning to a previous tab). For more information about the Precise bar, see About the Precise bar.
The selected entity is always reflected in the Tab heading, which serves as a point of orientation. The highest-level entity you can view information for in the Statistics tab is the Tier level (viewed when All Instances is selected from the Instance list). You can also select a specific instance from the Instance list.
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.
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.
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.
The following tabs show additional information when All Instances is selected:
If you want to view additional information on an associated entity, drill down to it, by selecting the entity's row. A drill-down affects the entire tab. When you drill down to another entity, the Tab heading reflects the new selection, the Main area displays information on the selected entity, and the Association area displays the entities that are now associated with the newly selected entity.
For example, when you want to view information on a specific disk, choose Disks from the Association controls. The Association area changes to display information on the disks associated with the selected Instance. Note that the Tab heading and the Main area remain unchanged.
In the Association area, click the row of the disk you want to view detailed information for. The Tab heading indicates the newly selected entity; the Main area displays statistical information on the disk entity you drilled down to, and the Association area shows information on its associated performance counters. For more information about displaying this data, see About drilling down in context.
The Statistics tab displays information on the following entities:
For an explanation of the counters displayed in the Statistics tab, see the Microsoft performance monitor (perfmon) tool.
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:
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:
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 Installation and Administration. The following table describes the information displayed in the Instance Grouping table.
Table 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. |
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 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. |
For more information about the Main and Association areas, see About the Main area and About the Association area.
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:
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:
For more information about session states, see About session states.
The CPU view displays statistics that report CPU usage. The following graphs are displayed:
The Load view displays statistics that report the instance load. The following graphs are displayed:
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:
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:
The Memory Page Faults view displays statistics that report instance paging versus memory usage. The following graphs are displayed:
The Memory Allocation view displays statistics that report on memory allocation. The following graphs are available:
The Memory Buffer Pools view displays statistics that report on Buffer Pool allocation and Hit Ratios. The following graphs are available:
The Network view displays statistics that report on network activity. The following graphs are available:
The Locking view displays statistics that report on locks and latches. The following graphs are available:
The Wait Event view displays statistics on the top ten wait events for the selected instance.
The Latch Event view displays statistics on the top ten latch events for the selected 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:
If the CPU wait time exceeds the resource wait time, consider adding additional CPU or faster CPUs, or perform SQL tuning.
Resource wait time is the amount of time the sessions waited for the resource to become available.
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.
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.
After drilling down to a database entity, the following views are available:
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:
For more information about session states, see About session states.
The Transactions view displays a graph that shows the database's transaction activity, such as Transactions/sec and Active Transactions.
The Log Flushes view displays statistics that report log performance. The following graphs are available:
The Log Growths or Shrinks view displays statistics that report on the log activity. The following graphs are available:
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.
The Backup or Restore view displays counter that reports on the performance of the backup and restore operations.
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. For more information about the Main and Association areas, see About the Main area and About the Association area.
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.
Displays predefined overtime graphs, which represent I/O load and performance of the selected disk. The following graphs are available:
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. For more information about the Main and Association areas, see About the Main area and About the Association area.
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.
The Overview displays statistics that report on the network load. The following graphs are available:
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. For more information about the Main and Association areas, see About the Main area and About the Association area.
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:
The Overview displays general details of the selected counter and an overtime graph that represents the counter value. The following information is available:
Maximum. Shows the maximum value of the counter, calculated from all time slices in the selected time frame.
This value may be different from the maximum value shown in the overtime graph to the right because each bar in the graph aggregates a number of time slices. |
Minimum. The minimum value of the counter, calculated from all time slices in the selected time frame.
This value may be different from the minimum value shown in the overtime graph to the right because each bar in the graph aggregates a number of time slices. |
Description. The description of the counter.
If the text in the Explanation field is cut off, hold the cursor over the Explanation description to view all the rest of the text. |
You can view information on the entities associated with the selected Performance Counter or Counter Instance entities in the Association area. It is possible to associate to the Performance Counter or Counter Instance entities from the Instance, Database, Disk or Network Card entities. When choosing a specific performance group the description of the counters are displayed. When drilling down to a counter with instances, the minimum, maximum and average values for the counter instances are displayed. For more information about the Main and Association areas, see About the Main area and About the Association area.
The Wait Events entity displays the name of the wait event group, the number of waits experienced by this event group and the average amount of time accumulated by wait events.
The Overview displays general details of the selected counter. The following information is available:
The Latch Event entity displays the name of the latch event group, the number of waits experienced by latches in this event group and the average amount of time accumulated by latch waits.
The Overview displays general details of the selected counter. The following information is available:
To determine whether SQL Server is performing optimally it is necessary to analyze performance measurements over time. The Statistics tab provides many performance counters grouped into several predefined graphs that enable you to locate performance problems in your system, such as, CPU, paging, I/O and network statistics. In addition, the In MS-SQL states enable you to correlate between the performance counters and the In MS-SQL states to determine how an activity in the SQL Server instance affects the system. The tab can be used to monitor your system's current state as well as historical state.
Performance tuning examples:
You can examine an SQL Server instance over time to confirm that memory usage falls within the normal range and that no processes, including SQL Server, are lacking or consuming too much memory. Lack of memory or a high memory consumption rate can cause paging. A page fault occurs when a process requires code or data that is not in its working set (its space in physical memory), thereby causing the page to be retrieved from the disk and written to the physical memory. If there is not enough space in the physical memory, a chosen page needs to be written to the disk to free space.
To examine memory statistics
You can examine an SQL Server instance over time to confirm that CPU usage falls within normal range. High CPU usage may indicate the need to improve the application or to add more processors to the machine to enhance performance. A low CPU usage indicates that the system is not using all the machine's processing power.
To examine CPU statistics
You can examine SQL Server I/O activity along with the entire server's I/O activity and disk performance, over time, to detect bottlenecks within the disk subsystem. High I/O activity and poor I/O performance may indicate the need to distribute the files more effectively over the disks to balance the I/O between disks or add more disks to enhance performance.
To examine I/O statistics
You can examine the network traffic of the server's network cards, over a specified time period, to detect bottlenecks. Network performance can be improved by one or more of the following:
To examine network activity
You can examine how the memory in the cache and buffer pool is divided between various objects such as procedure plans, ad hoc SQL plans, and trigger plans, by examining allocation pages vs. hit ratio. High memory consumption vs. a low hit ratio may indicate bottlenecks due to a lack of available physical memory for storing frequently accessed pages.
To examine the hit ratio of the various buffers in SQL server
Locks are held on SQL Server resources, such as rows read or modified during a transaction, to prevent concurrent updating of resources by multiple transactions. To minimize the locking cost, SQL Server locks resources automatically at a level relevant to the transaction. Locking at a lower level of granularity, such as rows, increases concurrency, but has a higher overhead because more locks are needed if many rows are locked. Locking at a higher granularity, such as tables, has an adverse effect on concurrency because locking an entire table restricts access to the entire table by other transactions, but has a lower overhead because fewer locks are being maintained. The Locking view of the Instance entity displays application locks, internal locks, lock escalations and the number of deadlocks to help you understand the locking behavior of your system. For example, if you observe that a large number of locks have timed out and that there are a large number of lock escalations, you can assume that the escalation causes many locks to time out and should therefore be prevented. In addition you can see how internal locks affect the application's locks.
To examine locking within the SQL server
A wide range of performance counters generated by the operating system are collected by the Precise for SQL Server Collector—some belonging to SQL Server and some to the OS itself. These performance counters are sampled every minute and displayed over time in this tab. When loaded into the PMDB, the counters are aggregated and maintained at a 15-minute time slice.
A performance group is an SQL Server performance group resource or an OS performance group, such as an SQL Server Lock. Each performance group contains a few counters that determine which performance aspects to monitor. For example, the SQL Server Access Methods performance group contains counters called "Pages Allocated" and "Pages Split."
Some performance counters have separate instances. An instance refers to a particular occurrence of a counter. For example, the SQL Server Locks performance group has counters such as "Lock Requests/sec" and "Lock Waits/sec," but because several lock levels exist, such as Database, Extent, Page, etc., each counter is divided into instances (in this case, the instances are lock levels).
To monitor a specific counter, choose the required performance group to associate it with, from the Associations list, and then choose the required counter.
To examine a specific counter over time
You can observe a breakdown of In MS-SQL waits into specific wait events, and examine the top ten wait events in the Wait Event view. You can also compare all the wait events associated with an instance according to the amount of time they waited, the number of times they waited, and the wait group they belong to. This allows you to easily identify the specific wait events the instance is waiting for. You can then tune these wait events, and free resources to the entire instance.
To examine In MS-SQL wait states
You can observe a breakdown of In MS-SQL latch wait states into specific latch wait events, and examine the top ten latch wait events, in the Latch Event view. You can also compare all the latch wait events associated with an instance according to the amount of time they waited, the number of times they waited, and the latch group they belong to. This allows you to easily identify the specific latch wait events the instance is waiting for. If latch wait activity is higher than you expected, consider increasing the amount of memory allocated to the SQL Server.
To examine In MS-SQL latch wait states
| | | | | | | |