Foglight for SQL Server (Cartridge) 5.7.5.30


	IMPORTANT: To create a custom filter for this table, use the options accessible by clicking the Customizer button at the table’s upper right side. For details, see Components Shared by all Foglight for SQL Server Screens.

To improve performance, Foglight for SQL Server limits the number of records that can be displayed in this table to 300 rows, but by default, a maximum of 20 records are displayed.


	NOTE: To define global settings about data retrieval to the Buffer Cache table panel, use the Buffer Cache view in the Databases Administration dashboard. To access this view, click the In-context actions button at the upper right side of the screen and then select Agent settings. For details, see Setting Options for Displaying Data in the Buffer Cache.

Table 9. The Buffer Cache table contains these columns:

Column

Description

Database

The name of the database where the object resides.

Owner

The user name of the object's owner.

Table

The table name (if the object is a table).

Index

The index name (if the object is an index).

If this column is left empty, the object is a heap.

Cached Size

The amount of cache used by the object.

Index ID

The index identifier (if the object is an index).

This metric can have one of the following values:

•

0 — indicates that the object is a heap and not an index.

•

1 — indicates that the object is a clustered index.

•

> 1 — indicates that the object is a non-clustered index.

File Group

The file group where the object resides.

% of Object

The percentage of object in the cache.

% of Cache

The percentage of buffer cache used by the object.

Because the process of determining the largest objects in the cache is highly CPU-intensive, this table can take some time to populate.

Tracking the Page Allocations

The Page Allocations chart displays the amount of memory allocated to database pages, free pages, and stolen pages over the selected time range (by default, last 60 minutes).

High volume of database pages requested by SQL Server can sometimes indicate that data is not properly indexed or that the query optimizer is not using the most efficient index.

Free pages values that are consistently close to zero may indicate use of inefficient queries, or shortage of SQL Server cache memory. The value of the Free Pages metric is calculated as follows: <free pages> * 8K / 1024. For details, see Free pages.

Constantly high value of stolen pages (pages that were taken from the buffer cache to satisfy other memory requests) may indicate an overall system memory shortage. For details, see Stolen pages.

Monitoring the Buffer Cache Hit Rates

The Buffer Cache Hit Rates chart displays the buffer cache hit rate over the specified time range.

The buffer cache hit rate is the rate of logical reads being satisfied from the buffer cache, saving the need to carry out physical reads.

The buffer cache contains database, free, and stolen pages, and its size is calculated as follows: <pages> * 8 K / 1024.

The buffer cache hit rate should normally exceed 90%; hit rate values that are constantly lower than 90% may result from extensive data operations, due to inefficient query planning.

Monitoring Plan Cache-related Data

The Plan Cache panel displays information about the SQL Server's plan cache, which holds execution plans for stored procedures, triggers, ad hoc SQL, and so on. This panel can be used for tracking the plan cache hit rate, and, if the rate it too low, investigate which object plans are used frequently.

The various panes of the Plan Cache panel allow carrying out the tasks described in the following sections:

•

Monitoring the Plan Cache

•

Reviewing Statistics about Plan Cache Objects

Reviewing Object Types

SQL Server 2005 and later objects

In SQL Server 2005 and later versions, the following object types can appear in the chart:

•

Bound Trees — normalized trees for views, rules, computed columns, and check constraints. For details, see glossary definition of Bound trees.

•

Extended Stored Procedures — a SQL Server object that dynamically loads and runs a function within a dynamic-link library (DLL) in a manner similar to a stored procedure. For details, see glossary definition of Extended stored procedures.

•

Object Plans — query plans generated by creating a stored procedure, function, or trigger. For details, see glossary definition of Object plans.

•

Replication Procedure Plans — query plans of a replication system stored procedure. For details, see glossary definition of Replication procedure plans.

•

SQL Plans — query plans corresponding to statements prepared using sp_prepare, sp_cursorprepare, or using auto-parameterization. For details, see glossary definition of SQL Plans.

•

Temporary Tables and Table Variables — temporary tables are session-specific tables, that is, the tables are automatically dropped when the session is closed. Table variables, on the other hand, are created in the memory and exist there until the running of a single Transact-SQL (T-SQL) batch is completed. For details, see glossary definition of Temporary tables and table variables.

Monitoring the Plan Cache

The Plan Cache table displays objects that are currently stored in the plan cache.

The plan cache contains executable plans for Transact-SQL, including objects such as stored procedures, triggers, views and defaults, as well as ad hoc and prepared SQL.

To improve performance, Foglight for SQL Server limits the number of records that can be displayed in this table to 300 rows, but by default, a maximum of 20 records are displayed.


	NOTE: To define global settings about data retrieval to the Plan Cache table, use the Plan Cache view in the Databases Administration dashboard. To access this view, click the In-context actions button at the upper right side of the screen and then select Agent settings. For details, see Setting Options for Displaying Data in the Plan Cache.

Foglight for SQL Server retrieves the largest plan cache entries first, so if any entries are not displayed, they are the smallest ones.

Table 10. The Plan Cache table contains the following columns:

Metric

Description

Database

The database where the object resides.

An object can have one of the following types:

•

Stored procedure

•

Prepared statement

•

Ad hoc query

•

Replication procedure

•

•

•

•

•

•

•

The number of times this cache object has been used since the plan was created.

Reference Count

The number of objects that reference this cache object.

Used Size

The amount of space in the plan cache, in megabytes, which is allocated to this object.

% Cache

The percentage of plan cache used by this plan.

SQL Query Text

The name of the procedure, or the first 128 bytes of the batch submitted.

Language

The language of the connection that created the cache object.

Each connection can individually set an SQL Server language to be used for the connection.

NOTE: Running identical plans that use several, incompatible language formats decreases the plan cache hit rate. To avoid language issues, ensure that all identical plans use the same language.

Date Format

The date format used by the connection that created the cache object.

NOTE: Running identical plans that use several, incompatible date formats decreases the plan cache hit rate. To avoid date issues, ensure using the same date format for all applications.

Tracking Memory Allocation to the Plan Cache by Object Type

The Cache Size by Object Type chart displays the amount of memory allocated to the plan cache over time, and breaks it down by object type. This display allows viewing trends of plan usage, which can be used for improving the queries used for retrieving the plans.

Reviewing Statistics about Plan Cache Objects

The Object Types chart shows various statistics for each type of plan cache object.

Figure 11. The Object Types chart.

The Object Types list can be used for selecting the metric to be displayed in this graph. Each metric is displayed for each type of plan cache object.

