Foglight for SQL Server (Cartridge) 7.1.0

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.

Table 31. 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 32. 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 page 95.

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 33. 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 34. 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.