Chat now with support
Chat with Support

Foglight for SQL Server 5.7.5.41 - User Guide

Introduction to this Guide Using Foglight for SQL Server
Viewing the Databases Dashboard Overview Dashboard Advisories Dashboard Monitoring Data Replication Monitoring SQL Performance Reviewing Memory Usage Reviewing the Instance Activity Reviewing Database Usage Reviewing the Services Using the HADR Drilldown Using the Logs Drilldown Reviewing Configuration Settings Viewing User-defined Counters and Collections
Monitoring Business Intelligence Services Administering Foglight for SQL Server
Configuration Settings Viewing User-defined Counters and Collections Managing Foglight for SQL Server Agent Settings Reviewing Foglight for SQL Server Alarms Generating Reports Monitoring SQL Server instances on VMware servers
Glossary
A
Access methods Adhoc SQL Plans Alarm Alert Allow updates Anonymous subscription Authentication AutoClose AutoGrow Automatic Discovery AutoShrink B Batch BCP (Bulk Copy Program) Blocking Books Online Bound trees Buffer Buffer cache Buffer pull Bulk copy Bulkinsert Bulk load C Cache CAL Calibration Cardinality Cartridge Chart Checkpoint process Client network utility CLR Compile Connect Connection Connectivity software CPU Usage Cursors D Data access components Data file Data storage engine Database Database object DataFlow DBCC DBID DBO Deadlock Disk queue length Disk transfer time Disk utilization DiskPerf Distributing instance Distributor DMO Drilldown DTC DTS E Error log Event alert Execution contexts Extended stored procedures Extent External procedures F File Filegroup File cache Foglight Agent Manager Foglight Management Server Foreign key Forwarded records Free buffers Free list Free pages Free potential Free space Freespace scans Full text search G GAM Ghosted records Growth increment GUID H Hash buckets Hashing Heap Hit rate Hit ratio Host name Host process I I/O Index Indid Integrated security Intent Locks J Job K Kernel memory Kernel mode Kill L Latch Lazy writer Licensing Lightweight pooling Lock Lock area Lock escalation Lock mode Log Log cache Log writer Logical I/O LRU LSN M Master MaxSize MDAC Metric Misc.normalized trees Model Monitor page file N Named pipes Net library NIC Null O OBID Object plans OLAP OLAP service OLTP Optimizer Optimizer cache osql P Page life expectancy Paging Panel Parse Parser Per seat licensing Per server licensing Performance alert Physical I/O Physical read Physical write PID Pinned Plan Plan cache Potential growth Prepared SQL plans Primary key Privileged mode Procedure cache Procedure plans Process Profiler Publication database Publisher Publisher databases Publishing server Pull subscription Pulse Push subscription Q Query plan R RAID Random I/O Read ahead Recompile Referential integrity Relational data engine Replication procedure plans Role Rollback S sa Schema locks Sequential I/O Session Severity SGAM Shared locks Show advanced options SMP Sort, Hash, Index Area SPID Spike Spinner SQL Agent Mail SQL Mail SQL Plans SQL Server Agent SQL Server authentication SQL Server books online Standard deviation Stolen pages Stored procedure Support service SYSADMIN role T TDS TempDB Temporary tables and table variables Threshold Torn page detection Transaction Trigger Trigger plans Truncate Trusted U UMS Unused space User connection area User mode V Virtual log file VLF W Waitfor Windows authentication mode Working set
Reference
SQL PI Repository Cold Backup Procedure SQL Performance Investigator Metrics
Active Time All SQL Agents CPU Usage All SQL Agents Resident Memory Usage Availability Average Physical I/O Operations Average SQL Response Time Backup Recovery Wait Blocked Lock Requests Checkpoint Pages CLR Wait CPU Usage CPU Wait Cursor Synchronization Wait Database Replication Wait Deferred Task Worker Wait Degree of Parallelism Disk Utilization DTC CPU Usage DTC Resident Memory Usage Distributed Transaction Wait Executions Ended Executions Started External Procedures Wait Full Scans Full Text Search CPU Usage Full Text Search Resident Memory Usage Full Text Search Wait Free Buffer Wait Hosted Components Wait IO Bulk Load Wait IO Completion Wait IO Data Page Wait IO Wait Latch Buffer Wait Latch Wait Latch Savepoint Wait Lazy Writes Lock Wait Lock Bulk Update Wait Lock Exclusive Wait Lock Intent Wait Lock Requests Lock Schema Wait Lock Shared Wait Lock Update Wait Lock Wait Log Buffer Wait Log Flushes Log Other Wait Log Synchronization Wait Log Wait Log Write Wait Memory Wait Network IO Wait Network IPC Wait Network Mirror Wait Network Wait Non SQL Server CPU Usage Non SQL Resident Memory Usage OLAP CPU Usage OLAP Resident Memory Usage OLEDB Provider Full Text Wait Other CPU Usage Other Miscellaneous Wait Other Wait Overall CPU Page Life Expectancy Page Splits Parallel Coordination Wait Physical I/O Physical Memory Used Physical Page Reads Physical Page Writes Probe Scans Plan Cache Hit Rate Range Scans Rec Ended Duration Remote Provider Wait Run Queue Length Samples Service Broker Wait Session Logons Session Logoffs SQL Agent CPU Usage SQL Agent Resident Memory Usage SQL Executions SQL Mail CPU Usage SQL Mail Resident Memory Usage SQL Recompilations SQL Response Time SQL Server Background CPU Usage SQL Server Cache Memory SQL Server Connections Memory SQL Server Connections Summary SQL Server Foreground CPU Usage SQL Server Resident Memory Usage SQL Server Swap Memory Usage Synchronous Task Wait Table Lock Escalation Target Instance Memory Total CPU Usage Total Instance Memory Virtual Memory Used
Rules Collections and Metrics

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:

•
Locks table
•
Lock Types chart
•
Latches chart
Locks table

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.

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 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:

•
Deadlocks
•
Databases
•
Objects
•
Applications
Deadlocks

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.

Login Name

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.
•
Deadlocks related to a database — clicking a specific database in the Involved Databases table displays in this section data about the deadlocks in which the selected database was involved, using the following components:
•
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 94.
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
•
Deadlocks related to an object — clicking a specific object in the Involved Objects table displays in this section data about the deadlocks in which the selected object was involved.

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
•
Deadlocks related to an application — clicking a specific application in the Involved Applications table displays in this section data about the deadlocks in which the selected application was involved.

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.

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 .

 
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.

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating