Examining SQL Server instance statistics

This section includes the following topics:

• About the Statistics tab
• How the Statistics tab is structured
• About the entities you can examine
• How the Statistics tab can help you identify performance problems

About the Statistics tab

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.

How the Statistics tab is structured

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.

About the Main area in the Statistics tab

The Main area shows comprehensive information on the selected entity. You can choose from several views to examine the entity from different angles. For example, the Instance entity provides information on CPU usage and pressure, memory allocation, I/O activity, network activity, locking, and wait and latch events.

About the Association area in the Statistics tab

The Association area displays relevant information on the entities associated with the selected entity (displayed in the Tab Heading in the Main area) in a table format. For example, it is possible to associate to all Databases or Disks that are related to a specific Instance, by selecting an entity from the Association controls list. Notice that some entities, show additional information on the associated entities in different tabs. The tabs are located above the Association area table. Clicking on a tab displays different table columns showing different information for the associated entities.

The following tabs show additional information when All Instances is selected:

• CPU
• Load
• I/O
• Memory
• Network

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.

About the entities you can examine

The Statistics tab displays information on the following entities:

• Tier (when All Instances is selected)
• Instance
• Databases
• Disks
• Network Card
• Performance Counter or Counter Instance
• Wait Events
• Latch Events

For an explanation of the counters displayed in the Statistics tab, see the Microsoft performance monitor (perfmon) tool.

About the Tier entity

Choosing All Instances from the Instances list displays information on the Tier entity.

The Tier entity provides statistical information on all SQL Server instances in the application, in the following views:

• Overview
• Instance Grouping

About viewing a summary of statistical information on top instance consumers

The overview of the Tier entity provides statistical information on the top six instance consumers of various resources and enables you to compare them.

The Tier area shows the top six consumers, as follows:

• Top 6 Instances (% CPU). Displays the top six CPU consuming instances.
• Top 6 Instances (Paging). Displays top six instances with the most page faults.
• Top 6 Instances (Batch Requests/sec). Displays top six instances with the highest batch request rate.

About viewing statistical information on instance grouping

The Instance Grouping view displays performance counters broken down into instance groups (the instance groups are defined by the user). For example, you can view the avg SQL Server CPU usage in the Production group.

For an explanation on how to define instance groups, see the Precise Installation Guide. The following table describes the information displayed in the Instance Grouping table.

Table 1 Instance Grouping table

About viewing instances associated with an Tier in the Association area

You can view information on the Instances associated with the selected Tier in the Association Area.

The following table describes the information displayed in the tabs in the Association area.

Table 2 Viewing information on associated instances

For more information about the Main and Association areas, see About the Main area and About the Association area.

About the Instance entity

The instance entity displays statistical information for the entire instance. Precise for SQL Server collects hundreds of counters that are relevant to the entire instance. The Instance entity displays predefined graphs that display several counters in each. Each graph contains counters that have some relation to each other.

After drilling down to an instance entity, the following views are available for the Instance entity:

• Overview
• CPU
• 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.

For 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 Pages.

About viewing Memory (Buffer Pools) statistics for an Instance

The Memory Buffer Pools view displays statistics that report on Buffer Pool allocation and Hit Ratios. The following graphs are available:

• Cache and Database Pages. Displays Trigger Plans Cache Pages, Procedure Plans Cache Pages, Prepared SQL Plans Cache Pages, Ad hoc Plans Cache Pages and Database Pages.
• Hit Ratios. Displays Trigger Plans Hit Ratio, Procedure Plans Hit Ratio, Prepared SQL Plans Hit Ratio, Ad hoc SQL Plans Hit Ratio, and Buffer Cache Hit Ratio.

About viewing Network statistics for an Instance

The Network view displays statistics that report on network activity. The following graphs are available:

• Packets/sec. Displays Max Packets received/sec and Max Packets Sent/sec.
• Output Queue Length. Displays the Max Output Queue Length counter.
• Bytes/sec. Displays Max Bytes Received/sec and Max Bytes Sent/sec.

About viewing Locking statistics for an Instance

The Locking view displays statistics that report on locks and latches. The following graphs are available:

• Latch Waits/sec. Displays the Latch Waits/sec counter.
• Lock Timeouts/sec. Displays the Lock Timeouts/sec counter.
• Table Lock Escalations. Displays the Table Lock Escalations counter.
• Deadlocks and Lock Timeouts. Displays the Deadlocks and Lock Timeouts counter.

About viewing Wait Events for an instance

The Wait Event view displays statistics on the top ten wait events for the selected instance.

About Latch Events for an instance

The Latch Event view displays statistics on the top ten latch events for the selected instance.

About CPU Pressure for an instance

The CPU Pressure view displays statistics on CPU pressure for the selected instance. The stacked graphs show the breakdown of total wait time into the following:

• Wait for Resource
• Wait for CPU
• % Wait for CPU

If the CPU wait time exceeds the resource wait time, consider adding additional CPU or faster CPUs, or perform SQL tuning.

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.

About the Database entity

The Database entity displays SQL Server database statistics. Precise for SQL Server collects many counters that are relevant to the database. The database entity displays predefined graphs that display several counters in each. This enables you to immediately view relevant counters, grouped according to topic.

After drilling down to a database entity, the following views are available:

• Overview
• Transactions
• Log Flushes
• Log Growths or Shrinks
• 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.

For 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. For more information about the Main and Association areas, see About the Main area and About the Association area.

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. For more information about the Main and 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. For more information about the Main and Association areas, see About the Main area and About the Association area.

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 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 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.

  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.

• 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. For more information about the Main and Association areas, see About the Main area and About the Association area.

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.

How the Statistics tab can help you identify performance problems

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

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

1. In the Time Frame list, choose the period of time you want to analyze.
2. In the Instance list, choose the instance you want to analyze.
3. 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.
4. 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.
5. On the Association controls in the Association area click Memory - Counters.
6. 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

1. In the Time Frame list, choose the period of time you want to analyze.
2. In the Instance list, choose the instance you want to analyze.
3. 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.
4. 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.
5. 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

1. In the Time Frame list, choose the period of time you want to analyze.
2. In the Instance list, choose the instance you want to analyze.
3. 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.
4. 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.
5. 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.
6. 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

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 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

1. In the Time Frame list, choose the period of time you want to analyze.
2. In the Instance list, choose the instance you want to analyze.
3. 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.
4. 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.
5. 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

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

1. In the Time Frame list, choose the period of time you want to analyze.
2. In the Instance list, choose the instance you want to analyze.
3. 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

1. In the Time Frame list, choose the period of time you want to analyze.
2. In the Instance list, choose the instance you want to analyze.
3. On the View controls in the Main area, click Locking.
   The Locking view lets you compare locking data to lock escalations.
4. 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).
5. Drill down to a specific counter to view additional information on that counter, for the specified time period.
6. If you identify a blocking situation, drill down to the Activity tab and continue to explore the Locks.

Examining a specific counter over time

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

1. In the Time Frame list, choose the period of time you want to analyze.
2. In the Instance list, choose the instance you want to analyze.
3. 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

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

1. In the Time Frame list, choose the period of time you want to analyze.
2. In the Instance list, choose the instance you want to analyze.
3. 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.
4. Drill down on a wait event entity and observe its performance over time.

Examining In MS-SQL latch 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

1. In the Time Frame list, choose the period of time you want to analyze.
2. In the Instance list, choose the instance you want to analyze.
3. 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.
4. Drill down on a latch event entity and observe its performance over time.
5. 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.

IDERA |  Products | Purchase | Support |  Community |  Resources |  About Us  | Legal