Page History
...
- Access Methods
- Backup Device
- Buffer Manager
- Buffer Partition
- Cache Manager
- Databases
- Exec Statistics
- General Statistics
- Latches
- Locks
- Memory
- Memory Manager
- Network Interface
- Physical Disk
- 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
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
- 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.
Info
...
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.
Info
...
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.
Info
...
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
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.
See “About the Main area” 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 | ||||
|---|---|---|---|---|
|
...
Performance tuning examples:■ Examining
...
...
...
...
...
...
...
...
...
| Anchor | ||||
|---|---|---|---|---|
|
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.
...
- 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.
...
- 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 Counters. To analyze the I/O activity of the SQL Server process.
- Processor - Counters To Counters. To check CPU balance.
- System - Counters To Counters. To check queue length.
| Anchor | ||||
|---|---|---|---|---|
|
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.
...
- 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.
| Anchor | ||||
|---|---|---|---|---|
|
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:■ Enhancing
- 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.
| Anchor | ||||
|---|---|---|---|---|
|
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.
...
- 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.
| Anchor | ||||
|---|---|---|---|---|
|
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.
...
- 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.
| Anchor | ||||
|---|---|---|---|---|
|
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.
...
- 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.
| Anchor | ||||
|---|---|---|---|---|
|
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.
...
- 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.
| Anchor | ||||
|---|---|---|---|---|
|
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.
...
- 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.
...