Foglight for SQL Server (Cartridge) 7.1.0


	NOTE: The pane’s initial view only shows the OS name; to display the build and service packs as well, hover the mouse or click the OS name’s text.

•

Response Time — the time (in milliseconds) that elapses from the moment a query, which is supposed to represent the general workload, is submitted, until the application indicates that the query was run.

Because the response time is usually the starting point for investigation, the Response parameter leads to the SQL Instance Summary panel in the SQL Activity drilldown.

•

CPU (%) and Memory (%) — the average CPU load and memory consumption (percentage), during the specified time range, of all CPU units that host the SQL Server instance. This indicator displays the share of SQL Server-incurred CPU load and memory consumption within the total figure.

Clicking the number on both icons displays a pop-up that shows the total CPU usage or memory consumption on the currently monitored host, plotted over time.

•

No. of CPUs — the number of CPU units on the instance’s host.

•

Total RAM — the total amount (in megabytes) of the host’s physical memory.

•

V. Overhead — the percentage of CPU that is unavailable to this virtual machine because it is utilized either by other virtual machines or by VMware itself.


	NOTE: The virtualization overhead metric holds value only for virtual hosts running on VMware ESX servers. Clicking the virtualization overhead indicator displays the Virtualization Resource Utilization pop-up.

•

Free RAM — the total amount (in megabytes) of physical memory available to the applications.

Tracking the Instance Data Flow

The following components represent the data flow in the SQL Server operation, from the session to the physical disk storage:

•

The Sessions pane allows examining all sessions running on the instance, distributed between the following session types:

•

Active vs. inactive sessions

•

System (background) vs. User (foreground) sessions

This pane displays the activity on the client side, including the flow of data (packets and batches of SQL Statements) from the client to the Server and (in the case of packets) back. For details, see Monitoring General Session Statistics .

•

The Process Activity pane — displays several indicators about the instance’s sessions, such as the total number of processes and the number of blocked processes. In addition, this pane allows you to track the rate of the data flow used by SQL connections — compiles and logical page reads, that is, referencing pages in the buffer cache. For details, see Data flows.Monitoring total activity .

•

The Background Processes pane — used for tracking the Replication and Services background processes. For details, see Monitoring Background Processes .

•

Memory Activity pane — displays the size of the total SQL memory (both physical RAM and buffer pool), and allows ensuring that the SQL Server appropriately uses its dynamic memory management. The SQL Memory pane’s indicators may point out inefficient memory management, which leads to unnecessary use of physical I/O operations. For details, see Monitoring the SQL memory management .

•

Physical I/O operations — displays representations of physical I/O activity. While such activity is necessary to carry out certain operations (for example, accessing a new table), a high level of physical I/O activity may indicate poorly coded SQL or inadequate indexes, which prevent logical reads. For details, see Tracking physical I/O activity .

•

Disk Storage pane — displays the state of the physical disk storage, that is, databases and their backup, data files, and log files. This pane features indicators that refer either to lack of disk space (when a data file is close to reaching its predefined storage ceiling), or performance issues such as high disk queue length.

In addition, the Disk Storage pane displays the Log Flush wait time alarm, which may indicate an issue with the logical disk activity. For details, see Monitoring the disk storage .

Monitoring General Session Statistics

The Sessions pane monitors all session types, that is, system, user and SQL Server Agent sessions. Using this pane allows viewing the response time compared with the number of sessions and the instance’s level of activity. A high response time value may result from a long queue, that is, an overly high percentage of active users.

Long queues can indicate one of the following issues:

•

A massive workload — the system handles more users than it was initially designed to do.

•

A bottleneck — lack of system resources prevents users from carrying out their transactions, resulting in wait events and an increasing number of sessions that remain active for prolonged periods.

The Sessions pane displays the total number of sessions, distributed according to the following distinctions:

•

Active vs. inactive sessions

•

System (background) vs. User (foreground) sessions

The parameters in this pane lead to the SQL Activity > Sessions panel. For details, see Reviewing the SQL Server Instance Activity and Reviewing Session Details , respectively, in Reviewing the Instance Activity .

The client applications represented graphically in the Sessions pane communicate with the SQL Server by sending and receiving network packets and by submitting SQL statements for execution by SQL Server. The flows, detailed in the following table, help indicate performance issues if their values are too low.

Table 15. Data flows.Monitoring total activity


Flow	Description
Batches	The rate at which batches of SQL statements are being submitted to SQL Server for execution.
Received packets	The rate at which the SQL Server receives network packets from client applications. When this icon is yellow, clicking it displays text that describes theBlocking Alarm deviations that triggered this display, along with a representation of the rate at which SQL Server is encountering network packet errors.
Sent packets	The rate at which network packets are being sent from SQL Server to client applications. When this icon’s color is yellow, clicking it displays text that describes the deviations that triggered this display, along with a graphic representation of the rate at which SQL Server is encountering network packet errors.

The Process Activity pane allows monitoring the processes, both system and user sessions, run by the SQL Server. This pane also features other performance indicators, such as total number of lock requests per second and CPU usage, and allows accessing the error log.

Table 16. The Process Activity pane displays the following parameters:
Parameter	Description
Total Processes	The total number of SQL Server processes, including both user and system processes.
Blocked Processes	Number of processes that are waiting for another process to release a resource that the process is currently locking. Blocked processes can sometimes lead to bottlenecks. The Blocked Process indicator changes its color when one or more processes become blocked. For details, see Blocking Alarm and Deadlocks Alarm .
Active transactions (No.)	The average number of active transactions for each database of the monitored instance.
Error Log	The SQL Server and SQL Agent error logs. When scanning is enabled, Foglight for SQL Server scans the SQL Server logs and raises alarms upon finding error messages that contain any of the error log alert rules. These rules can be specified for all connections, or for the current connection, using the Log Scanning view in the Databases Administration dashboard. For details, see Defining Error Log Filtering . Hovering over this icon displays the number of errors that were recorded in the SQL Server error log during the selected time range (by default, last 60 minutes). For details about the error log alarms, see Error Log Alarm .

For further details, see Reviewing the SQL Server Instance Activity .

To communicate with the SQL memory, the SQL processes use logical reads and compiles, graphically represented as flows in the homepage.

Please select your product:

To serve you better, please complete the Purpose of your Chat:

Recommended Solutions for Your Problem

Foglight for SQL Server (Cartridge) 7.1.0 - User Guide

Overview Dashboard

Identifying the Instance

Tracking the Instance Data Flow

Monitoring General Session Statistics