Foglight for SQL Server (Cartridge) 5.9.2.1

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 Space	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.
Log Space	The log files in all databases of the SQL Server instance.The total percentage is also displayed in text.
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 16. 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 17. 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 On	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