Chat now with support
Chat with Support

Foglight for Azure SQL Database (Cartridge) 5.9.7.20 - 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.

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.

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

 

* 
NOTE: 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.

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.

* 
NOTE: If you attempt to select instances of more than one type of database, such as a SQL Server database and an Azure SQL Database, an error message is displayed.
To open the Databases Administration dashboard:
1
In the navigation panel, under Homes, click Databases.
2
Select the row check boxes beside one or more Azure SQL databases.
3
Click Settings and then click Administration.
The Administration dashboard opens, containing settings for all the selected agents. Settings are broken down into categories, which are organized under an Azure SQL tree.
* 
TIP: The list of agents you selected can be found by clicking Selected Agents.
Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating