Chatta subito con l'assistenza
Chat con il supporto

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

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

Metric Descriptions
 
 
 
Metric
Description

Desired State

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.

Related Documents

The document was helpful.

Seleziona valutazione

I easily found the information I needed.

Seleziona valutazione