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

Using Foglight for Azure SQL Database

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.

Viewing the Databases Dashboard

 
* 
NOTE: The following section describes the various components of the Databases dashboard. To learn how to customize this dashboard’s display and focus on a requested instance, see Selecting an Instance to Monitor.

The Instance home page is launched by accessing the Databases dashboard.

To access the Databases dashboard:
1
Ensure that the navigation panel on the left is open.
To open the navigation panel, click the right-facing arrow on the left.
2
On the navigation panel, click Homes > Databases.

The Databases dashboard provides an at-a-glance view of the monitored environment, with all of the currently monitored database types.

The Databases dashboard includes these sections:

•
Database cartridge type tiles — each tile represents a database type (SQL Server, SQL Server BI, Azure SQL, Oracle, SAP ASE, DB2, MongoDB, Cassandra, or All Instances) and displays the number of instances for each database type, along with a breakdown according to the instance health state severity (Normal, Warning, Critical, Fatal).
When selecting the Azure SQL Tile, an Azure SQL Databases table will be displayed. It shows the “regular” Azure SQL Databases.
•
Status section — includes the following components:
•
Status summary — a color-coded bar, which provides a visual representation of the summarized health condition of all instances listed in the Database Group table.
The status summary bar provides a graphic representation of the monitored environment’s current state, broken down to the number of instances and their current health state: Fatal, Critical, Warning, Normal or Unknown.
•
Database-specific health summary — when the database group All is selected, this section displays all of the currently monitored instances for each database type, divided by their health state (for example: four Azure SQL Databases, three of which have the health state Warning and one is indicated with the health state Fatal). When a user-defined database group is selected, this section displays data only about the agents included within the selected group.
* 
NOTE: The Status Indicators section can be used for filtering the Databases Group table to display only instances that meet a criterion set in this section. For details, see Filtering the Display by Severity .
•
The Monitor button — Use this button to add instances to be monitored.
•
The Configure Alarms button — Takes you directly to the Administration > Alarms page. On the Alarms page you can view the alarms as well as configure the Alarm Template and alarm notification settings.
•
The Settings button — After selecting one or more instances of the same database type, use this button to set options for collecting, storing, and displaying data for the selected instances, as well as configuring the connection to SQL Performance Investigator (if installed). For details, see Administering Foglight for Azure SQL Database .
•
Currently selected database group table — a list of all monitored databases within the database group that is currently selected in the Databases section. For details, see Using the Currently Selected Database Group Table .
•
The Show Quick View () button — provides the ability to open a quick view of the instance at the bottom of the screen.
•
The Select dashboards () button — provides direct link to several drilldowns and panels, thereby saving the need to navigate to these locations through the Overview drilldown.
 
* 
IMPORTANT: If the display is filtered to show only Azure SQL Databases the Databases table is renamed to Azure SQL.

Selecting an Instance to Monitor

Because the Databases dashboard displays by default all of the currently monitored databases, it is advisable to customize the dashboard’s view to display only the relevant instances, and then preview such instances to decide which to monitor.

To select an instance to monitor:
1
Display only instances relevant for your needs, using one of the following methods:
•
Filter by database type — if multiple database types are being monitored, click the database type tile that represents the requested type (in this case, Azure SQL).
•
Filter by severity — use the status indicators to display only instances of a specific database type that share a specific health state severity. For further details, see Filtering the Display by Severity .
•
Create user-defined groups — use the Databases area to create groups that contain only the databases that need to be monitored for a specific need. For further details, see Creating User-defined Database Groups .
2
View the selected instances’ severity level, using the status indicators.
3
Click the requested instance to view a cue card with a preview of the instance’s most significant performance indicators.
4
Click Home Page on the cue card to launch a full-screen view of the requested instance.
* 
IMPORTANT: Launching a full-screen view of the requested database by clicking the Home Page link can be carried out only for instances monitored in Foglight for Azure SQL Database mode.
Filtering the Display by Severity

The Status Indicators section can be used for filtering the Databases Group table to display only instances that meet a criterion set in this section.