Table 11. The Object Types chart displays the following metrics.
Metric	Description
Hit Rate	The rate of logical reads that were satisfied from plans existing in the plan cache, divided by plan types.
Number of Objects	The number of objects of each type that are currently stored in the plan cache.
Use Rate	The rate at which each type of plan cache objects is being run (used).

Reviewing the Instance Activity

The Activity drilldown provides access to graphs that display both current and recent activity details for the currently monitored SQL Server instance.

The first section contains instructions regarding the use of the various panels of the Activity drilldown for carrying out a root-cause analysis of possible performance issues. The following sections provide in-depth data about each panel.

Viewing In-depth Data about the Instance

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

•

An overview of the activity in the currently monitored database, using the SQL Instance Summary panel.

This panel can be used for indicating specific performance issues, such as high response time and low cache rates. Most of the possible reasons for a slow response time can be initially traced by using the charts in this panel. For details, see Reviewing the SQL Server Instance Activity.

•

More detailed information, using the other panels.

Each of the other panels allows carrying out a root-cause analysis of possible performance issues indicated in the SQL Instance Summary panel.

The Activity panels allow carrying out the following tasks:

•

Viewing SQL I/O activity data — using the SQL I/O Activity panel, which provides graphical representations of various I/O activities of the currently diagnosed SQL Server instance. For details, see Viewing SQL I/O Activity Data.

•

Viewing session data — using the Sessions panel, which lists all current SQL sessions and allows viewing session details and locks. For details, see Reviewing Session Details.

•

Viewing detailed statistics about locks and latches — using the Locks panel, which displays information about all locks, latches and requests on the currently diagnosed SQL Server instance. For details, see Monitoring Locks and Latches.

•

Viewing statistics about current lock conflicts — using the Blocking (Current) panel, which displays information about all current blocked sessions and lock conflicts, including the connections and resources involved in these conflicts. For details, see Tracking Current Lock Conflicts.

•

Tracking deadlocks and their implications — using the Deadlocks panel, which displays all of the deadlocks that took place within the selected time range, as well as the databases and objects that were involved in the deadlock situations. For details, see Tracking Deadlocks and their Affected Objects.

•

Viewing I/O Statistics by Database Files — using the I/O by File panel, which displays current I/O statistics for each SQL Server file. For details, see Viewing I/O Statistics by Database Files.

•

Viewing the Real Activity data — represent the main activity areas in the SQL connection process. For details, see Viewing the Real-time Summary Page

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:

•

Monitoring connected sessions

•

Monitoring CPU utilization

•

Viewing the SQL Server I/O activity

•

Tracking the response time

•

Monitoring cache hit rates

•

Monitoring the call rates

Monitoring connected sessions

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.

Figure 12. The Connected Sessions chart.

Sessions are broken down into the following categories:

•

Active user sessions

•

Idle user sessions

•

Internal SQL Server System sessions

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.

Monitoring CPU utilization

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.

Figure 13. The CPU Utilization chart.

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:

•

Physical I/O activity — indicated by the rate at which pages are physically read from and written to disk by SQL Server.

•

Logical I/O activity — indicated by the rate at which pages in the buffer cache, a memory area used by SQL Server to hold recently accessed database pages, are being referenced by SQL connections (logical page reads).

Figure 14. The SQL Server IO chart.

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.

To view a breakdown of logical and physical reads per session:

Click Activity > Sessions to go to the Sessions panel.

Click the requested session in the Sessions table.

Review the details displayed in the Session Details pane.

For details, see Viewing Session Details.

To view current SQL Server I/O statistics for each SQL Server database file:

Click Activity > IO by File to go to the IO by File panel.

View the details displayed in the IO by File table.

For details, see Viewing I/O Statistics by Database Files.

Tracking the response time

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.

Monitoring cache hit rates

Figure 15. The Cache Hit Rates chart displays the hit rates for the main SQL Server cache resources.

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:

•

Buffer Cache — the hit rate for this cache should normally be over 90%.

•

Procedure Cache — the hit rate for this cache varies widely, depending on how well the application is written.

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.

Monitoring the call rates

Figure 16. The Call Rates chart displays the rate at which various events are occurring in SQL Server.

The following metrics are charted:

•

Batches — a batch is one or more transact SQL statements sent at one time from an application to the SQL Server instance. High rates of both batch, and compiles and re-compiles can sometime indicate a bottleneck, as a result of batches that are not using compiled plans, or identical batches that use a different plan or create a new one. To troubleshoot this issue, review the use of execution plans, date and language formats used.

•

Compiles — the rate at which an SQL stored procedure is being compiled into the procedure cache. The Compiles figure includes Re-Compiles.

•

Re-Compiles — the rate at which SQL Server is recompiling an SQL stored procedure in the procedure cache.

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:

•

Viewing the Real-time Summary Page

•

Monitoring the SQL Server physical I/O activity

•

Monitoring the SQL Server logical I/O activity

•

Viewing how SQL Server’s logical data is accessed and updated

•

Viewing Session Details

Viewing the Real-time Summary Page

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.

Table 12. Main elements of the Real-time Summary Page.


View Name	Description
Instance properties	Identifying the Instance
Sessions pane	Monitoring General Session Statistics
Background Processes pane	Monitoring Background Processes
Workload	The workload (average active sessions) graph for the SQL Server instance, plotted over the specified time range (by default: last 60 minutes).
Process activity	Monitoring total activity
Memory Activity pane	Monitoring the SQL memory management
Disk Storage pane	Monitoring the disk storage
Physical I/O operations	Tracking physical I/O activity

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

•

Instance identification — used for identifying the instance, its type, and its properties. See Identifying the Instance.

•

Components representing instance data flow — the main activity area in the Foglight for SQL Server Instance Homepage includes several panes and flows that represent the data flow in the SQL Server operation. The dataflow is described in this guide as a top-down design, that is: from the session to the physical disk storage. See Tracking the Instance Data Flow.

Overview Dashboard

Identifying the Instance

The Instance identification indicators allow you to identify the currently monitored instance, its type and its operation period.

These indicators are as follows:

•

Instance name — identifies the currently monitored instance.

•

Specified time range — indicates the period for which data is being displayed (by default: last 60 minutes).

•

Instance pane — contains the following indicators:

•

DB Type — identifies whether the monitored database’s type is SQL Server, Sybase, DB2, or Oracle.

•

DB Version — identifies the SQL Server version number, along with the most recently installed service packs.

•

