Chat now with support
Chat with Support

Foglight for Azure SQL Database (Cartridge) 7.2.1 - User Guide

Installing and Configuring Agents Using Foglight for Azure SQL Database Administering Foglight for Azure SQL Database Reporting Reference
Overview of Collections SQL Performance Investigator Metrics
Active Connections Active Time Active Time per Execution Active Time Rate Always On Average CPU Percent Average DTU Percent Average Memory Usage Percent Average SQL Response Time Average XTP Storage Percent Average Data IO Percent Average Log Write Percent Backup Recovery Blocked Sessions Connections CPU Limit CLR Wait CLR Wait per Execution CLR Wait Rate CPU Time CPU Time per Execution CPU Time Rate CPU Wait CPU Wait per Execution CPU Wait Rate Cursor Synchronization Database Replication Deferred Task Worker Distributed Transaction DTU Limit Elapsed Time Executions External Procedure Full Text Search Governor Wait Governor Wait per Execution Governor Wait Rate Granted Memory Hosted Component Inactive User Connections I/O Bulk Load I/O Completion I/O Data Page I/O Wait I/O Wait per Execution I/O Wait Rate Idle Time Internal Cache Latch Latch Buffer Latch Savepoint Latch Wait Latch Wait per Execution Latch Wait Rate Lead Blockers Lock Bulk Update Lock Exclusive Lock Intent Lock Schema Lock Shared Lock Update Lock Wait Lock Wait Rate Lock Wait per Execution Log Buffer Log Other Log Synchronization Log Wait Log Wait per Execution Log Wait Rate Log Write Logical Reads Logical Reads per Execution Max Degree of parallelism Max DTU Percent Max Session Percent Max Workers Percent Memory Wait Memory Wait per Execution Memory Wait Rate Network HTTP Network I/O Network IPC Network Mirror Network Wait Network Wait per Execution Network Wait Rate OLEDB Provider Full Text Other Miscellaneous Other Wait Other Wait per Execution Other Wait Rate Parallelism Wait Percent of Total Plan Recompilations Physical Reads Physical reads per Execution Preemptive Wait Preemptive wait per Execution Preemptive Wait Rate Remote Provider Wait Remote Provider wait per Execution Remote Provider Wait Rate Row count Service Broker Synchronous Task Wait Time Percent Writes Writes per Execution XTP Log write Wait XTP Miscellaneous Wait XTP Procedure Wait XTP Transaction Wait XTP Wait XTP Wait per Execution XTP Wait Rate
Glossary

SQL Performance Investigator (SQL PI)

The SQL PI view provides the ability to perform a more in-depth analysis and investigation of the database activity and resource consumption by adding a dimension view of the activity, locked objects, and blocking history.

The performance tree provides iterative (up to three levels) access to any of the key dimensions associated with Azure SQL Database activity, based on the OLAP multidimensional model and an database view of the database activity. Domain nodes offer a hierarchical view of all types of Azure SQL Database activity characteristics.

Selecting a dimension from the tree determines what subset of activity is displayed. Iterative drill-down into domains of interest provides increasingly refined focus and diagnosis.

5
Select Client Machines under the selected SQL Statement, to view the computers on which the statement was run.

In a similar manner, such iterative drilldowns can be carried out into any Azure SQL Database dimension of interest, to gain a complete understanding of the causes of its behavior.

The default Azure SQL Database dimensions are as follows:

The History section view is divided into two sections that are correlated to each other:

Selecting each dimension in the performance tree together with a specific resource effects the data displayed for each Level.

The Lock Analysis displays all locks that took place within the selected time range.

The lock analysis feature is integrated as part of the performance tree and it displays all the lock trees including further details for both the blocker and the blocked session including:

This view presents the execution plan of a selected SQL and the cost of it. A Historical execution plan can be generated from History by selecting the statement or batch and by clicking Open Plan in SSMS in the top of the table.

Azure SQL Activity Drilldown

Use the Azure SQL Activity drilldown to carry out the operations described in the following topics:

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 Azure SQL Database 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 3. The Blocking table displays the following parameters:

Unique number the Server has assigned for identifying the selected session.

Session ID that is currently blocked.

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.

Resource for which the session is waiting.

Object that is owned by the selected session and waited by the other session involved in the lock.

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

Number of result sets that are open for the session.

Number of transactions that are open for the session.

Total amount of CPU consumed by the session so far.

Total amount of memory consumed by the session so far.

Number of logical reads that have been performed by the session.

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

Locked resource type of the session.

Mode of the locked session.

Current status of the locked session.

Text of the locked SQL query.

The Number of Blocked Processes chart displays the number of Azure SQL Database sessions that were involved in blocks over time. Use this chart to review the frequency and duration of lock conflicts in Azure SQL Database.

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).

The Sessions screen allows you to trace the activity of all currently connected sessions, as well as their resource consumption.

 

This panel can also be used to carry out the tasks described in the following sections:

The diagram displays the distribution of the currently connected sessions between active and inactive sessions.

The diagram displays the distribution of the currently connected sessions between foreground and background sessions.

The Sessions view in the table can be filtered by the Active only and Foreground Only check boxes:

The Sessions list provides detailed information about the sessions as follows:

Administering Foglight for Azure SQL Database

Administering Foglight for Azure SQL Database

You use the Databases Administration dashboard to set options for collecting, storing, and displaying data about monitored Azure SQL databases.

Opening the Databases Administration Dashboard

You can edit settings for one or more Azure SQL databases on the Databases > Administration dashboard.

1
In the navigation panel, under Homes, click Databases.
3
Click Settings and then click Administration.
Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating