Skip to content

Instantly share code, notes, and snippets.

@varun-dhawan

varun-dhawan/QueryStore_LogAnalytics_Integration.md

Last active March 23, 2023 01:48
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save varun-dhawan/a0667041dadc2d0a6deb121ad6207b1d to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save varun-dhawan/a0667041dadc2d0a6deb121ad6207b1d to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. varun-dhawan revised this gist Mar 23, 2023. 1 changed file with 6 additions and 2 deletions.
    8 changes: 6 additions & 2 deletions QueryStore_LogAnalytics_Integration.md
    View file
    Original file line number Diff line number Diff line change
    @@ -46,8 +46,12 @@ Starting with Azure Postgres Single Server, Query store for PostgreSQL is integr

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

    1. Identify Long Running Query
    2. Analysis of Wait Stats
    1. Long Running Queries
    2. Wait Statistics
    3. Top Queries by Calls
    4. High Memory Queries
    5. High IOPS Queries
    6. Wait Statistics

    ## References

  2. varun-dhawan revised this gist Mar 23, 2023. No changes.
  3. varun-dhawan revised this gist Mar 23, 2023. 1 changed file with 2 additions and 0 deletions.
    2 changes: 2 additions & 0 deletions QueryStore_LogAnalytics_Integration.md
    View file
    Original file line number Diff line number Diff line change
    @@ -10,6 +10,8 @@ ms.date: 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.
  4. varun-dhawan revised this gist Mar 23, 2023. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion QueryStore_LogAnalytics_Integration.md
    View file
    Original file line number Diff line number Diff line change
    @@ -8,7 +8,7 @@ ms.author: varundhawan
    ms.date: 1/3/2023
    ---

    # Query store integration with log analytics to derive performance insights
    # Query Performance Insights

    ## What is Query Store

  5. varun-dhawan revised this gist Mar 23, 2023. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion QueryStore_LogAnalytics_Integration.md
    View file
    Original file line number Diff line number Diff line change
    @@ -1,5 +1,5 @@
    ---
    title: Query store integration with log analytics to derive performance insights
    title: Query Performance Insights
    description: High level spec doc
    ms.service: postgresql
    ms.subservice: flexible-server
  6. varun-dhawan revised this gist Mar 13, 2023. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion QueryStore_LogAnalytics_Integration.md
    View file
    Original file line number Diff line number Diff line change
    @@ -52,5 +52,5 @@ Integrate Azure Postgres Query Store with Log Analytics, and enabled following s
    Monitor Performance with Query Store - https://learn.microsoft.com/en-us/azure/postgresql/flexible-server/concepts-query-store

    ## Team
    * Engineering - Saikat.Sen@microsoft.com; [email protected]
    * Engineering - Sergiu.Ernu@microsoft.com; sbalijepalli@microsoft.com; Saikat.Sen@microsoft.com
    * Product - [email protected]
  7. varun-dhawan revised this gist Mar 13, 2023. 1 changed file with 2 additions and 2 deletions.
    4 changes: 2 additions & 2 deletions QueryStore_LogAnalytics_Integration.md
    View file
    Original file line number Diff line number Diff line change
    @@ -1,5 +1,5 @@
    ---
    title: Azure Database for PostgreSQL Flexible Server - Query store integration with log analytics
    title: Query store integration with log analytics to derive performance insights
    description: High level spec doc
    ms.service: postgresql
    ms.subservice: flexible-server
    @@ -8,7 +8,7 @@ ms.author: varundhawan
    ms.date: 1/3/2023
    ---

    # Azure Database for PostgreSQL Flexible Server - Query Store integration with Log Analytics (LA)
    # Query store integration with log analytics to derive performance insights

    ## What is Query Store

  8. varun-dhawan revised this gist Jan 9, 2023. 1 changed file with 16 additions and 1 deletion.
    17 changes: 16 additions & 1 deletion QueryStore_LogAnalytics_Integration.md
    View file
    Original file line number Diff line number Diff line change
    @@ -38,4 +38,19 @@ Starting with Azure Postgres Single Server, Query store for PostgreSQL is integr
    * Identifying and tuning top expensive queries
    * A/B testing
    * Keeping performance stable during upgrades
    * Identifying and improving ad hoc workloads
    * Identifying and improving ad hoc workloads

    ## Scope of Zn plan

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

    1. Identify Long Running Query
    2. Analysis of Wait Stats

    ## References

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

    ## Team
    * Engineering - [email protected]; [email protected]
    * Product - [email protected]
  9. varun-dhawan revised this gist Jan 9, 2023. 1 changed file with 10 additions and 1 deletion.
    11 changes: 10 additions & 1 deletion QueryStore_LogAnalytics_Integration.md
    View file
    Original file line number Diff line number Diff line change
    @@ -22,11 +22,20 @@ The Query Store feature in Azure Database for PostgreSQL provides a way to track
    1. Identifying top N queries that are waiting on resources
    1. 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.

    ## Access Query Store information
    ## 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
  10. varun-dhawan revised this gist Jan 9, 2023. 1 changed file with 4 additions and 4 deletions.
    8 changes: 4 additions & 4 deletions QueryStore_LogAnalytics_Integration.md
    View file
    Original file line number Diff line number Diff line change
    @@ -26,7 +26,7 @@ The Query Store feature in Azure Database for PostgreSQL provides a way to track

    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. This will only store query plans for non-utility queries.
    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.
  11. varun-dhawan revised this gist Jan 9, 2023. 1 changed file with 8 additions and 0 deletions.
    8 changes: 8 additions & 0 deletions QueryStore_LogAnalytics_Integration.md
    View file
    Original file line number Diff line number Diff line change
    @@ -22,3 +22,11 @@ The Query Store feature in Azure Database for PostgreSQL provides a way to track
    1. Identifying top N queries that are waiting on resources
    1. 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.

    ## 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. This will only store query plans for non-utility queries.
  12. varun-dhawan revised this gist Jan 9, 2023. 1 changed file with 5 additions and 5 deletions.
    10 changes: 5 additions & 5 deletions QueryStore_LogAnalytics_Integration.md
    View file
    Original file line number Diff line number Diff line change
    @@ -16,9 +16,9 @@ The Query Store feature in Azure Database for PostgreSQL provides a way to track

    ## Common scanarios for customers to use Query Store

    * Determining the number of times a query was executed in a given time window
    * Comparing the average execution time of a query across time windows to see large deltas
    * Identifying longest running queries in the past few hours
    * Identifying top N queries that are waiting on resources
    * 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.
    1. Determining the number of times a query was executed in a given time window
    1. Comparing the average execution time of a query across time windows to see large deltas
    1. Identifying longest running queries in the past few hours
    1. Identifying top N queries that are waiting on resources
    1. 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.

  13. varun-dhawan revised this gist Jan 9, 2023. 1 changed file with 11 additions and 2 deletions.
    13 changes: 11 additions & 2 deletions QueryStore_LogAnalytics_Integration.md
    View file
    Original file line number Diff line number Diff line change
    @@ -8,8 +8,17 @@ ms.author: varundhawan
    ms.date: 1/3/2023
    ---

    # Azure Database for PostgreSQL Flexible Server - Query store integration with log analytics
    # Azure Database for PostgreSQL Flexible Server - Query Store integration with Log Analytics (LA)

    ## 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.
    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

    * Determining the number of times a query was executed in a given time window
    * Comparing the average execution time of a query across time windows to see large deltas
    * Identifying longest running queries in the past few hours
    * Identifying top N queries that are waiting on resources
    * 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.

  14. varun-dhawan revised this gist Jan 9, 2023. 1 changed file with 5 additions and 1 deletion.
    6 changes: 5 additions & 1 deletion QueryStore_LogAnalytics_Integration.md
    View file
    Original file line number Diff line number Diff line change
    @@ -8,4 +8,8 @@ ms.author: varundhawan
    ms.date: 1/3/2023
    ---

    # Azure Database for PostgreSQL Flexible Server - Query store integration with log analytics
    # Azure Database for PostgreSQL Flexible Server - Query store integration with log analytics

    ## 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.
  15. varun-dhawan created this gist Jan 9, 2023.
    11 changes: 11 additions & 0 deletions QueryStore_LogAnalytics_Integration.md
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,11 @@
    ---
    title: Azure Database for PostgreSQL Flexible Server - Query store integration with log analytics
    description: High level spec doc
    ms.service: postgresql
    ms.subservice: flexible-server
    ms.topic: conceptual
    ms.author: varundhawan
    ms.date: 1/3/2023
    ---

    # Azure Database for PostgreSQL Flexible Server - Query store integration with log analytics