Up Since — identifies the date and time when the instance was last started.


	NOTE: The pane’s initial view only shows the date; to display the time as well, hover the mouse on the instance status icon ().

•

OS Version — identifies the operating system’s version number, along with the most recently installed builds and service packs.


	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 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 13. Data flows.


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.

Monitoring total activity

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.

Tracking physical I/O activity

The physical I/O Operations section of the Foglight for SQL Server Instance Homepage displays graphical representations of the number of pages physically read from and written to disk by SQL Server.

In addition, this section allows viewing the SQL Server processes Checkpoint and Lazy Writes.


Parameter	Description
Physical Writes	A flow that represents data/index pages written to disk per second. Normally, SQL users do not have to wait for database write operations to complete, as most modifications to database pages are made in the buffer cache.
Physical Reads	A flow that represents page reads from the disk per second. Physical reads are used when a connection requests a page that is not already in the buffer cache. Physical read operations are necessary when accessing new table or index pages. Nevertheless, these operations should be avoided when possible, as requested pages should reside in the SQL Server buffer cache.
Log Flushes	A flow that represents the number of log pages per second being written to disk by the Log Writer process. For details, see the glossary definition of Log writer.

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

Monitoring the disk storage

The Disk Storage pane displays the status of the storage devices in the SQL Server system, that is, databases and their backup, data files, and log files. Using this pane allows identifying storage issues such as a database that has not been backed up or a data file that has almost exhausted its growth potential and is going to fill completely.

In addition, the Disk Storage pane displays the Log Flush wait time alarm, which may indicate excessive I/O operation.

Table 16. The Disk Storage pane.
Parameter	Description
Databases	The total number of available databases in the SQL Server instance. This icon displays an alarm when one or more of the databases becomes unavailable, or if any database has not been backed up in the last few days. For details about handling these alarms, see Recent Backups Alarm and Database Unavailable Alarm.
Data Files	The total number of data files in all databases of the SQL Server instance. The disk icon represents the total percentage of currently used space within the entire capacity of all data files that reside on all databases. The total percentage is also displayed in text at the bottom of this section, below the text that displays the physical size of data files space used. Additional indicators in this section display the total number of files and file groups in all of the Databases in the SQL Server instance.
Log Files	The log files in all databases of the SQL Server instance. The disk icon represents the total percentage of currently used space within the entire capacity of all log files that reside on all databases. The total percentage is also displayed in text at the bottom of this section. In addition, this section displays the physical size of log files space used and the total number of log files.
Disk Activity section
Disk Utilization	The percentage of time the busiest disk spent serving system-wide I/O requests. The Disk Utilization metric serves as a measure for the system I/O load. High values may indicate a device bottleneck, due to either disk fragmentation or I/O resource contention of multiple processes that try to write or read from the disk.
Disk Queue Length	The average number of I/O requests that are queued and waiting for an available disk during the sample interval. This figure may include I/O activity generated by both SQL Server and non-SQL Server processes. For details, see Viewing the disk queue length.

Monitoring Background Processes

The Background Processes pane displays the status of optional SQL Server components, such as the Replication Agent, which is implemented as a job of SQL Server Agent service, and several of the SQL Server services.

Some of the supporting services significantly enhance the database efficiency and accuracy, and therefore should be active at all times; for example, DTC, which ensures successful and complete transactions.

Table 17. The Background Processes pane.
Indicator	Description
Replication	Indicates whether SQL Server Replication is configured. If replication is configured, indicates whether there are replication errors. Replication is an optional component of SQL Server, which synchronizes data between different SQL Server databases.
Services	Status of the SQL Server services that are currently monitored. These services are supplementary to SQL Server and provide capabilities such as mail and full text search. This icon invokes an alarm when any of the SQL Server services are installed, but not active. For details, see Alarms Displayed in the Background Processes Panel. For details about the drilldowns used for handling the supporting services alarms, see Reviewing the Support Service Status.

Monitoring the SQL Server physical I/O activity

The SQL Server Physical I/O chart displays the rate at which pages are physically read from and written to disk by SQL Server. The read and write operations are carried out by either SQL Server processes or system processes.

Figure 17. The SQL Server Physical I/O chart.

Physical read operations are necessary when creating a table or an index page for the first time. Nevertheless, these operations should be avoided when possible, as requested pages should reside in the SQL Server buffer cache. High rates of these metrics indicate extensive data operations.

The following list presents the main physical I/O types shown in the chart:

•

Checkpoint Writes are the most common type of write activity under normal circumstances. The checkpoint process periodically scans the buffer cache for modified pages and flushes all modified pages out to disk, thereby minimizing the amount of work SQL Server is required to do on restart.

Checkpoint writes are carried out at intervals, which are defined by the recovery interval parameter. If this parameter’s value is too high, the checkpoint process may run infrequently, thereby overloading the Lazy Writer process. As a result, the Lazy Writer does not efficiently maintain the Free Pages list and, when a certain threshold has been exceeded, the Free Buffers alarm is invoked. For details, see Free Buffers Alarm.

•

Lazy Writer Writes are carried out when the Lazy Writer process needs to free up buffer pages that have been modified in the buffer cache. Freeing up the buffers requires the Lazy Writer process to write first the changed pages to disk.

High value of lazy writes may indicate that SQL Server is running out of available space in the buffer pool cache. Use this chart to view whether the high lazy writes value is a consistent issue.

When the amount of SQL Server memory available for immediate reuse drops below a certain threshold, the Free Buffers alarm is invoked. For details, see Free Buffers Alarm.

•

Physical Page Reads are carried out when a user connection requests a page that is not already in the buffer cache. The connection requesting the page awaits until the I/O operation completes.

•

Physical Page Writes are write operations where the user connection has to wait for the I/O to complete before continuing. These are most often caused by operations such as create index, bulk insert, or restore.

•

Read Ahead occurs when SQL Server forecasts the need for data that currently resides on the disk. In this case, the pages are pre-fetched into the buffer cache before being requested by the user, using Read Ahead processing.

Monitoring the SQL Server logical I/O activity

The SQL Server Logical I/O chart displays the number of Logical reads (getpage requests) issued by SQL Server.

SQL Server keeps a copy of its most recently used database pages in the buffer cache. When a connection needs to reference a database page, SQL Server performs a Logical I/O operation by checking the buffer cache to see if the requested page is already in memory. If the page is found in the buffer cache, a Logical I/O read is carried out; otherwise, the page is read from disk, using a Physical I/O operation.

Viewing the disk queue length

The Disk Queue Length chart displays the disk activity for each logical disk.

Disk Queue length tracks the average number of I/O requests that are queued and waiting for an available disk during the sample interval. This figure may include I/O activity generated by processes other than SQL Server. Values that exceed the threshold set in this metric may indicate a system bottleneck.


	NOTE: Storage Area Network (SAN) storage array may indicate high Disk Queues for sustained periods, even though no disk I/O bottleneck takes place. Therefore, it is advisable to check related information about read, write, and transfer operations, and determine whether there are pending SQL Server I/O operations, before assuming the occurrence of I/O bottlenecks.

Viewing how SQL Server’s logical data is accessed and updated

The Access Methods chart allows viewing various statistics used for monitoring the methods used for accessing and updating SQL Server data. These counters are all collected from the SQL Server Access Methods Manager.

Figure 18. The Access Methods chart.

Table 18. The Access Methods list allows you to choose one of the following counters:
Counter	Description
Page Splits	Displays the rate at which index database pages are being split into two because the page does not contain enough free space to accommodate an inserted or updated record.
Page Allocations	Displays the rate at which pages are being allocated to tables or indexes. Indicates how fast tables are expanding.
Page Deallocations	The rate at which pages are being de-allocated from tables or indexes, thereby indicating how fast tables are shrinking.
Freespace Scans	The number of scans initiated to search for free space to insert a new record. A high rate of freespace scans can indicate disk fragmentation.
Forwarded Records	The rate at which SQL Server is retrieving forwarded records. When a row in a table that does not have a clustering index is updated - and if the modified row no longer fits on the old page - SQL Server moves the row to a new page and leaves a forwarding pointer in the old page. Using this method saves the need to modify the non-clustered indexes in order to reflect the new row location; however, when this method is implemented, subsequent retrieval of this row using non-clustered indexes requires extra I/O resources. A high Forwarded Records rate can indicate a need to reorganize the existing tables (unload/reload) or define clustered indexes.
Ghosted Records	The rate at which SQL Server encounters ghosted records during scans. When a record is deleted from a table, SQL Server improves concurrency by not physically removing the row from index Leaf levels, but marking it instead as deleted (ghosted). At some later point, a housekeeping process asynchronously removes these rows from the leaf level. Until the records are removed, SQL Server must skip the ghosted records during leaf-level scans.
Table Lock Escalation	The rate at which locks are being escalated to the table level. A high table lock escalations rate may indicate a need to tune queries, in order to avoid designing multiple page locks that continually force SQL Server to carry out such escalations.

Viewing Session Details

The Sessions panel allows you to trace the activity of all currently opened sessions, as well as their resource consumption.

The Sessions panel is divided into 3 views:

•

Resource consumption - displays the current and last 1 hour resource consumption.

•

Current - displays all the current opened sessions.

•

Last 1 Hr. - Enabled only when SQL PI is configured. This view provides several significant features, which highly enhance data retrieval and display capabilities, viewing the top sessions that were executed in the instance during the last 1 hour and filtering them based on the resource consumption.

Reviewing Resource Consumption

The resource consumption section is divided into 2 time frames, Current and Last Hour.

The Current section allows viewing the consumption of various resources of all the sessions currently opened to the instance. The resource list is as follows:

•

Sessions — Displays the distribution of the currently opened SQL Server sessions between active (sessions that are actively processing in SQL Server) and inactive sessions during the current sampling period.

•

Throughput — Divided to 3 metrics which represent the throughput of the monitored instance:

•

Sessions/Sec — The rate per second of sessions that were initiated during the current sampling period.

•

Transaction — The average number of active transactions on the monitored instance during the current sampling period.

•

Batches/Sec — The rate per second of statement executions during the current sampling period.

•

Blocked — Displays the total number of SQL Server sessions that are blocked during the current sampling period.

•

CPU — The Host CPU Usage the total percentage of CPU resources being used on the monitored host. The displayed value represents the total CPU that is consumed by all Windows processes (SQL Server and non-SQL Server processes).

The Last 1 hour trend presents the activity on the instance during the last 1 Hour by presenting:

•

Workload — displays the general workload (average active sessions) during the specified time range.

•

Breakdown — displays the workload, distributed by the various wait event categories. The colors of the categories match the colors of the various resources displayed in the Resource Breakdown section.

By clicking the All Wait Events link at the upper right corner of the section, The Active Wait Events pop-up appears, with the Resource drop-down list unfiltered and displaying the entire list of wait events

Reviewing Current session details

The current sessions view allows you to monitor the currently running sessions and their individual resource consumption. The Session List section contains a table that lists all currently running SQL Server sessions, displaying each session in a separate row. The Sessions view in the table can be filtered by the Active only and Foreground Only check boxes:

•

Active only — selected by default, presenting only the active sessions that are currently running, by clearing the check box, all inactive sessions will be presented as well.

•

Foreground only — selected by default, presenting only the sessions whose SPID number is higher than 50, by clearing the check box, the background sessions will be presented as well which holds the instance sessions ID’s 1 through 50.

Table 19. The current Sessions table contains the information detailed below.
Column	Description
Kill	Allows termination of the selected session, using the Kill Session dialog box that is displayed upon clicking this column.
SPID	The session unique identifier (Session Process ID).
Status	Indicates whether the session is active, blocked or suspended.
DB User	The SQL Server login name for this session.
Database	The name of the database the user is accessing.
CPU Usage	Time spent by the various sessions consuming CPU cycles. The CPU usage value is read directly from the operating system.
Memory Usage	The portion, in megabytes, of the procedure cache allocated to this process. A negative number indicates that the process is freeing memory allocated by another process.
Active Time	Sum of all the active wait events, equal to the session total activity within the current interval.
Transaction Count	The number of open transactions. This parameter’s value corresponds to the value of the session’s @@trancount — a global variable that reflects the level of nested transactions.
Last batch	The latest batch ran by this process.
Login Time	The time when the session was created.
Program	The program that is the owner of this SQL Server session.
Host Name	The name of the client computer that established the SQL Server connection.
Last Batch Time	The time when the last batch started execution.
Blocked by	Which SPID (if any) holds locks on a resource on which this session is waiting.
Cache Hit Ratio	The percentage, for this session, of buffer cache hit ratio, that is, file read operations that were satisfied by the file system cache without requiring any physical I/O. The value of this metric should be as high as possible.
Physical Writes	The total number of data/index pages written to disk by the selected session Normally SQL users do not have to wait for database write operations to complete. Most modifications to database pages are made in the buffer cache.
Logical Reads	The total number of logical reads carried out by the selected session.
Physical Reads	The total number of physical reads carried out by the selected session.
Activity	Indicates whether the session is active.
Context Info	The context information of the current session.
Last Command	The currently executed or most recently executed command.
Lock Timeout	The amount of time that this session will wait for lock requests to be satisfied; corresponds to the @@Lock_Timeout global variable for this session.
Session Up Time	The amount of time since the session is started (dd:hh:mm:ss).
Resource Pool name	The name of the resource pool attached to the session
XTP Transactions	The number of In-Memory OLTP (XTP) open transactions by the session
Oldest Transaction Duration	The duration (in seconds) of the oldest transaction opened by the session


	NOTE: To accelerate the sessions' retrieval time, only the first 60 characters of the SQL text of each query are being retrieved.

