Foglight for Azure SQL Database (Cartridge) 6.3.0

Installation About Monitoring Extensions Installing and Monitoring a Single Azure SQL Database

Viewing the Databases Dashboard Selecting an Instance to Monitor Foglight for Azure SQL Database Overview Dashboard SQL Performance Investigator (SQL PI) Azure SQL Activity Drilldown

Administering Foglight for Azure SQL Database

Opening the Databases Administration Dashboard Reviewing the Administration Settings Customizing Alarms for Foglight for Azure SQL Database Rules

Introducing the Alarms View Modifying Alarm Templates Configuring Email Notifications Cloning Agent Settings Reviewing Rule Definitions

Defining Connection Details Administering SQL Performance Investigator

Reporting

Generating Reports for a Foglight for Azure SQL Database

Reference

Overview of Collections

Usability Query Store Query Store Top SQLs Version Info Resource Stat Wait Statistics Database Connections Summary Database Replication Database Storage Database Recommendations

SQL Performance Investigator Metrics

Glossary

Alarm Authentication Calibration Cartridge Client Machines CPU Usage CPU Wait DataFlow Deadlock Disk Transfer Time Disk Utilization DiskPerf Dispatcher Drilldown DTU Elastic Pool External Procedures Foglight Agent Manager Foglight Management Server General purpose I/O Wait Kernel Memory Kernel Mode Kill Lock Wait Log Buffer Wait Logical Reads LRU LSN Metric Network Wait OLTP Other Wait Paging Panel Physical Reads Process Random I/O Recompile Referential Integrity Relational Data Engine Schema Locks Severity Single database SPID Spinner Threshold Transaction Unused Space vCore

Overview of Collections

Foglight for Azure SQL Database collects raw data for a set of collection types. This data is used by the Rules to trigger alarms and is used to populate dashboards. The collections data tables are not viewable from Foglight for Azure SQL Database, but the data is implemented in the various drilldowns of Foglight for Azure SQL Database.


	NOTE: Several collections contain metrics whose name ends with Rate are not documented in this guide. They are used for plotting the original metric (usually called by the same name without the “Rate” suffix) on a graph for the selected time range.

Usability

Purpose

Retrieves database usability based on response time and database availability.

Collection Type

Azure SQL

Collection Sampling Settings


Frequency Mode	Collection Interval (Seconds)
Realtime Collection	60
Online	300
Offline	60

Metric Descriptions


Metric	Description
Response Time	Average duration of the SQL statements that executed during the specified time range.
Up Since	The Azure SQL DB startup time.
Uptime	The amount of time Azure SQL DB was running since it was created.

Query Store

Purpose

Collects query store information.

Collection Type

Azure SQL

Collection Sampling Settings


Frequency Mode	Collection Interval (Seconds)
Realtime Collection	60
Online	300
Offline	900

The desired operation mode of Query Store.

Actual State

The actual operation mode of Query Store. Difference between actual mode and desired mode indicates a problem.

ReadOnly Reason

When the desired_state_desc is READ_WRITE and the actual_state_desc is READ_ONLY, readonly_reason returns a bit map to indicate why the Query Store is in readonly mode.

1 - database is in read-only mode

2 - database is in single-user mode

4 - database is in emergency mode

8 - database is secondary replica (applies to Always On and Azure SQL Database geo-replication). This value can be effectively observed only on readable secondary replicas

For other modes, refer to SQL online docs.

flush_interval_minutes

The period for regular flushing of Query Store data to disk in minutes. Default value is 15 minutes.

Change by using the ALTER DATABASE <database> SET QUERY_STORE (DATA_FLUSH_INTERVAL_SECONDS = <interval>) statement.

interval_length_minutes

The statistics aggregation interval in minutes. Arbitrary values are not allowed. Use one of the following: 1, 5, 10, 15, 30, 60, and 1440 minutes. The default value is 60 minutes.

Storage Size

Size of Query Store on disk.

Max Storage Size

Maximum disk size for the Query Store.

current_storage_size_percent

Size of Query Store on disk in percentage.

stale_query_threshold_days

Number of days that the information for a query is kept in the Query Store. Default value is 30. Set to 0 to disable the retention policy.

For SQL Database Basic edition, default is 7 days.

Change by using the ALTER DATABASE <database> SET QUERY_STORE ( CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = <value> ) ) statement.

Max Plans per Query

Limits the maximum number of stored plans. Default value is 200. If the maximum value is reached, Query Store stops capturing new plans for that query. 0 indicates there is no limitation with regards to the number of captured plans.

Query Capture Mode

Actual capture mode of Query Store. can be:

•

ALL - all queries are captured

•

AUTO - capture relevant queries based on execution count and resource consumption

•

NONE - stop capturing new queries. Query Store will continue to collect compile and runtime statistics for queries that were captured already

Cleanup Mode

Actual size-based cleanup mode of Query Store. can be:

•

OFF - no cleanup.

•

AUTO - size based cleanup will be automatically activated when size on disk reaches 90 percent of max_storage_size_mb

•

Size based cleanup removes the least expensive and oldest queries first. It stops when approximately 80 percent of max_storage_size_mb is reached

Capture Wait Stats

Indicates whether Query Store performs capture of wait statistics.

Storage Utilization (%)

Percentage of current storage size out of the Max Storage Size.

Query Store Top SQLs

Purpose

Collects query store top SQLs.

Collection Type

Azure SQL

Collection Sampling Settings


Frequency Mode	Collection Interval (Seconds)
Realtime Collection	300
Online	300
Offline	300

Metric Descriptions


Metric	Description
Query ID	Query ID
Interval ID	Runtime Stats Interval ID
Object Name	The database object that the query is part of. Ad-hoc query will be NULL.
Average Duration	Average duration for the query plan within @LastMinutes.
Average CPU Time	Average CPU time for the query plan within @LastMinutes
Average Logical IO Reads	Average number of logical I/O reads for the query plan within @LastMinutes
Average Logical IO Writes	Average number of logical I/O writes for the query plan within @LastMinutes
Average Physical IO Reads	Average number of physical I/O reads for the query plan within @LastMinutes
Average Memory Grant	Average memory grant for the query plan within @LastMinutes.
Average Tempdb Space Used	Average number of page reads for the query plan within @LastMinutes
Max DOP	Maximum DOP (degree of parallelism) for the query plan within @LastMinutes
Average Log IO	Average number of bytes in the database log used by the query plan, within @LastMinutes
Total Executions	Total count of executions for the query plan within @LastMinutes.
Plan ID	Plan ID.
Last Plan ID	Previous plan ID.
Total Duration	Total duration for the query plan within @LastMinutes
Total CPU	Total CPU time for the query plan within @LastMinutes
CPU Percentage	The CPU percentage of this query out of total CPU.
LOG IO Percentage	The LOG IO percentage of this query out of total LOG IO.
DATA IO Percentage	The DATA IO percentage of this query out of total DATA IO.

Documentos relacionados

The document was helpful.

Seleccionar calificación

I easily found the information I needed.

Seleccionar calificación

Seleccione su producto:

Con el fin de ofrecerle un mejor servicio, complete el campo Motivo del chat:

Soluciones recomendadas para su problema

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

Overview of Collections

Usability

Query Store

Query Store Top SQLs