Page History
...
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). See “About the Precise bar” on page 24For 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.
...
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.
...
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. See “About For more information about displaying this data, see About drilling down in context” on page 27context.
| Anchor | ||||
|---|---|---|---|---|
|
...
For an explanation on how to define instance groups, see the Precise Installation Guideand Administration. The following table describes the information displayed in the Instance Grouping table.
Table 9- 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
...
The following table describes the information displayed in the tabs in the Association area.
Table 9- 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 For more information about the Main and Association areas, see About the Main area” area and “About About the Association area” on page 25area.
About the Instance entity
...
- 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:
- 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 36For more information about session states, see About session states.
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 pagesPages.
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
...
- 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 36For more information about session states, see About session states.
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 For more information about the Main and Association areas, see About the Main area” area and “About About the Association area” on page 25area.
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 For more information about the Main area” and “About the Association area” on page 25.About the Network Cards Association areas, see About the Main area and About the Association area.
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 For more information about the Main and Association areas, see About the Main area” area and “About About the Association area” on page 25area.
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
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 For more information about the Main and Association areas, see About the Main area” area and “About About the Association area” on page 25area.
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.
Precise. Performance intelligence from click to storage. Learn more > >
...
...