The refresh rate of the data displayed on the Sessions panel can be set by selecting a value from the Refresh interval drop-down list, which appears on the panel's upper right side.

Reviewing Session Details

The various sections of the Session Details page provide detailed information about the selected session upon selecting a specific session in the table.

Configuring SQL PI presents in-depth analysis of the session details. See Reviewing Session details with SQL PI

Session Identification Section

Table 20. Displays several parameters that provide general information about the selected session, as follows:
Parameter	Description
Session ID	Session Process ID; the unique number that SQL Server has assigned to identify the selected session. NOTE: Clicking on the Session ID enables you to switch sessions from a pop-up list of current sessions.
Login Time	The time when the user logged on to the session.
Status	Indicates whether the session is active or inactive.
DB User	The SQL Server login name for this session.
Program	The name of the program the user is running to access SQL Server.
Machine	The name of the host the session is running from.
Waiting for:	The resource on which the session is waiting.

Reviewing Workload details

The Workload page displays the selected session workload (Seconds/Sec) during the specified time range (default 1 Hr.). The breakdown displays the workload, distributed by the various wait event categories. The colors of the categories match the colors of the various resources displayed in the Resource Breakdown section. By clicking the All Wait Events link at the upper right corner of the section, The Active Wait Events pop-up appears, with the Resource drop-down list unfiltered and displaying the entire list of wait events.

CPU Section

Displays the sum amount f CPU (in seconds) that the selected session is consuming.

IO Section

Displays the sum amount of IO (in pages) that the selected session is consuming.

Session Details Section

The Session Details section contains several tabs of information.

Reviewing the Most Recent SQL details

This tab provides details about the statement that is currently being executed by the selected session.

Table 21. This pane contains a table with the following columns:
Column	Description
SQL Batch	The full text of the SQL execution batch.
CPU Time	Total CPU time consumed by the statement
Physical IO Reads	The total number of physical I/O reads performed by the selected SQL statement.
Physical IO Writes	The total number of physical I/O writes performed by the selected SQL statement.
Active Time	Summary of all Sum of active wait events incurred by the SQL statement.
Logical Reads	The total number of logical reads carried out by the selected SQL statement.
Waiting for	The resource on which the session is waiting.

Reviewing Metrics

Additional columns are displayed in the Metrics table when SQL PI is configured. See .

Table 22. The Metrics pane contains the sum of activity for the selected session in a table that displays following metrics:


Column	Description
CPU Usage	Time spent using CPU. Calculated in seconds.
Transaction Count	Number of open transactions for the session.
Physical Reads	Total number of physical reads operations performed by all requests running in the instance.
Physical Writes	Total number of logical write operations performed by all requests running on the instance.
Logical Reads	Total number of logical read operations performed by all requests running on the instance.
Cache Hit Ratio	The ratio of logical reads to physical reads. It indicates the percentage of database page I/O requests that were satisfied from the Buffer Cache and therefore did not have to perform disk reads
Memory Usage	Amount of memory
Active Time	Sum of all the active waits and CPU usage, equal to the session total activity within the current interval.

Reviewing Session Trace details

The Session Trace pane uses SQL Server trace functionality to display the SQL events activity generated by the selected session. The retrieval of the data displayed in the Session Trace pane starts upon entering the pane, and retrieval continues throughout the session investigation run by Foglight for SQL Server. Two minutes after exiting the session pane, data retrieval for the specific session is automatically disabled.

Table 23. The Session Trace pane allows you to trace the following events:
Event	Description
RPC :Completed	Occurs when a remote procedure call (RPC) has completed
SQL :Batch Completed	Occurs when a Transact-SQL batch has completed.
Event Class	The type of class that was traced.
Start Time	The time when the class event started.
End Time	The time when the class event ended.
Duration	The duration of the class event in milliseconds.
Text Data	The query’s text. This column displays data only if the event class or classes being captured by the trace contain text.
Reads	The number of logical disk reads performed by the server on behalf of the event.
Writes	The number of physical disk writes performed by the server on behalf of the event.
CPU Usage	The amount of CPU time (in milliseconds) used by the event.
Database ID	ID of the database currently used by the session.
Object Name	System-assigned ID of the object.
Index ID	ID for the index on the object affected by the event.

Reviewing Session Blocks details

The Session Blocks pane displays all blocks held or requested by the selected session. The Lock Status column indicates whether the locks have been granted to the session, or are currently blocked and waiting on another session.

Table 24. The Session Blocks table displays the following columns:
Column	Description
Session ID	The session unique identifier (Session Process ID). The unique number that SQL Server has assigned to identify the selected session.
SQL User	The SQL Server login name for this session.
Type	The type of the currently locked resource (Database, table, page, row, extent, and more).
Status	The status of the lock, for example, running or blocked.
Wait time	The time the session is waiting on lock.
Resource	The name of the currently locked object.
Command	The currently executed command.
Program	The name of the program the user is running to access SQL Server.
Win user	The Windows login name for this session.
CPU	The amount of CPU time, in milliseconds, used by the event.
IO	The total number of I/O operations performed by the selected SQL statement.
Host Name	The name of the host the session is running from.
DB Name	The database on which the lock is taking place.
Session SQL	The full text of the SQL execution batch.

Reviewing Session Locks details

The Locks table displays information about all locks currently held or requested.

Table 25. The Locks table displays the following columns:
Column	Description
SPID	the server process ID of the current user process.
Database	The database being used by the current process.
Count	The number of locks of the type specified in the Lock Type column against the database specified in the Database column, from the SPID specified in the SPID column.
Index Name	The index being used by the current process, if any.
Object Name	The currently locked object.
Lock Type	The type of the resource that is currently locked. For example, database, table page, row, or extent.
Mode	The kind of lock being applied to the resource. For example, shared, exclusive, update, IntentShared, or IntentExclusive.
Status	The status of the lock: Blocked, Blocking, or blank (Granted)

