Tag Archives: AlwaysON

AlwaysOn Availability Groups – Query to Find Latency Part 2

Sometimes there is a need to maintain some historical content on how current your Availability Group is performing. The following script builds off the original script in Part 1. It populates a global temp table with information every minute. I really got the idea from Brent Ozar’s sp_SQL Blitz. I took the road of least intrusive by creating the ##LatencyCheck_tbl global temp table. Sergey Gigoyan has a good post on global temp tables and good links at the end of the post, too.

The ##LatencyCheck_tbl temp table is populated after 1 minute and every minute until cancelled. Need to make sue the table gets dropped after you have done you analysis. It is a good idea to keep an eye on the size of the table, too (execute sp_spaceused ‘##LatencyCheck_tbl’). Like any temp table, service restart will drop the table the process cancelled. If this is something you would like always run. you can look into creating a SQL Agent job or even a procedure that auto starts. I don’t suggest doing that. If you do choose to do so, you should make sure you monitor of have a process in place to reduce the size of the temp table. Also, look into grabbing Perfmon counters and Extended Events. Everything has overhead and we do not want to succumb to the “Watcher Effect”..DAH! DAH! [Organ music].

Read more »

AlwaysOn Availability Groups – Query to Find Latency Part 1

AlwaysOn Availability Groups latency can be a real concern. Microsoft provides a AlwaysON Dashboard to monitor and some administration of Availability Groups in SQL Server Management Studio(SSMS). Ben Snaidero as an excellent posting about the dashboard and T-SQL monitoring of AG.

The query below uses sys.dm_hadr_database_replica_states to query the current state. It can be run on the primary or secondary server. We use a readable secondary and it is nice to see how close to real-time updates. As a roll-your-own solution, you could create a SQL Agent job to monitor and send an email when certain criteria is met.

Read more »