Using Foglight for Azure SQL Database

Foglight for Azure SQL Database monitors the Azure SQL Database activity by connecting to and querying the Azure SQL Database. The agents provided monitor the Azure SQL Database system. The dashboards included with the cartridge provide a visual representation of the status of the major components of the Azure SQL agents. They allow you to determine any potential bottleneck in database performance. This section provides agent configuration instructions and information on investigating Azure SQL Database performance. It covers the entire monitoring process, starting with the discovery of the databases and the connection with these databases, and proceeding with the use of the various drilldowns and the Global Administration options.

This section covers the following key areas:

Viewing the Databases Dashboard

This section describes the various components of the Databases dashboard. For more information, refer to Viewing Databases Dashboard.

Foglight for Azure SQL Database Overview Dashboard

The Foglight for Azure SQL Database Overview dashboard provides various detailed views that are accessible using the toolbar. The Overview dashboard contents depends on whether an Azure SQL Database is selected.

Breakdown and Baseline Chart Formats

Several charts can display information in either of the following formats:

  • Baseline format — where a selected individual metric is displayed as a single line.
  • Breakdown format — a representation of actual activity of a metric or a set of metrics, compared with the typical behavior of these metrics for the selected time range. A breakdown display can also present a single metric divided into various components. For example, space utilization is broken down by the various components that occupy the space.

Home Page Toolbar

The Foglight for Azure SQL Database Overview dashboard provides the following toolbar buttons.

Name Description
Overview Opens the Overview view.
SQL PI Opens the SQL Performance Investigator (SQL PI) drilldown, which displays performance-related data at the following levels:
- High-level data — available for all instances.
- In-depth data, delivered by means of the Cluster view or Instance view tree — available only for instances or RACs that have Performance Analysis installed and configured.
Activity Opens the Viewing the Blocking (Current) panel, which contains the following panels:
- Blocking (Current)— see For more information, see Viewing the Blocking (Current) panel.
- Sessions — see Viewing Detailed Sessions Data

Overview view

The upper section of the view includes general information on the monitored database:

  • Azure SQL Database
  • Collation
  • Elastic pool
  • Edition Each tile is constructed of a title that state the name of the monitored issue and an aggregation of the alarms relevant to that issue. The tiles are organized by priority:
  • Availability — Shows information and alarms about the availability of the database. Also includes the monitoring state for that database.
  • HA/DR — High Availability (HA) and Disaster Recovery (DR) state of the database. Displays the state of the resources used for availability. For example, Always On, Cluster, Replication, Mirroring, Log Shipping.
  • Storage — Shows the space utilization level for all Data and Query store.
  • Compute Utilization— Provides general information collected from the server:
    • DTU— The DTU tile exists only when the database purchase model is DTU. A database transaction unit (DTU) purchasing model represents a blended measure of CPU, memory, reads, and writes.
    • CPU— Azure SQL CPU utilization compared to the general host CPU utilization.
    • Data I/O— Utilization of data input or output on the monitored server.
    • Log I/O— Utilization of log input or output on the monitored server.
  • Memory— Amount of memory Azure SQL uses compared to other processes and the total memory on the server.
  • Database Performance — Summarizes general information collected from the SQL PI dashboard. The pie chart displays the average workload divided by resources over time. The graphs display the total workload trend over time. The Database Performance tile also displays the following issues:
  • Tuning recommendations
  • Current sessions state (for example, Active, Inactive, and Blocked) The right panel is used to display either alarms or Top 10 SQLs:
  • Alarms

    Only the three resources would directly be displayed, according to the importance. The state of backup’s alarms is also displayed.

    • Display all active alarms for the underlined database.
    • Enable sorting by creation time or severity.
  • Top 10 SQLs — Displays the 10 SQLs with the longest duration.

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.

Performance Tree

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.

