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].