QueryStore_LogAnalytics_Integration.md

title	description	ms.service	ms.subservice	ms.topic	ms.author	ms.date
Query Performance Insights	High level spec doc	postgresql	flexible-server	conceptual	varundhawan	1/3/2023

Query Performance Insights

Query Performance Insights is a feature in Azure Postgres that provides detailed insights into the performance of queries executed against your PostgreSQL database. Customers can use the Query Performance Insights feature in Azure Database for PostgreSQL to visually inspect your database workloads in the Azure portal to identify long-running queries, inspect wait statistics associated with queries, and detect changes in query performance. You can view the performance insights by selecting Query performance Insight under the Intelligent Performance section of the menu bar in the portal page of your Azure Database for PostgreSQL serve.

What is Query Store

The Query Store feature in Azure Database for PostgreSQL provides a way to track query performance over time. Query Store simplifies performance-troubleshooting by helping you quickly find the longest running and most resource-intensive queries. Query Store automatically captures a history of queries and runtime statistics, and it retains them for your review. It slices the data by time so that you can see temporal usage patterns. Data for all users, databases and queries is stored in a database named azure_sys in the Azure Database for PostgreSQL instance.

Common scanarios for customers to use Query Store

1. Determining the number of times a query was executed in a given time window
2. Comparing the average execution time of a query across time windows to see large deltas
3. Identifying longest running queries in the past few hours
4. Identifying top N queries that are waiting on resources
5. Understanding wait nature for a particular query To minimize space usage, the runtime execution statistics in the runtime stats store are aggregated over a fixed, configurable time window. The information in these stores can be queried using views.

How to access Query Store information

Query Store data is stored in the azure_sys database on your Postgres server, and can be accessed using following views

1. QUERY_STORE.QS_VIEW - This view returns all the data in Query Store. There is one row for each distinct database ID, user ID, and query ID.
2. QUERY_STORE.QUERY_TEXTS_VIEW - This view returns query text data in Query Store. There is one row for each distinct query_text.
3. QUERY_STORE.PGMS_WAIT_SAMPLING_VIEW - This view returns wait events data in Query Store. There is one row for each distinct database ID, user ID, query ID, and event.
4. QUERY_STORE.QUERY_PLANS_VIEW - This view returns the query plan that was used to execute a query. There is one row per each distinct database ID, and query ID.

What is Query Store - Log Analytics integration?

Starting with Azure Postgres Single Server, Query store for PostgreSQL is integrated with Azure Monitor diagnostic settings. This integration allows customers to sent the query store data (JSON format) to Azure Monitor Logs for analytics and alerting, Event Hubs for streaming, and Azure Storage for archiving. Once the data is in log analytics, customers can then use this in wide variety of scenarios such as:

• Identifying and tuning top expensive queries
• A/B testing
• Keeping performance stable during upgrades
• Identifying and improving ad hoc workloads

Scope of Zn plan

Integrate Azure Postgres Query Store with Log Analytics, and enabled following scenarios:

1. Long Running Queries
2. Wait Statistics
3. Top Queries by Calls
4. High Memory Queries
5. High IOPS Queries
6. Wait Statistics

References

Monitor Performance with Query Store - https://learn.microsoft.com/en-us/azure/postgresql/flexible-server/concepts-query-store

Team

• Engineering - Sergiu.Ernu@microsoft.com; sbalijepalli@microsoft.com; Saikat.Sen@microsoft.com
• Product - varun.dhawan@microsoft.com