Alert for Long running/blocking queries for azure database for postgresql

sql user 86

Hello Experts.

We have a azure database for postgresql hosted and we would like to have an alert email sent for
long running queries and blocking happening on PAAS instance. can you please suggest with queries and steps? . We have already enabled diagnostic logging on PAAS instance and logs are getting stored on log analytics workspace.

Navtej Singh Saini 4,226 Reputation points Microsoft Employee

2021-03-23T23:51:58.19+00:00

Hi @sql user

Have you checked the concept of Query Store in Azure Database for Postgresql. Please go through this document for more information and let us know if you have any questions.

Regards
Navtej S
Navtej Singh Saini 4,226 Reputation points Microsoft Employee

2021-03-25T04:35:39.583+00:00

@sql user Please let us know if you need further help.
sql user 86 Reputation points

2021-03-25T05:54:40.957+00:00

yes we do have query store enabled by default and we can get the data from query store. We are looking for a way to send an email to users on daily basis on list of bloacking queries and long running queries.Is there a way to satisfy this requirement with output of long running queries from through query store feature? .
Navtej Singh Saini 4,226 Reputation points Microsoft Employee

2021-03-27T00:58:06.653+00:00

@sql user We are checking a sample for this.

1 answer

Navtej Singh Saini 4,226 Reputation points Microsoft Employee

2021-03-30T00:06:11.557+00:00

@sql user

Here is the answer from our team.

"We don't have a native notification service tied to Intelligent Performance/ Query Store but we do expose APIs that will allow you to query the data periodically. They could also query the azure_sys database directly using sql. Building a workflow on top of either is obviously one choice. The cons here is it will put load on top of your OLTP workload for the duration your Query Store query runs.
The second way to achieve this is to integrate with Log analytics. Query Store data is published to Log Analytics so that customers can integrate easily with the different popular third party monitoring apps. If your Cx uses one of these apps and they have a notification service, that's another way to go about this. This has zero impact on your core OLTP workload.
If the Cx has a read replica set up, realize that QS data is replicated to the replica as-is from the primary. The cons here is: load on replica and this will have as much lag as replication lag."

Here are three options to achieve this. Please check and let us know if any questions.

Regards
Navtej S
Please sign in to rate this answer.
sql user 86 Reputation points

2021-03-30T10:23:31.93+00:00

Thanks Navtej for the response. Appreciate.

I have one question on Log analystics part. When we check the azurediagnostics table, we just see query_id column and cannot see Query name (actual querries) to see the queries which ran on the PAAS instance. Just seeing query id, one cannot make out what are those queries running?. How do we get the query text in Azurediagnostics table..

Navtej Singh Saini 4,226 Reputation points Microsoft Employee

2021-03-31T01:55:44.403+00:00

@sql user We don’t stream query text to LA b/c it’s PII data. You can find the query text corresponding to a query id by querying the azure_sys database directly. The view query_store.query_texts_view has the mapping.

Please let us know if any other question.

Regards
Navtej S

Navtej Singh Saini 4,226 Reputation points Microsoft Employee

2021-04-01T16:19:02.557+00:00

@sql user Please let us know if you need any further help with this.

sql user 86 Reputation points

2021-06-17T02:26:05.957+00:00

Hi Navtej,

Is there a way to integrate query store (for query text) and azurediagnostics table in log analytics. bcoz when we would like to see query text as when sending an alert through LA. or any other ideas to get a complete workflow/solution?

Also, for the below comment. what are those APIs..

"We don't have a native notification service tied to Intelligent Performance/ Query Store but we do expose APIs that will allow you to query the data periodically"
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Alert for Long running/blocking queries for azure database for postgresql

1 answer

Your answer