The postings on this site are my own and do not represent my Employer's positions, advice or strategies.

LifeAsBob - Blog

 

Home

No Ads ever, except search!
Thursday, March 28, 2024 Login
Public

Monitoring read-only PAAS SQL node 2/7/2021 7:39:34 PM

If you connect to your Business Critical Azure SQL instance with ADS or SMSS and in the Additional Connection Parameters you specify ApplicationIntent=ReadOnly, you are then connected via your admin account to that read only replica and can do some performance monitoring via database scoped DMVs:


Monitoring and troubleshooting read-only replicas

When connected to a read-only replica, Dynamic Management Views (DMVs) reflect the state of the replica, and can be queried for monitoring and troubleshooting purposes. The database engine provides multiple views to expose a wide variety of monitoring data.

Commonly used views are:

MONITORING AND TROUBLESHOOTING READ-ONLY REPLICAS

Name

Purpose

sys.dm_db_resource_stats

Provides resource utilization metrics for the last hour, including CPU, data IO, and log write utilization relative to service objective limits.

sys.dm_os_wait_stats

Provides aggregate wait statistics for the database engine instance.

sys.dm_database_replica_states

Provides replica health state and synchronization statistics. Redo queue size and redo rate serve as indicators of data latency on the read-only replica.

sys.dm_os_performance_counters

Provides database engine performance counters.

sys.dm_exec_query_stats

Provides per-query execution statistics such as number of executions, CPU time used, etc.

sys.dm_exec_query_plan()

Provides cached query plans.

sys.dm_exec_sql_text()

Provides query text for a cached query plan.

sys.dm_exec_query_profiles

Provides real time query progress while queries are in execution.

sys.dm_exec_query_plan_stats()

Provides the last known actual execution plan including runtime statistics for a query.

sys.dm_io_virtual_file_stats()

Provides storage IOPS, throughput, and latency statistics for all database files.

  

You can also set up xevent sessions : Read queries on replicas - Azure SQL Database | Microsoft Docs

 


Blog Home