Reviewing Session details with SQL PI

Configuring SQL PI enhances the Current session details by adding the next enhancements:

Reviewing SQL Summary details

The SQL Summary pane displays summarized data about each of the SQL Statements that were executed by the session. The SQL statements are sorted into a table that represents the activity of each SQL statement that ran or still running on the selected session.

Table 26. The SQL Summary table displays the following columns:
Column	Description
SQL Statement	The SQL statement’s text.
Rows	Total number of rows retrieved by the statement.
Active Time	Summary of all active wait events incurred by the SQL statement.
CPU Usage	Total CPU usage consumed by the statement.
CPU Wait	Time spent by waiting in the systems run queue for CPU cycles. This reading is calculated from the operating system readings, rather than the SQL Server wait states.
I/O Wait	Time spent waiting for disk input/output operations to complete.
Memory Wait	Time spent waiting by the various processes waiting for the completion of a log operation.
Network Wait	Network wait events occur when a session spends time waiting for messages to be sent or received over the network interface.
Lock Wait	Time spent waiting for a blocking lock, held by another session, to be released.
Log Wait	Time spent waiting for a Log operation to complete.
CLR Wait	Time spent waiting for CLR code execution to complete.
Remote Provide Wait	The time waiting for a remote OLEDB call to complete or DTS synchronization.
Other Wait	Time waiting for miscellaneous log waits.
SQL Batch	The full text of the SQL execution batch.
Elapsed Time	The amount of time, in seconds, that the SQL statement’s execution lasted.
Parallel Coordination Wait	Time spent by the various sessions waiting for parallel coordination tasks to complete. This is the time spent by the various processes coordinating parallel query threads and exchanging data.
Cursor Synchronization Wait	Time spent by the various sessions waiting for cursor synchronization operations to complete
Physical IO Reads	The total number of physical I/O reads performed by the selected SQL statement
Physical IO Writes	The total number of physical I/O writes performed by the selected SQL statement.
Logical Reads	The total number of logical reads carried out by the selected SQL statement.
Command Type	The type of the command that was carried out by the selected SQL statement. For example, insert, select.

The Activity Panel displays the SQL Batch that is executed for the selected SQL statement in the table and a pie chart which display the SQL statement workload activity.

Reviewing Metrics details with SQL PI

Selecting a metric row from the metric list present this metric activity in a chart. The data displayed in the chart represents the metric activity over the selected time frame.

Table 27. The following columns are added the metrics table when SQL PI is configured:
Column	Description
Backup Recovery	Time spent waiting for backup or recovery tasks to complete.
Cursor Synchronization	The time spent synchronizing information flow within cursors.
Database Replication	Time spent waiting for replication synchronization events to complete.
Deferred Task Worker	Time spent waiting for I/O requests.
Distributed Transaction	Time spent waiting for various distributed transaction events to complete.
External Procedure	Time spent waiting for external procedures to end.
Full Text Search	Average CPU consumption by the Full Text Search service.
Hosted Component	Time spent waiting for hosted components such as, but not exclusively, CLR.
I/O Bulk Load	Time spent waiting for the completion of I/O operations required to carry out a bulk load I/O.
I/O Completion	Time spent waiting for I/O operations to complete.
I/O Data Page	Time spent waiting to latch a buffer for an I/O request.
Internal Cache Latch	Time spent waiting for latches that are not buffer latches or savepoint latches.
Latch Buffer	Time spent waiting to latch a buffer that is not an I/O request.
Latch Savepoint	Time spent waiting to synchronize commits to marked transactions.
Lock Bulk Update	Time spent waiting to acquire bulk update locks.
Lock Exclusive	Time spent waiting to acquire exclusive locks.
Lock Intent	Time spent to acquire intent locks.
Lock Schema	Time spent waiting to acquire schema locks
Lock Shared	Time spent waiting to acquire shared locks.
Lock Update	Time spent waiting to acquire update locks.
Log Buffer	Time spent waiting for space in the log buffer or otherwise waiting for memory to be made available to write log records.
Log Other	Time spent waiting for miscellaneous log.
Log Synchronization	Time spent waiting to see whether log truncation frees log space.
Log Write	Time spent waiting for outstanding I/O to finish, or waiting for log flushes to complete.
Network HTTP	Time spent waiting for outstanding HTTP connections to complete and exit.
Network I/O	Time spent waiting for network packets.
Network IPC	Time spent waiting for sub-tasks to generate data; long waits are indicative of unexpected blockages.
Network Mirror	Time spent waiting for sub-tasks to generate data; long waits are indicative of unexpected blockages.
OLEDB Provider Full Text	Time spent waiting for the Microsoft Full Text Engine for SQL Server.
Other Miscellaneous	Time spent waiting for miscellaneous database operations.
Parallel Coordination	Time spent waiting for parallel coordination tasks to complete.
Service Broker	Time spent waiting for Service Broker event handlers and endpoints.
Synchronous Task	Time spent waiting for tasks started synchronously. Most SQL Server processes are started Asynchronously.

Reviewing Session details

Displays the same data as described in the Reviewing Session details with SQL PI.

The Sessions details can be sorted by selecting a resource from the drop down resource list above the left panel, by selecting the resource the workload chart will be updated accordingly.


	NOTE: The Session Blocks and Session Locks tabs are not presented when drilling to the session's details from the Last 1 Hour view.

Monitoring Locks and Latches

The Locks panel displays information about all locks and latches in the currently monitored SQL Server instance.The information is provided by the panes described in the following topics:

•

•

•

The Locks table displays information about all locks currently held or requested.

To create a custom filter for this table, use the options accessible by clicking the Customizer button at the table’s upper right side. For details, see Components Shared by all Foglight for SQL Server Screens.

To configure the default retrieval settings for this panel, use the Lock view in the Databases Administration dashboard. For details, see Setting Options for Displaying Data in the Locks Panel.

Table 28. The Locks table displays the following columns:
Column	Description
SPID	The server process ID of the current user process.
Database	The database being used by the current process.
Count	The number of locks of the type specified in the Lock Type column against the database specified in the Database column, from the SPID specified in the SPID column.
Index Name	The index being used by the current process (if any).
Object Name	The currently locked object.
Lock Type	The type of the resource that is currently locked (Database, Table, Page, Row, Extent, and so on).
Mode	The kind of lock being applied to the resource (Shared, Exclusive, Update, IntentShared, IntenExclusive, and so on).
Status	The status of the lock: Blocked, Blocking, or blank (Granted).
Login Name	SQL Server login name for this session.

