How can I monitor query performance on my Azure read-replica database?

Question

I'm looking at Query Performance Insight for my read-replica database. But the information it shows are the performance stats from the primary database. How can I get the performance stats for the read-replica?

Answer 1

The query store for read-replicas is in preview. More information: https://learn.microsoft.com/en-us/sql/relational-databases/performance/query-store-for-secondary-replicas?view=sql-server-ver16

Answer 2

Query Store is not available on read replicas. One way to monitor query performance is using Dynamic Management Views (DMVs).

You can rely sys.[dm_db_wait_stats] to monitor waits see script I shared on StackOverflow.

Another example of monitoring using DMVs is running this query on each replica.

SELECT 
  s.*,
  t.*
FROM sys.dm_exec_query_stats AS s
  CROSS APPLY sys.dm_exec_sql_text(s.sql_handle) as t
  CROSS APPLY sys.dm_exec_query_plan(s.plan_handle) as p -- If you wants plans also
WHERE 1 = 1
  AND s.last_execution_time >= CAST(DATEADD(MINUTE, -30, GETUTCDATE()) AS DATETIME) -- last 30 mins
ORDER BY s.last_execution_time DESC;

This PowerShell script was written by a Microsoft Scalation Engineer.

Azure Database Watcher is a tool that can help you monitor you Azure SQL replicas also and it is at this time in Preview.