Query Store: Azure Database for MySQL (2023)

  • Article
  • 7 minutes to read

APPLIES TO: Query Store: Azure Database for MySQL (1)Azure Database for MySQL: Single server


Azure Database for MySQL: Single server is being retired. We recommend upgrading to Azure Database for MySQL - Flexible Server. For more information on migrating to Azure Database for MySQL - Flexible Server, seeWhat's happening with Azure Database for MySQL single server?

Applies to:Azure Database for MySQL 5.7, 8.0

The Azure Database for MySQL Query Store feature provides a way to track query performance over time. Query Store simplifies performance troubleshooting by helping you quickly find the most time-consuming and resource-intensive queries. Query Store automatically captures query history and runtime statistics and preserves them for your review. It separates data by time window so you can see database usage patterns. Data from all users, databases and queries are stored inMySQLSchema database on instance of Azure Database for MySQL.

Common scenarios for using the Query Store

Query Store can be used in a variety of scenarios, including the following:

  • reverse query detection
  • Determine how many times a query was executed in a given time window
  • Compare the average execution time of a query across time windows to see large deltas

Enable query storage

Query Store is an optional feature, so by default it is not active on a server. Query Store is enabled or disabled globally for all databases on a given server and cannot be enabled or disabled on a per-database basis.

Enable Query Store through the Azure portal

  1. Sign in to the Azure portal and select your Azure Database for MySQL server.
  2. Chooseserver parameterinside sidethe settingsmenu section.
  3. Look for the query_store_capture_mode parameter.
  4. Set the value to ALL andsave not computer.

To enable wait statistics on your query store:

  1. Look for the query_store_wait_sampling_capture_mode parameter.
  2. Set the value to ALL andsave not computer.

Allow up to 20 minutes for the first batch of data to be stored in the MySQL database.

Information in the query repository

The query store has two repositories:

  • A runtime statistics store that stores information about query execution statistics.
  • Wait statistics store to store wait statistics information.

To minimize memory usage, runtime runtime statistics are aggregated in the runtime statistics store over a fixed, configurable window of time. Information in these stores is visible by querying the Query Store views.

The following query returns information about the queries in the query store:

SELECT * FROM mysql.query_store;

Or this wait statistics query:

AUSWAHL * DESDE mysql.query_store_wait_stats;

query query search


Wait statistics should not be enabled during peak workload periods or indefinitely for sensitive workloads.
Use caution when enabling wait statistics on workloads with high CPU utilization or on servers configured with fewer virtual cores. It shouldn't be on all the time.

Wait event types combine different wait events into buckets according to similarity. Query Store provides the type of wait event, the specific wait event name, and the query in question. The ability to correlate this wait information with query execution time statistics means you can gain a deeper understanding of what contributes to query performance.

Here are some examples of how you can use Query Store wait statistics to learn more about your workload:

highlock awaitsReview the query texts for the affected queries and identify the target entities. Search the Query Store for other queries that modify the same entity, run frequently, and/or have a long duration. Once you identify these queries, you should change your application logic to improve concurrency or use a less restrictive isolation level.
High buffer I/O waitFind queries with a large number of physical reads in the query store. If they match queries with high I/O latencies, consider introducing an index on the underlying entity to perform lookups instead of checks. This would minimize query I/O overhead. Check theperformance recommendationsto your server on the portal to see if there are any index recommendations for that server that optimize queries.
high standby memoryFind the most memory intensive queries in Query Store. These queries are likely to delay the progress of the affected queries. Check theperformance recommendationsto your server on the portal to see if there are any index recommendations that optimize these queries.

configuration options

When Query Store is enabled, it stores data in 15-minute aggregation windows, up to 500 distinct queries per window.

The following options are available for configuring Query Store parameters.

ParameterDescriptionPatternTo reach
query_store_capture_modeEnable or disable the Query Store feature based on value. Note: If performance_schema is disabled, enabling query_store_capture_mode enables performance_schema and a subset of the performance schema tools required for this feature.NONONE, ALL
query_store_capture_intervalThe Query Store polling interval in minutes. Lets you specify the interval over which query metrics are aggregatedfifteen5 - 60
query_store_capture_utility_consultasEnable or disable to capture all utility queries running on the system.NOTBUT
query_store_retention_period_in_daysTime window in days to keep data in Query Store.71 - 30

The following options are specific to wait statistics.

ParameterDescriptionPatternTo reach
query_store_wait_sampling_capture_modeAllows you to enable/disable wait statistics.NONONE, ALL
query_store_wait_sampling_frequencyChanges the hold sample rate in seconds. 5 to 300 seconds.305-300


nowquery_store_capture_modeoverrides this setting, which means that bothquery_store_capture_modejquery_store_wait_sampling_capture_modeMust be enabled for ALL for wait stats to work. Yesquery_store_capture_modeis disabled, wait statistics are also disabled because wait statistics uses the enabled performance schema and the query text captured by Query Store.

Use oblue portalÖCLI do Azureto get or set a different value for a parameter.

views and features

View and manage Query Store using the following views and features. someone inSelect the privileged public roleYou can use these views to display the data in the Query Store. These views are only available inMySQLData base.

Queries are normalized by looking at their structure after removing literals and constants. If two queries are identical except for the literal values, they will have the same hash.


This view returns all data in the query store. There is a row for each unique database ID, user ID, and query ID.

Namedata typeIS_NULLABLEDescription
scheme namevarchar(64)NOTscheme name
query_idgrande (20)NOTUnique ID generated for the specific query. If the same query is run against a different schema, a new ID will be generated
datetime_idtime stampNOTThe timestamp when the query was executed. This is based on the query_store_interval setting
query_summary_textbigger textNOTNormalized query text after removing all literals
query_example_textbigger textNOTFirst occurrence of actual query with literals
query_digest_truncateda littleEWhether the query text was truncated. Value is Yes if the query is greater than 1KB
run_countgrande (20)NOTThe number of times the query was executed for this timestamp ID/during the configured interval period
account_warninggrande (20)NOTNumber of alerts generated by this query during internal processing
error_countgrande (20)NOTNumber of errors this query generated during the interval
sum_wait_timerDoubleETotal execution time of this query during the interval
avg_timer_waitDoubleEAverage execution time of this query during the interval
min_timer_waitDoubleEMinimum execution time for this query
max_timer_waitDoubleEMaximum execution time
sum_lock_timegrande (20)NOTTotal time spent running this query on all locks during this time window
sum_affected_rowsgrande (20)NOTNumber of rows affected
sum_rows_sentgrande (20)NOTNumber of lines sent to the client
sum_rows_examinedgrande (20)NOTNumber of lines examined
sum_select_full_joingrande (20)NOTNumber of complete unions
sum_select_scangrande (20)NOTNumber of scans selected
sum_sort_rowsgrande (20)NOTNumber of sorted rows
sum_no_index_usedgrande (20)NOTNumber of times the query did not use an index
sum_no_good_index_usedgrande (20)NOTNumber of times the query execution engine did not use good indexes
sum_created_tmp_tablesgrande (20)NOTTotal number of temporary tables created
sum_created_tmp_disk_tablesgrande (20)NOTTotal number of temp tables created on disk (generating I/O)
seen for the first timetime stampNOTThe first occurrence (UTC) of the query during the aggregation window
last seentime stampNOTThe last occurrence (UTC) of the query during this aggregation window


This view returns data from wait events in Query Store. There is a row for each unique database ID, user ID, query ID, and event.

Namedata typeIS_NULLABLEDescription
starting rangetime stampNOTStart of the break (15-minute increment)
end_intervaltime stampNOTEnd of break (15-minute increment)
query_idgrande (20)NOTUnique ID generated in normalized query (from Query Store)
query_digest_idvarchar(32)NOTNormalized query text after removing all literals (from Query Store)
query_summary_textbigger textNOTFirst hit of actual query with literals (from Query Store)
event typevarchar(32)NOTWait for the event category
event namevarchar(128)NOTWait event name
star_accountgrande (20)NOTNumber of wait events collected during the polling interval
sum_timer_wait_msDoubleNOTTotal wait time (in milliseconds) for this query during the interval


mysql.az_purge_querystore_data(TIMESTAMP)Deletes all Query Store data before the specified timestamp
mysql.az_procedure_purge_querystore_event(TIMESTAMP)Clears all wait event data before the specified timestamp
mysql.az_procedure_purge_recommendation(TIMESTAMP)Deletes recommendations that expire before the specified timestamp

Limitations and known issues

  • If a MySQL server has the parameteronly readingEnabled, Query Store cannot retrieve data.
  • Query Store functionality may break when long Unicode queries (>= 6000 bytes) are encountered.
  • The retention period for wait statistics is 24 hours.
  • Wait statistics use sampling to capture a subset of events. The frequency can be changed with the parameterquery_store_wait_sampling_frequency.

Next steps

  • learn more aboutQuery performance information
Top Articles
Latest Posts
Article information

Author: Twana Towne Ret

Last Updated: 03/08/2023

Views: 6106

Rating: 4.3 / 5 (64 voted)

Reviews: 95% of readers found this page helpful

Author information

Name: Twana Towne Ret

Birthday: 1994-03-19

Address: Apt. 990 97439 Corwin Motorway, Port Eliseoburgh, NM 99144-2618

Phone: +5958753152963

Job: National Specialist

Hobby: Kayaking, Photography, Skydiving, Embroidery, Leather crafting, Orienteering, Cooking

Introduction: My name is Twana Towne Ret, I am a famous, talented, joyous, perfect, powerful, inquisitive, lovely person who loves writing and wants to share my knowledge and understanding with you.