Lock Types chart

The Lock Types chart shows lock statistics broken down by the various types of locks available in SQL Server.

This chart displays one line for each of the following lock types:

•

AllocUnit — a lock on an allocation unit

•

Application — a lock on an application-specified resource

•

Database — a lock on a database, including all of the database’s objects

•

Extent — a lock on a contiguous group of eight pages

•

File — a lock on a database file

•

HoBT — a lock on a heap of data pages, or on the BTree structure of an index

•

Key — a lock on a row in an index

•

Metadata — a lock on a piece of catalog information, also called metadata

•

Object — lock on table, stored procedure, view, and so on, including all data and indexes. The object can be anything that has an entry in sys.all_objects.

•

Page — a lock on an 8-kilobyte (KB) page in a database

•

RID (Rows) — Row ID; a lock on a single row in a heap

Use the list on the chart title to select which of the following lock types to display:

•

Waits — the rate of lock request wait events. Such wait events take place when lock requests cannot be satisfied immediately and require the caller to wait before being granted the lock.

•

Average Wait Time — the average time (in seconds) that elapses before a lock request wait is cleared.

•

Lock Requests — the number of lock requests and lock conversion requests per second.

•

Timeouts — the number of lock time-outs per second.

By default, SQL Server never times out locks. However, many applications issue a SET LOCK_TIMEOUT statement to cause SQL Server to time out their locks after the specified interval. This metric shows how often these time-outs are being exceeded.

The Lock Timeout (@@LOCK_TIMEOUT) values for each connection are displayed in the Session Details pane, in the Sessions panel. For details, see Viewing Session Details.

•

Deadlocks — the number of lock requests per second that resulted in a deadlock.

A deadlock occurs when multiple SQL Server sessions request conflicting locks in such a way that two locks are blocked by each other. For further details, see Deadlocks Alarm.

Latches chart

The Latches chart displays statistics on latch requests.

This chart shows the following series of data:

•

Latch Waits — how many wait events for latches occurred in the specified time range

•

Total Wait Time — the total amount of time (in milliseconds) that latch requests spent waiting during the specified time range

Latches are file system locks, used for synchronizing data within SQL Server. Latches are enforced when a data element is being accessed physically, in order to ensure that the data page on which the data element resides is readable and writable.

Enforcement of latches is also carried out before the modified data page is written to disk, to prevent modifications by other users during the physical write operation. After the page is successfully written to disk, the latch is released.

A high rate of latch wait events per second may indicate a slow disk I/O subsystem.

Tracking Current Lock Conflicts

The Blocking (Current) panel provides details for all current lock conflicts.

This panel allows carrying out the tasks detailed in the following topics:

•

Handling Blocking Sessions, using the Blocking table. For details, see Handling blocking sessions.

•

Monitoring the processes blocked during the reporting period, using the Number of Blocked Processes chart. For details, see Monitoring blocked processes for the sampled interval.

Handling blocking sessions

The Blocking table displays all connections that are either currently waiting on locks held by others, or are causing others to wait, highlighting who is waiting on whom, and the resources involved.

The hierarchy in this tree diagram represents the blocking chains. It shows who is blocking whom, by displaying one entry for each session that is blocked, and one for each session that is blocking another but is not blocked itself. Sessions at the top of the tree (those that do not have a parent in the tree) are at the head of the blocking chain, and are therefore the root cause of all blocking. Such sessions appear as Lead Blockers in the Number of Blocked Processes chart.

Table 29. The Blocking table displays the following parameters:
Parameter	Description
SPID	The unique number the Server has assigned for identifying the selected session.
Wait Time	How long this session has been waiting for the lock (measured in seconds). If the value displayed is 0, the session is not waiting.
Type	The type of the lock request that is waiting (Database, Table, Page, and so on).
Resource	The resource that is in conflict. This value often identifies a database and table. The data in the Resource column is reported directly from SQL Server.
Command	The current or previous command executed. This information can be useful when deciding which sessions to kill.
SQL User	Identifies the user associated with the SPID.
Program	The application program that the user is using (for example, Microsoft Access).
Win User	Name of the Windows account with which the user is logged in to SQL Server.
CPU	The total amount of CPU consumed by the session so far.
I/O	The total amount of I/O resources consumed by the session so far. This information can be useful when deciding which sessions to kill.
Host Name	The name of the client computer.
Status	The status of the session (Blocked, Blocking, or both). For sessions at the head of the blocking chain (those that are not blocked), this will indicates if the session is Runnable or Sleeping.
DB Name	The name of the database where the session is active.
Session SQL	Displays the SQL belonging to the session that is blocked and/or blocking.

Monitoring blocked processes for the sampled interval

The Number of Blocked Processes chart displays the number of SQL Server sessions that were involved in blocks over time. Use this chart to review the frequency and duration of lock conflicts in SQL Server.

This chart displays the following indicators:

•

Blocked Processes — number of sessions that were waiting on locks held by others.

•

Lead Blockers — number of sessions that were not blocked, but were blocking others. Lead Blockers correspond to sessions in the Blocking table that do not have a parent in the Blocking chain (at level 1 in the tree).

Tracking Deadlocks and their Affected Objects

The Deadlocks panel displays all deadlocks that took place within the selected time range.

Deadlock situations are highly time and resource-consuming, and result in unresponsive applications and operation rollback, because they involve at least two transactions that lock one another and can be resolved only by terminating one of the transactions, making it a “deadlock victim”. Resolving a deadlock can become even more complicated when the deadlock involves more than two sessions (chained deadlocks).

Using the Foglight for SQL Server agent for collecting Deadlock Graph trace data does not require turning on any SQL Server trace flags. Foglight for SQL Server collects this data implicitly for any SQL Server user who has been granted the ALTER TRACE permission through the automatic discovery wizard; the user does not need to intervene in the process.

To retrieve Deadlock Graph data, Foglight for SQL Server uses minimal trace over deadlocks, collecting only the deadlock-related data, unlike SQL Server Profiler. The only difference is in the refresh rate; while SQL Server Profiler constantly refreshes its data, Foglight for SQL Server agent collects the data every 1 minute (by default) when focusing the screen (online mode), and every 5 minutes when off screen (offline mode). As a result, when watching the deadlock dashboard the data refresh occurs every 1 minute (by default).

The Deadlocks panel contains an overview pane, which provides significant performance-related details about each of the deadlocks, such as the amount of time the “deadlock victim” ran before being terminated, the amount of time each of the transactions involved in the deadlock had to wait, and the ratio of regular to chained deadlocks during the specified time range. The panel’s other panes focus on specific types of components (databases/objects/applications), allowing to view which of the application’s components were most adversely affected by deadlocks, and are therefore the most vulnerable to deadlock situations.

This panel includes the panes described in the following sections:

•

•

•

•

The Deadlocks pane contains the following components, which provide information over all of the deadlocks that took place during the specified time range:

•

Summary — provides summarized data regarding the following aspects:

•

Overall lost time — the total amount of time that the terminated session (“deadlock victim”) was running before its termination.

•

Number of deadlocks — the total number of deadlock situations that took place during the specified time range.

•

Chained and Regular Deadlocks — contains the following components:

•

Chart — displaying the distribution of locks, with different color codes for chained and regular deadlocks.

•

Graph — displaying the number of regular and chained deadlocks

•

Table — displaying detailed data about each of the deadlocks, as listed below.

Table 30. Deadlock details include:

Column

Description

Time

The exact time when the deadlock took place.

Type

The deadlock type:

•

Chained — indicated by a lock sign

•

Regular — the column is empty

SPID

The SQL Server’s internal process ID

SQL Text

The short text of the selected SQL statement.

Database

Database name

Host Name

The name of the client computer that established the SQL Server connection.

The SQL Server login name for this session.

Client Application

The client application that established the SQL Server connection.

Lost Time

The time, in seconds, since the last transaction ran until the deadlock occurred.

Last trans. started

The time when the last transaction started.

Wait Time

The amount of time the process is waiting for the resource.

Log Used

The amount of log used through the session process.

Lock Mode

The lock mode set for the Owner.

Trans. Count

The number of opened transactions.

Owned Object

The object that is owned by the selected session and waited by the other session involved in the deadlock.

Waited Object

The object that is owned by the other session involved in the deadlock, and for which the selected session waits.

Wait Resource

The resource for which the process is waiting.

Deadlock Priority

The deadlock priority specified for the SQL Server session.

Databases

The Databases pane displays all of the databases that were involved in deadlock situations during the specified time range. This pane contains the following sections:

•

Involved Databases table — contains the following columns

•

Name — the name of the involved database.

•

Overall lost time — the total amount of time that the terminated sessions (“deadlock victims”) were running before they were terminated and rolled back.

•

Deadlocks — the total number of deadlock situations in which the database was involved during the specified time range.

•

Chained — indicates how many of the deadlock situations were chained deadlocks, that is, deadlocks that involve more than two sessions.

•

Regular — indicates how many of the deadlock situations involved only two sessions.

•

Chart — displaying the distribution over the specified time range of deadlocks in which the selected database was involved.

•

Table — displaying detailed data about each of the deadlocks in which the selected database was involved. This table is identical to the one displayed on the Deadlocks pane. For details, see Deadlock table on Table — displaying detailed data about each of the deadlocks, as listed below..

Objects

The Objects pane displays all of the objects that were involved in deadlock situations during the specified time range. This pane contains the following sections:

•

Involved Objects table

•

The components of this pane (tables and a chart) are identical to the ones found in the Databases pane.

Applications

The Applications pane displays all of the applications that were involved in deadlock situations during the specified time range. This pane contains the following sections:

•

Involved Applications table

•

The components of this pane (tables and a chart) are identical to the ones in the Databases pane.

Viewing I/O Statistics by Database Files

The I/O by File panel displays current I/O statistics for each SQL Server file. The I/O statistics’ display can also be grouped by other grouping criteria, such as database files or disk.

This panel includes the following components:

•

A table that displays current SQL Server I/O statistics for each SQL Server database file. For details, see Viewing the I/O by File table.

•

A chart that provides a graphic representation of some of the I/O statistics for each row selected in the table. For details, see Viewing the I/O by File chart.

Viewing the I/O by File table

The I/O by File table displays current SQL Server I/O statistics for each SQL Server database file.

Table 31. The I/O by File table displays by default the following columns:
Column	Description
DBName	The name of the SQL Server database that contains the file.
File Name	The name of the file whose I/O statistics are on display.
Disk	The name of the physical disk on which the database resides.
MB on Disk	Physical file size on disk in megabytes.
MB Read	The number of megabytes read from this file since SQL Server started.
MB Written	The number of megabytes written to this file since SQL Server started.
MB Total	The total number of megabytes written to and read from this file since SQL Server started.
Reads Wait Time	The total number of seconds SQL Server has spent waiting for physical read operations on the file since SQL Server started. Displays data only for SQL Server 2005 and later versions.
Writes Wait Time	The total number of seconds SQL Server has spent waiting for write operations on the file since SQL Server started. Displays data only for SQL Server 2005 and later versions.
Total Wait Time	The total number of seconds SQL Server has spent waiting for physical read and write operations on the file since SQL Server started. Displays data only for SQL Server 2005 and later versions.
Read Operations	The total number of read operations carried out by this file during the selected time period.
Write Operations	The total number of write operations carried out by this file during the selected time period.
Total Operations	The combined total number of read and write operations carried out by this file during the selected time period.

Viewing the I/O by File chart

The I/O by File chart provides a graphical representation of SQL Server I/O statistics for each SQL Server database file.

This chart represents several indicators from the table (see Viewing the I/O by File table), as presented in the following table.

To display only requested files, click the first requested file and then hold down either the Shift key, for choosing a block of multiple files, or the Ctrl key, for choosing individual files.

Table 32. The I/O by File chart displays the following:
Indicator	Description
Current IO Rate	The current rate, in megabytes, at which SQL Server is performing physical read and write operations to this file (Reads rate plus Writes rate).
Current Read Rate	The current rate, in megabytes, at which SQL Server is performing physical read operations from this file.
Current Write Rate	The current rate, in megabytes, at which SQL Server is performing physical write operations to this file.
Current Wait Time (Average Active Sessions)	The number of seconds that SQL Server has spent waiting for I/O operations on this file since the last time data was collected.

Seleziona il prodotto:

Per una maggiore efficacia, compilare l'Obiettivo della chat:

Soluzioni consigliate per il problema

Foglight for SQL Server (Cartridge) 5.7.5.30 - User Guide

Monitoring Buffer Cache-related Data

Reviewing the Instance Activity

Viewing In-depth Data about the Instance

Tracking the Instance Data Flow