To filter the display by severity:
1
Click a a certain type of severity within a a specific database type (for example: Azure SQL databases whose severity level is Critical).
The selected filter is shown in the table’s title.
2
To select another filter, click the required status indicator (for example: Azure SQL > Normal severity).
Creating User-defined Database Groups

Use the Databases area of the navigation pane to create, edit, and remove groups of database instances. Click Edit Group to add or remove database instances from the sub-group.

To create a database group:
1
Select the parent database instance group, Database Group, under Databases in the navigation pane.
2
Click the button.
The Add Group dialog box opens.
3
Type a name for the group in the Name field.
4
Type a description for the group (optional).
5
Select an instance in the Available column, and click [>] to move the instance to the Selected column.
Alternatively, click [>>] to move all of the databases to the Selected column.
6
Click OK.
The group name appears in the Database Groups list.
The database instances are listed in the Databases pane.

After adding one or more user-defined groups, it is possible to add sub-groups to these groups.

To add sub-groups to a selected group:
1
Position the cursor on the requested group’s name.
2
Repeat Step 2 to Step 6 described in To create a database group: .
* 
NOTE: The Add Group dialog box displays the name of the parent group in the Parent field. Before proceeding, ensure that the sub-group is added to the requested group.
To remove a database group:
1
Select the group to be removed.
2
Click the button.
The Remove Groups dialog box appears, asking approval to remove the selected group with all its sub groups.
 
3
Click Remove.
The group is removed from the Database Groups list.
To edit a database sub-group:
1
Select the requested group.
2
Click the button.
The Edit Group dialog box opens.
3
To add an instance to the group, select the requested instance in the Available column and click [>] to move the selected instance to the Selected column.
4
To remove an instance from the sub-group, select the requested instance in the Selected column, and click [<].
The selected instance moves to the Available column.
5
Click OK.
Components Shared by All Foglight for Azure SQL Database Screens

The tables’ Customizer button and the In-context action buttons are common to all Foglight for Azure SQL Database screens.

The Customizer button — use this button, which is found on the upper right corner of each table, to create a custom filter for the table. The filter is created by clicking Customizer and specifying the criteria that the various values should meet in order to be displayed in the table (having a specific name, exceeding a certain size, and so on). For detailed instructions and examples, see Foglight User Guide > Working with Dashboards > Working with Tables.

Figure 1. Customizer button

In-context actions buttons — found on the upper right corner of all screens:

•
Refresh — clicking this button retrieves the data acquired using the most recent sampling.
•
Agent Settings — when using panels whose configuration is set using the Databases Administration dashboard, clicking this button opens the relevant screen in the Databases Administration dashboard (for example, clicking this button from within the Databases > Locks panel opens the Locks screen).
Using the Currently Selected Database Group Table

The currently selected database group table displays the following columns.

 
Table 1. Currently Selected Database Group Table
Name
Description
Database

Sev

The highest severity alarm of the Azure SQL Database, which determines the database’s health state.

Name

The name of the Azure SQL Database. In addition, this row includes the Go to Home Page button.

Version

The version number of the Azure SQL Database.

Up Since

The date and time that the Azure SQL Database was last restarted.

Workload

The workload (average active sessions) for the Azure SQL Database. When holding the cursor over the workload graph, the dwell displays a chart that shows the workload history over the specified time range.

DB Alarms

The number of warning, critical, and fatal alarms for the Azure SQL Database.

When holding the cursor over one of the alarm counts, the dwell displays the most recent alarms invoked for this database, sorted by severity.

Clicking this field displays the Alarms list, which is listed by severity order. See the Foglight Online Help, Monitoring System-Wide Alarms for details on the alarm information.

System Utilization

Host

The name of the computer that is hosting the database.

Monitoring Status

Agent

The operational status of the monitoring agent.

When the agent instance is running, the State icon () is green, and holding the cursor over the icon displays the message Collecting Data.

When the agent instance is running but not collecting data, holding the cursor over the icon displays one of the following status messages listed below.

•
Starting
•
Stopped
•
Stopping
•
Unknown

When SQL PI is configured the SQL PI icon is displayed.

Foglight for Azure SQL Database Overview 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.

 
Table 2. Foglight for Azure SQL Database Home Page Toolbar
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
 
* 
NOTE: 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.
Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating