High CPU driving query in azure database for postgresql

sql user 86

Team,

i have azure database for postgresql and CPU is gradually increasing and i have query store enabled and i cannot increase the resources due to cost limitation.

How to get list of queries responsible for consuming CPU from during a specific period?. I'm looking for exact Query store queries which i can directly run in 'azure_sys' database. the below link doesn't have any QS queries.

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

The query performance insights just gives high duration queries and doesn't give which query is driving the CPU..

Appreciate the help. thanks

Anurag Sharma 17,606 Reputation points

2021-07-23T09:06:00.117+00:00

Hi @sql user , welcome to Microsoft Q&A forum.

We are looking into this and will get back at the earliest.
Anurag Sharma 17,606 Reputation points

2021-07-26T05:58:21.577+00:00
Hi @sql user , thanks for your patience. As such there is directly no way I could find where in we can get the exact query for CPU utilization. However we can first use the pg_stat_statements to check which queries are taking longer to execute. We can thus optimize those queries and check the CPU usage.

You can also run the below query to check the long running transaction as per the article.

SELECT max(now() -xact_start) FROM pg_stat_activity WHERE state IN ('idle in transaction','active');

Share via

High CPU driving query in azure database for postgresql

Your answer