How to get long running queries and CPU consuming queries from querystore views

Question

Using querystore views query_store.qs_view and query_store.pgms_wait_sampling_view,

how to display the long running queries and CPU consuming queries from this querystore to azure dashboard? Is there any standard method that we can suggest to our customers?

Answer 1

There is a Query Store in Azure Database for PostgreSQL, which you can use to analyze your workload and detect long running queries and CPU consuming queries.

https://learn.microsoft.com/en-us/azure/postgresql/single-server/concepts-query-store

The Query Store includes a store for wait statistics, and there is an additional capture mode query that governs wait statistics: pgms_wait_sampling.query_capture_mode can be set to none or all. The pg_qs.query_capture_mode supersedes pgms_wait_sampling.query_capture_mode. If pg_qs.query_capture_mode is none, the pgms_wait_sampling.query_capture_mode setting has no effect.

https://docs.microsoft.com/dotnet/api/azure.identity.defaultazurecredential

Additionally, you can use the Azure DataFactory Management client library for .NET to interact with Azure resources, including databases.

https://github.com/Azure/azure-docs-sdk-dotnet/blob/main/api/overview/azure/preview/resourcemanager.datafactory-readme.md

https://github.com/Azure/azure-sdk-for-net/blob/Azure.ResourceManager.DataFactory_1.0.0-beta.2/doc/dev/mgmt_quickstart.md

Answer 2

Thank you Salman for quick response. I appreciate it.

When I run a query to monitor the CPU utilization using pg_stat_statements, it was showing 21% CPU utilization for 2 queries and for the same 2 queries, it was showing 7% using query store qs_view. I am just confused which one I should considered.