Foglight for SQL Server (Cartridge) 5.9.3.20 - User Guide

Viewing In-depth Data about the Instance

The Activity drilldown provides two levels of information about the currently diagnosed SQL Server instance:

The Activity panels allow carrying out the following tasks:

Reviewing the SQL Server Instance Activity

The SQL Instance Summary panel displays a breakdown for the selected time range (by default: last 60 minutes) of system and SQL Server activity metrics, such as CPU utilization and response time. The display also includes the inner division within the metrics (for example, total CPU utilization compared with CPU utilization by the SQL Server). All metrics are displayed in the Foglight for SQL Server real-time summary; however, the home page displays only the last snapshot (by default: 20 seconds).

This panel allows viewing the source of a performance issue, by displaying the most immediate causes for this issue. Because the SQL Instance Summary panel displays the immediate performance indicators, most in-depth analyses can be carried out in other panels of the Activity drilldown.

The SQL Instance Summary panel allows carrying out the tasks detailed in the following sections:

The Connected Sessions chart displays SQL Server session information. This chart contains a plot graph, which displays the number of SQL Server sessions over time.

Sessions are broken down into the following categories:

A high number of active user sessions, that is, non-system sessions that are actively processing in SQL Server or that are waiting on locks, may indicate wait events that prevent such sessions from completing their activity and moving to idle state. For example, a CPU-intensive operation may result in a CPU wait event.

A high number of idle user sessions can complicate their management and lead to significant resource consumption, as a result of the update and management operations required for such sessions.

For more in-depth information about the SQL Server’s sessions, go to the Sessions panel, described in section Viewing Session Details .

The CPU Utilization chart displays the amount of CPU being used by SQL Server compared with the total being used by all processes in Windows.

The top-consuming sessions in the system can be reviewed in the Sessions panel, by sorting the Sessions table by the CPU Usage column. For details, see Viewing Session Details .

Viewing the SQL Server I/O activity

The SQL Server IO chart displays the following types of I/O activity:

Under optimal work conditions, the SQL Server uses logical page reads to read pages from the buffer cache. However, required pages that do not yet reside in the cache are being read from disk using physical I/O operations.

A high value for the logical page reads indicates that SQL Server efficiently uses the memory allocated to its buffer cache. A high value for physical page reads, on the other hand, indicates that SQL Server is finding fewer pages already in memory, resulting in the need to perform more disk reads.

The SQL Server IO chart, which displays I/O activity for all sessions and for all SQL Server database files, is actually a summary of two different charts, found in the SQL I/O Activity panel. For details, see Monitoring the SQL Server physical I/O activity and Monitoring the SQL Server logical I/O activity . A more focused display can be obtained using the steps detailed below.

Response time is the full time (in milliseconds) it has taken a dummy query (by default: select 1) to get from the application to SQL Server and back.

Every time a real-time sampling interval starts (by default: 5 minutes), a query is sent and its response time value is displayed. Any value higher than 20 ms may indicate a performance issue.

The Response Time chart displays the response over the defined time range.

A hit rate indicates the rate at which SQL Server finds pages already in the cache memory, saving the need to carry out physical reads.

Hit rates are shown for the following cache types:

A buffer cache hit ratio value that continually goes below 90% may indicate the need for reviewing the memory settings.

Low procedure cache hit rates may indicate inefficient use of SQL cache memory, which increases the need for compilation.

To further investigate the reasons for the low cache hit rates, see Monitoring Buffer Cache-related Data and Monitoring Plan Cache-related Data . To review how the cache hit rate affects the number of compiles and recompiles, review the Call Rates chart.

The following metrics are charted:

When many recompiles take place, the SQL Server’s CPU can become overloaded, slowing down everything running on that computer. When a predefined threshold is exceeded, the Recompiles alarm is invoked. The Re-Compiles metric of this chart allows viewing whether the recompiling issue is a persistent one. For further details, see Recompiles Alarm .

Viewing SQL I/O Activity Data

The SQL I/O Activity panel provides information, represented graphically by charts, about the physical and logical activity of all SQL Server’s sessions. In addition, this panel includes a chart that displays various statistics relating to how SQL Server data is being accessed and updated (for example, page splits and page allocations). An extra chart featured in this panel, Disk Queue Length, displays the disk activity for each logical disk, including activity generated by non-SQL Server processes.

The charts displayed in this panel provide a summary of physical and logical I/O activity for all sessions. To view a breakdown of these activities per session, go to the Sessions panel and click the requested session.

The SQL I/O Activity panel allows carrying out the tasks described in the following topics:

The following table identifies each of the main elements of the Foglight for SQL Server real-time summary page, and provides a link to display more information, corresponding to the logical dataflow within the Foglight for SQL Server Instance Homepage.

The main groups (panes) of gathered icons and gauges, which represent the main activity areas in the SQL connection process, are as follows: