Page History
This section includes the following topics:
...
...
...
...
...
| Anchor | ||
|---|---|---|
|
...
|
...
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.
...
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.
| Anchor |
|---|
...
|
...
|
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).
...
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 following tabs show additional information when All Instances is selected:
- CPU
■ CPU
■ Load
...
- Load
- I/O
...
- Memory
■ Memory
■ Network
- Network
If you want to view additional information on an associated entity, drill down to it, by selecting the entity's row. A
...
See “About drilling down in context” on page 27.
| Anchor |
|---|
...
|
...
|
The Statistics tab displays information on the following entities:
...
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.
...
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.
...
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
...
See “About the Main area” on page 25.See 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.
...
I/O Displays % Disk Write Time and % Disk Read Time.
About viewing information on entities associated with the selected Disk
...
See “About the Main area” on page 25.See and “About the Association area” on page 25.
About the Network Cards entity
...
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
...
See “About the Main area” on page 25.See and “About the Association area” on page 25.
About the Performance Counter and Counter Instance entities
...
Additional counters reported by Precise for SQL Server include:
■ Access Methods
■ Backup Device
...
■ Memory Manager
■ Network Interface
■ Physical Disk
■ Plan Cache
...
■ Transactions
■ User Setable
About getting an overview of Performance Counters or Counter Instances
The Overview displays general details of the selected counter and an overtime graph that represents the counter value. The following information is available:
Group The name of the counter's group.
Average The average value of the counter, within the selected time frame.
Maximum Shows the maximum value of the counter, calculated from all time slices in the selected time frame.
Note: 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.
Note: 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.
Note: 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.
Overtime graph Displays the counter's value over time.
About viewing information on the entities associated with a selected Counter
...
See “About the Main area” on page 25.See and “About the Association area” on page 25.
About the Wait Events entity (SQL Server 2005 only)
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.
About getting an overview of Wait Events
The Overview displays general details of the selected counter. The following information is available:
Wait group Displays the name of the wait type.
Wait name Provides a description of what is causing the event. Times waited (avg) Average time waited for wait events.
Wait for Resource graph Displays the time waited for wait events and the time waited for group events.
Waits graph Displays the number of wait events and the number of wait events for group events.
About the Latch Events entity (SQL Server 2005 only)
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.
About getting an overview of Latch Events
The Overview displays general details of the selected counter. The following information is available:
Wait group Displays the name of the latch class.
Wait name Provides a description of what is causing the event. Times waited (avg) Average time waited for latch events.
Wait for Resource graph Displays the time waited for latch events and the time waited for group events. Waits graph Displays the number of latch waits and the number of latch waits for group events.
| Anchor | ||
|---|---|---|
|
...
|
...
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:
■ Examining memory statistics
■ Examining CPU statistics
■ Examining I/O statistics
■ Examining network activity
■ Examining the hit ratio of the various buffers in SQL server
■ Examining locking within the SQL server
■ Examining a specific counter over time
■ Examining In MS-SQL wait states
■ Examining In MS-SQL latch wait states
Examining memory statistics
...
To examine memory statistics
...
- In the Time Frame list, choose the period of time you want to analyze.
...
- In the Instance list, choose the instance you want to analyze.
...
- On the View controls in the Main area, click Overview.
The Page Faults/sec graph displayed in the Overview of the Instance entity shows the page faults broken down into SQL Server page faults and non-SQL Server page faults. Analyze this graph together with the In MS-SQL and Load graphs to determine whether the paging is the source of the performance problems. If you conclude that excessive paging is the source of the performance problems, examine the amount of memory allocated to the SQL Server and reduce it, if necessary.
...
...
- On the View controls in the Main area, click Memory (Page Faults).
- The Memory (Page Faults) view displays page faults vs. memory allocation broken down into SQL Server and non-SQL Server components. Compare these graphs to determine whether server memory is being allocated properly and whether the physical memory needs to be expanded or reduced to enhance performance.
...
- On the Association controls in the Association area click Memory - Counters.
...
- Drill down to the Available MBytes and Pages/sec counters to analyze memory usage.
Examining CPU 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
...
- In the Time Frame list, choose the period of time you want to analyze.
...
- In the Instance list, choose the instance you want to analyze.
...
- On the View controls in the Main area, click Overview.
The CPU graph displayed in the Overview of the Instance entity shows CPU usage broken down into SQL Server processes and non-SQL Server processes. Analyze this graph together with the In MS-SQL and Load graphs to determine whether CPU usage is the source of the performance problems. If you conclude that high CPU usage is the source of the performance problems, consider adding more CPU or tuning the application in the SmarTune or Activity tabs.
...
- On the View controls in the Main area, click CPU.
The CPU view displays CPU usage broken down into SQL Server processes and non-SQL Server processes vs. processor queue length. Compare these graphs to determine whether a high level of CPU utilization is causing processor bottlenecks and if the CPU is being consumed by SQL processes or other processes.
...
- On the Association controls in the Association area click one of the following:
...
- Process - Counters To analyze the I/O activity of the SQL Server process.
- Processor - Counters To check CPU balance.
- System - Counters To check queue length.
Examining I/O 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
...
- In the Time Frame list, choose the period of time you want to analyze.
...
- In the Instance list, choose the instance you want to analyze.
...
- On the View controls in the Main area, click Overview.
The I/O graph displayed in the Overview of the Instance entity displays the I/O activity of the entire machine. Analyze this graph together with the In MS-SQL and Load graphs to determine whether I/O activity is the source of the performance problems. If you conclude that increased I/O activity is the source of the performance problems, examine I/O wait in the SmarTune or Activity tabs, or check if the relevant storage Point is installed, in the Objects tab.
...
- On the View controls in the Main area, click I/O (Load).
The I/O (Load) view displays SQL Server I/O activity vs. the entire server's activity. Compare these graphs to determine whether most of the I/O activity is being generated by SQL Server.
...
- On the View controls in the Main area, click I/O (Performance).
The I/O (Performance) view displays disk sec/IO operations vs. the disk queue length. Compare these graphs to determine whether poor performance is due to a long I/O request queue.
...
- On the Association controls in the Association area, click Disks. Drill down on a Disk entity to determine if it is experiencing poor performance and high I/O activity and if the load is balanced.
...
...
Examining network activity
...
■ Enhancing the application
■ Reducing the amount of data returned
■ Configuring the SQL Server instance “network packet size (B)” parameter with a reasonable number that would avoid many packets from being sent or received (causing overhead)
■ Reconfiguring the network configuration
To examine network activity
...
- In the Time Frame list, choose the period of time you want to analyze.
...
- In the Instance list, choose the instance you want to analyze.
...
- On the View controls in the Main area, click Overview.
The Network graph displayed in the Overview of the Instance entity shows the maximum packets (of all network cards defined on the server) that were sent or received, during the specified time period. Compare this graph with the In MS-SQL and Load graphs to determine whether the network activity caused the SQL Server's performance to suffer.
...
- On the View controls in the Main area, click Network.
The Network view displays network traffic vs. output queue length. Compare these graphs to determine whether a long queue length is the cause of heavy network traffic.
...
- On the Association controls in the Association area, click Network Cards. Drill down on a Network Card entity to locate the network cards with the most activity. Check if you can balance activity load across network cards. If not, consider adding a new network card.
...
Examining the hit ratio of the various buffers in SQL server
...
To examine the hit ratio of the various buffers in SQL server
...
- In the Time Frame list, choose the period of time you want to analyze.
...
- In the Instance list, choose the instance you want to analyze.
...
- On the View controls in the Main area, click Memory (Buffer Pools).
The Buffer Pool view in the Instance entity displays cache and database pages to hit ratios.
...
...
Examining locking within the 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
...
- In the Time Frame list, choose the period of time you want to analyze.
...
- In the Instance list, choose the instance you want to analyze.
...
- On the View controls in the Main area, click Locking.
The Locking view lets you compare locking data to lock escalations.
...
- On the Association controls in the Association area, click Locks - Counter. View the counters associated with the locks and compare the locks being implemented by the various resources (such as, Table, Key and RID).
...
- Drill down to a specific counter to view additional information on that counter, for the specified time period.
...
- If you identify a blocking situation, drill down to the Activity tab and continue to explore the Locks.
...
...
Examining a specific counter over time
...
To examine a specific counter over time
...
- In the Time Frame list, choose the period of time you want to analyze.
...
- In the Instance list, choose the instance you want to analyze.
...
- On the Association controls in the Association area, choose the required performance group the counter is associated with and drill down to it to view additional information on that counter, for the specified time period. For example, if you want to view information on the amount of physical memory available to processes running on the computer, on the Association controls, click Memory - Counters, and drill down to the Available MBytes counter.
Examining In MS-SQL wait states
...
To examine In MS-SQL wait states
...
- In the Time Frame list, choose the period of time you want to analyze.
...
- In the Instance list, choose the instance you want to analyze.
...
- On the Association controls in the Association area, click Wait Events. View the wait events listed in the
...
- Association area. Observe to which wait group an event belongs.
Compare the event in one wait group to an event in a different wait group to observe how much time each wait event spent in the SQL Server.
...
- Drill down on a wait event entity and observe its performance over time.
Examining In MS-SQL latch wait states
...
To examine In MS-SQL latch wait states 1
- In the Time Frame list, choose the period of time you want to analyze.
...
- In the Instance list, choose the instance you want to analyze.
...
- On the Association controls in the Association area, click Latch Events. View the latch events listed in the
...
- Association area. Observe to which latch group an event belongs.
Compare the event in one latch group to an event in a different latch group to observe how much time each wait event spent in the SQL Server.
...
...
- Drill down on a latch event entity and observe its performance over time.
...
- You can continue your analysis by viewing the hit ratio in the Memory (Buffer Pools) view. If the hit ratio is below
...
- 99% consider adding RAM to the SQL Server. If the hit ratio exceeds 99%, consider improving the speed of your
...
- I/O system.
...
Precise. Performance intelligence from click to storage. Learn more > >
...