Chat now with support
Chat with Support

The Quest and One Identity Support Portals will be unavailable on Friday July 10, 2020 from 5:30 PM to 6:30 PM for website maintenance.

Foglight for SQL Server (Cartridge) 5.9.5.10 - 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 Performance Counters and Collections
Monitoring Business Intelligence Services Administering Foglight for SQL Server
Configuration Settings 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 pool 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
SQL Server Agent's Default Collections Access Methods Agent Alert List Agent Job List Always On Availability Groups Backup Locations Blocking History Blocking List Buffer Cache List Buffer Manager CLR Assemblies Cluster Summary Configuration Database Index Density Vectors Database Index Details Database Index Fragmentation Info Database Index Histogram Database Index List Database Information Database Properties Database Sessions (Session List) Database Summary Database Tables List Databases Deadlock DTC Information Error Log Error Log List Error Log Scan File Groups File Data Flow Statistics File Groups Files Files Drive Total Files Instance Summary Full Text Catalog InMemory OLTP (XTP) Instance Wait Categories Instance Wait Events Job Messages Latches and Locks Lock Statistics Locks List Log Shipping Log Shipping Error Logical Disks Memory Manager Mirroring Mirroring Performance Counters Missing Indexes Plan Cache Distribution Plan Cache List Replication Agents Replication Agent Session Actions Replication Agent Session Merge Articles Replication Agent Sessions Replication Agent Sessions by Type Replication Available Replication Publications Replication Subscriptions Reporting Services Resource Pool Session Data Session Trace SQL PI Instance Statistics SQL Server Connections Summary SQL Server Global Variables SQL Server Host SQL Server Load SQL Server Services SQL Server Throughput SQL Server Version Info SSIS OS Statistics SSIS Summary Statistics Top SQLs Top SQL Batch Text Top SQL Long Text Top SQL Plan Top SQL Short Text Top SQL Summary Traced SQL PA Usability User-defined Performance Counters User-defined Queries Virtualization XTP Session Transactions Statistics

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.

Table 31. The Blocking table displays the following parameters:

The unique number the Server has assigned for identifying the selected session.

How long this session has been waiting for the lock (measured in seconds). If the value displayed is 0, the session is not waiting.

The type of the lock request that is waiting (Database, Table, Page, and so on).

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.

The current or previous command executed. This information can be useful when deciding which sessions to kill.

Identifies the user associated with the SPID.

The application program that the user is using (for example, Microsoft Access).

Name of the Windows account with which the user is logged in to SQL Server.

The total amount of CPU consumed by the session so far.

The total amount of I/O resources consumed by the session so far. This information can be useful when deciding which sessions to kill.

The name of the client computer.

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.

The name of the database where the session is active.

Displays the SQL belonging to the session that is blocked and/or blocking.

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:

Description

The exact time when the deadlock took place.

The deadlock type:

The SQL Server’s internal process ID

The short text of the selected SQL statement.

Database name

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

The SQL Server login name for this session.

The client application that established the SQL Server connection.

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

The time when the last transaction started.

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

The amount of log used through the session process.

The lock mode set for the Owner.

The number of opened transactions.

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

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

The resource for which the process is waiting.

The deadlock priority specified for the SQL Server session.

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 .

Table 33. The I/O by File table displays by default the following columns:

The name of the SQL Server database that contains the file.

The name of the file whose I/O statistics are on display.

The name of the physical disk on which the database resides.

Physical file size on disk in megabytes.

The number of megabytes read from this file since SQL Server started.

The number of megabytes written to this file since SQL Server started.

The total number of megabytes written to and read from this file since SQL Server started.

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.

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.

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.

The total number of read operations carried out by this file during the selected time period.

The total number of write operations carried out by this file during the selected time period.

The combined total number of read and write operations carried out by this file during the selected time period.

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.

The current rate, in megabytes, at which SQL Server is performing physical read and write operations to this file (Reads rate plus Writes rate).

The current rate, in megabytes, at which SQL Server is performing physical read operations from this file.

The current rate, in megabytes, at which SQL Server is performing physical write operations to this file.

The number of seconds that SQL Server has spent waiting for I/O operations on this file since the last time data was collected.

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:

Related Documents