Foglight for SQL Server (Cartridge) 7.3.0 - User Guide

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:

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.

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 .

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.

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:

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:

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:

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:

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

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:

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:

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

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 .

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.

Reviewing Database Usage

The Databases drilldown displays storage information about the selected SQL Server instance, including: databases, file groups, files, tables, indexes, disks, and log files.

The Databases drilldown is divided into the following areas: