Foglight for SQL Server 5.7.5.41

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 14. 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 .
Parses	Total number of parse calls (both hard and soft).
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.

Tracking logical reads

The flow from the SQL Memory pane to the SQL Processes pane indicates the rate at which pages in the buffer cache are being referenced by SQL connections (logical page reads).

Normally, the majority of logical reads is satisfied from the cache, but if the required page is not yet in the cache, it is read from disk.

For details, see Viewing the SQL Server I/O activity .

Tracking compiles

The Compiles flow displays the rate of SQL compilations and recompilations per second.

Preferably, the most frequently used executable query plans should be retrieved from the procedure cache, thereby saving the need to compile, significantly reducing the utilization of CPU resources, and speeding up the response time of SQL Server queries. Recompiling, which is a CPU-intensive process that may degrade performance, can in certain cases be avoided through sound coding practices.

When many recompiles take place, the SQL Server’s CPU can become overloaded, thereby slowing down everything running on that computer. As a result, the Recompiles alarm is invoked. For details, see Recompiles Alarm .

The Call Rates chart, displayed in the SQL Instance Summary panel of the SQL Activity drilldown, details the use of compiles and re-compiles during the last 60 minutes. For details, see Monitoring the call rates .

Monitoring the SQL memory management

The Memory Activity pane allows monitoring the SQL Server’s dynamic memory management and ensuring its proper handling of the buffer pool.

Each page of memory used by SQL Server is assigned to one of several cache types. Each cache grows and shrinks in size as required. The main caches are the Buffer Cache, which stores a copy of the SQL Server’s most recently used database pages, and the Procedure Cache, which holds recently compiled query execution plans. Both these cache types should satisfy I/O requests and save the need for physical reads from the disk. The Buffer cache’s efficiency also affects the Page Life Expectancy, another indicator displayed in the SQL Memory pane.

Table 15. The SQL Memory pane displays the following parameters:
Summary section
Total (MB)	The total amount of memory, either fixed or dynamically allocated, which the SQL Server is currently using. When the amount of SQL Server memory available for immediate reuse drops below a threshold, the Free Buffers alarm is invoked. For details, see Free Buffers Alarm .
Memory Used	Displays the total amount of memory, either fixed or dynamically allocated, that the SQL server is currently consuming, scaled against the maximum amount of memory it can use. By default, SQL Server manages its total memory automatically, adjusting it to the varying memory requests of both SQL Server processes and Windows processes. Clicking this gauge allows accessing the Memory > Buffer Cache panel, which provides an in-detail view of SQL Server’s memory management. For details, see Viewing the Memory Summary .
Plan Cache Section
Total	Displays the amount of memory currently allocated to the plan cache (formerly known as procedure cache), a memory area used by SQL Server to hold recently compiled query execution plans stored procedures, triggers, ad hoc SQL, and so on. The value of this metric is calculated as follows: <pages> * 8K / 1024
Hit Rate	Displays the percentage of plan cache lookups that found the required plan already in the cache. If a matching plan is found, SQL Server does not need to compile the query/stored procedure. This can save a significant amount of CPU resources, and can speed up SQL Server queries. A low Plan Cache hit rate may lead to performing extra compilations, thereby degrading SQL Server performance by causing extra CPU load. In a such situation, the Procedure Cache Hit Ratio Alarm is invoked. For details, see Procedure Cache Hit Ratio Alarm . The plan cache hit rate is the average hit rate for all procedure cache object types, except for ad hoc SQL. This rate is calculated using a differential sampling method, which gauges only the last few sampled periods.
Buffer Cache Section
Total	Displays the amount of memory currently allocated to the buffer cache, including database, free, and stolen pages. The buffer cache, which is an in-memory copy of recently used database pages, is normally the largest memory cache used by SQL Server. If an SQL process needs to access a database page, finding this page in the buffer cache spares the SQL Server the need to read the page from disk, thereby significantly reducing the amount of disk I/O and speeding up queries. The value of this metric is calculated as follows: <pages> * 8K / 1024
Hit Rate	Displays the percentage of database page I/O requests that were satisfied from the buffer cache. High buffer cache hit rate indicates that SQL Server efficiently uses the memory allocated to its buffer cache. Low buffer cache hit rate, on the other hand, indicates that SQL Server is finding fewer pages already in memory, resulting in the need to perform more disk reads. Low buffer cache hit rate invokes the Buffer Cache Hit Ratio alarm. For details, see Buffer Cache Hit Ratio Alarm .
Page Life Expectancy	Displays the current Page Life Expectancy, that is, the length of time in seconds that a database page stays in the buffer cache before it is flushed out. Small values indicate that the buffer cache retains pages for short periods, as a result of high data cycling, and that the buffer cache is not being effective. Microsoft recommends 300 seconds as the minimum value for this metric; any less is indicative of a shortage of memory. For details, see Page Life Expectancy Alarm .

Please select your product:

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

Recommended Solutions for Your Problem

Foglight for SQL Server 5.7.5.41 - User Guide

Monitoring total activity

Tracking logical reads

Tracking compiles

Monitoring the SQL memory management