Master of None

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

/*
Create tempdb table to capture AG metrics. 
It will run until cancelled.
After ##LatencyCheck_tbl data is exported, drop table.
Example Query below.

jackdonnell.com
*/
SET NOCOUNT ON 
USE tempdb
GO

-- Remove exist table if found
IF OBJECT_ID ('tempdb..##LatencyCheck_tbl','u' ) IS NOT NULL
DROP TABLE ##LatencyCheck_tbl
GO

-- create temp table
CREATE TABLE ##LatencyCheck_tbl
(
[Last Commit Time] varchar(20),
[Time Behind Primary] varchar(30),
[Redo Queue Size] varchar(25),
[Estimated Completion Time] varchar(20),
[Estimated Recovery Time Minutes] varchar(20),
[Estimated Recovery Time Seconds] varchar(12),
[SampleTime] DATETIME
)
GO

Use tempdb
GO
-- Create GOTO loop
RepeatLoop:

	BEGIN

		WAITFOR DELAY '00:01:00'; -- Execute every 1 minute
		INSERT ##LatencyCheck_tbl
		SELECT
		Convert(VARCHAR(20),last_commit_time,22) AS [Last Commit Time],
		CAST(((DATEDIFF(s,last_commit_time,GetDate()))/3600) as varchar) + 'hour(s),' + CAST((DATEDIFF(s,last_commit_time,GetDate())%3600)/60 as varchar) + 'min, '+ CAST((DATEDIFF(s,last_commit_time,GetDate())%60) as varchar) + ' sec' AS [Time Behind Primary],
		CAST(redo_queue_size as VARCHAR(25)) AS [Redo Queue Size],
		CONVERT(VARCHAR(20),DATEADD(mi,(redo_queue_size/redo_rate/60.0),GETDATE()),22) AS [Estimated Completion Time],
		CAST(CAST((redo_queue_size/redo_rate/60.0) as decimal(10,2)) as VARCHAR(20)) AS [Estimated Recovery Time Minutes],
		CAST((redo_queue_size/redo_rate) as VARCHAR(12)) AS [Estimated Recovery Time Seconds],
		GETDATE() [SampleTime]
		from sys.dm_hadr_database_replica_states
		WHERE last_redone_time is not null

	END;
GOTO RepeatLoop -- Start process over

Now we have the proces running, we can use the following to query the output. From another SSMS window, execute the following query. I left some of the WHERE clause commented to show some examples.

SET NOCOUNT ON 
GO
USE tempdb
GO

SELECT 
	CONVERT(VARCHAR(25),CAST([SampleTime] as DATETIME),22) [SampleTime],
	[Last Commit Time],
	[Time Behind Primary],
	[Redo Queue Size],
	[Estimated Completion Time],
	[Estimated Recovery Time Minutes],
	[Estimated Recovery Time Seconds],
	--CAST([Redo Queue Size]as BIGINT)/CAST([Estimated Recovery Time Seconds] as BIGINT) [Redo_Rate (REDO_QueueSize/EstRecTimeSec)],
	DATEDIFF(second,[Last Commit Time],[SampleTime] )[Diff_Last_Commit_CurrentTime_sec]
FROM ##LatencyCheck_tbl
WHERE 1 = 1
AND [SampleTime] >= DATEADD(MINUTE, -480, GETDATE())
--AND [SampleTime] >= CONVERT(VARCHAR(10),GETDATE()-1,121)
--and( DATEPART(hour,CAST([SampleTime] as datetime)) < =3 -- less than 4 AM
--OR DATEPART(hour,CAST([SampleTime] as datetime)) >=19
--) -- Greater than 7 PM

ORDER BY CAST([SampleTime] as DATETIME) DESC;
GO

Tiny URL for this post:
 Copy