For example, to begin the investigation by first identifying the most active User, follow the steps described below:

  1. Select the Users node, to display the most active database users in the selected time range. That is, the database users who consumed the highest amount of the selected resource.
  2. Select the first user, to focus the entire window on that user’s activity.
  3. Identify the most demanding SQL statement that this specific user has executed, by expanding the user node and then selecting the SQL statement dimension node. This displays the most active SQL statements executed by this user.
  4. Select a specific SQL statement to focus the entire window on the selected statement’s activity.
  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:

  • SQL Statements — The executed SQL queries.
  • TSQL Batches— A batch of SQL statements. That is a group of two or more SQL statements or a single SQL statement that has the same effect as a group of two or more SQL statements.
  • Programs — names of the programs that connected to Azure SQL Database and executed the SQL statements.
  • Users — Azure SQL Database login names used for logging in to Azure SQL Database.
  • Client Machines — The machines on which the client executable (connected to Azure SQL Database) is running.
  • Context Info — Displays the information set by the DBMS_APPLICATION_INFO.SET_CLIENT_INFO procedure.
  • Command Types — Executed SQL command type (for example, INSERT and SELECT).
  • Locked Objects — Displays the objects that experienced locks, the duration of the lock and the type of the lock. The object view can be sorted by selecting a dimension in the performance tree, for example: by selecting a database name only the locked object that occurred on that database will be displayed.

Viewing Historical Metrics

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

  • Resource consumption charts — This section displays data in five different charts:
    • Workload chart — Displays the database resource activity over the selected time frame by emphasizing the resources by colors.
    • Baseline chart — Displays the database workload compared to the baseline over time.
    • Breakdown chart — Activity of the database by second.
    • Resource Breakdown Pie chart — Displays the resource breakdown usage by % of the total database activity.
    • All wait events pop up — Displays details of the wait events that the database is waiting on during the selected time range.
  • Overview section- Displays a graphical representation of the metrics highlighted in the Workload related Metrics table below.
    • Workload related Metrics - A table that displays a variety of resource consumption metrics which can give an in-depth of the database activity, each resource holds its default metrics. Selecting each dimension in the performance tree together with a specific resource effects the data displayed for each Level.
  • For example, by selecting the Lock resource the Database view dimension will present only locks related data, the SQL Statements dimension will present only the statements that were experiencing locks and DB users the were experiencing locks and so on through all the dimensions and resources.

Blocking History

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:

  • Lock event start date
  • SPID
  • Blocked By
  • Resource
  • Lock Resource Type
  • Status
  • Duration
  • Program
  • User
  • SQL Text
  • Machine
  • Command Type

Viewing Execution Plans

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:

  • Viewing information about current blocking processes — using the Blocking (Current) panel. For details, see Viewing the Blocking (Current) panel.
  • Viewing information about sessions that experienced the highest contention of a specified contention criterion — using the Sessions panel. For details, see Viewing Detailed Sessions Data.

Viewing the Blocking (Current) panel

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

The Blocking table displays the following parameters:

Parameter Description
SPID Unique number the Server has assigned for identifying the selected session.
Blocked Session ID Session ID that is currently blocked.
Status 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.
Wait Resource Resource for which the session is waiting.
Object Resource Object that is owned by the selected session and waited by the other session involved in the lock.
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.
Open Result sets Number of result sets that are open for the session.
Open Transaction Number of transactions that are open for the session.
CPU Total amount of CPU consumed by the session so far.
Memory Usage Total amount of memory consumed by the session so far.
Logical Reads Number of logical reads that have been performed by the session.
Physical I/O Total amount of I/O resources consumed by the session so far. This information can be useful when deciding which sessions to kill.
Lock Level Locked resource type of the session.
Lock Mode Mode of the locked session.
Lock Status Current status of the locked session.
SQL Text Text of the locked SQL query

Monitoring blocked processes for the sampled interval

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

Viewing Detailed Sessions Data

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

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.

This panel can also be used to carry out the tasks described in the following sections: Active/Inactive Sessions The diagram displays the distribution of the currently connected sessions between active and inactive sessions.

Foreground/Background Sessions The diagram displays the distribution of the currently connected sessions between foreground and background sessions.

Sessions List 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 user sessions, by clearing the check box, the background sessions will be presented as well.

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

  • SPID — Session identifier.
  • Status — Indicates the status of the session.
  • DB User — The login name for this session.
  • CPU Usage — The CPU usage of this session.
  • Total I/O — The total I/O of this session.
  • Memory Usage — The memory usage of this session.
  • Active Time — The active time of this session.
  • 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 user logged into the session.
  • Program — The name of the program the user is running to access Azure SQL Database.
  • Last Batch Time — The time when the last batch started execution.
  • Blocked by — The session ID that blocks this session.
  • Last Wait Type — The type of the last wait.
  • Last Wait Resource — The resource for which the session is currently waiting.
  • Physical Write — 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.
  • Last Tran. Start Time — The start time of the last transaction.