Parallel Redo Operations

Posted by Barac in SQL Monitoring, SQL Server, SQL Tips and Tricks on Apr 10th, 2021 | 0 comments

One of the most common issues with Availability Group and using readable secondary replica is with Parallel REDO operations.

On your readable secondary replica, you will notice a few different types of waits commonly related to this, but in this article, I will cover just one of them PARALLEL_REDO_TRAN_TURN

PARALLEL_REDO_TRAN_TURN

This type of waits happens in readable secondary replica and it is caused by page splits or forwarded records on heap tables triggered by new inserts or updates on primary replica.

There are few ways to fix this issue
– Reduce the number of pages splits on the primary replica
– Reduce the number of forwarded records (heap tables)
– Using trace flag 3459, DBCC TRACEON (3459, -1) and disabling parallel redo

So how to monitor this?
I am using the following query:

SELECT
ar.replica_server_name AS ServerName,
adc.database_name AS DBName,
drs.synchronization_health_desc AS AGHealthStatus,
(drs.log_send_queue_size/NULLIF(drs.log_send_rate,0)) AS LogSendSecs,
(drs.redo_queue_size/NULLIF(drs.redo_rate,0)) AS RedoSecs,
drs.redo_queue_size AS RedoSize,
ISNULL(drs.redo_rate,0) AS RedoRate
FROM sys.dm_hadr_database_replica_states AS drs
INNER JOIN sys.availability_databases_cluster AS adc
ON drs.group_id = adc.group_id AND
drs.group_database_id = adc.group_database_id
INNER JOIN sys.availability_groups AS ag
ON ag.group_id = drs.group_id
INNER JOIN sys.availability_replicas AS ar
ON drs.group_id = ar.group_id AND
drs.replica_id = ar.replica_id

And this is my RedoSeconds time:

redo_queue_size/NULLIF(redo_rate,0)

With a slight adjustment, I wrap this query in form of an alert to be forwarded to my email.

DECLARE
@EmailSubject varchar(100),
@TextTitle varchar(100),
@TableHTML nvarchar(max),
@Body nvarchar(max)
SET @EmailSubject = 'Secondary Replica Status'
SET @TextTitle = 'Availability Group Sync Alert'
SET @TableHTML =
'<html>'+
'<head><style>'+
-- Data cells styles / font size etc
'td {border:1px solid #ddd;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font-size:12pt; width="200"}'+
'</style></head>'+
'<body data-rsssl=1>'+
-- TextTitle style
'<div style="margin-top:15px; margin-left:15px; margin-bottom:15px; font-weight:bold; font-size:13pt; font-family:calibri;">' + @TextTitle +'</div>' +
-- Color and columns names
'<div style="font-family:Calibri; font-size: 12pt; "><table>'+'<tr bgcolor=#00881d>'+
'<td align=left; width="200"><font face="calibri" color=White><b>ServerName</b></font></td>'+ -- Server Name
'<td align=left; width="200"><font face="calibri" color=White><b>DBName</b></font></td>'+ -- DB Name
'<td align=left; width="200"><font face="calibri" color=White><b>AGHealthStatus</b></font></td>'+ -- AG Status
'<td align=left; width="200"><font face="calibri" color=White><b>Log Send Time (Secs)</b></font></td>'+ -- Log Send Size
'<td align=left; width="200"><font face="calibri" color=White><b>Redo Time (Secs)</b></font></td>'+ -- LogSendRate
'<td align=left; width="200"><font face="calibri" color=White><b>Redo Size (KB)</b></font></td>'+ -- RedoSize
'<td align=left; width="200"><font face="calibri" color=White><b>Redo Rate (KB/Secs)</b></font></td>'+ -- RedoRate
'</tr></div>'
-------------------------------------------------------------------------------------------------------------------------------------
----- -- Secondary Replica Stats  --------
-------------------------------------------------------------------------------------------------------------------------------------
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan'),
SecondaryReplicaStats AS (
SELECT 
	ar.replica_server_name AS ServerName, 
	adc.database_name AS DBName, 
	drs.synchronization_health_desc AS AGHealthStatus, 
	(drs.log_send_queue_size/NULLIF(drs.log_send_rate,0)) AS LogSendSecs,
	(drs.redo_queue_size/NULLIF(drs.redo_rate,0)) AS RedoSecs,
	drs.redo_queue_size AS RedoSize,
	ISNULL(drs.redo_rate,0) AS RedoRate
FROM sys.dm_hadr_database_replica_states AS drs
INNER JOIN sys.availability_databases_cluster AS adc 
	ON drs.group_id = adc.group_id AND 
	drs.group_database_id = adc.group_database_id
INNER JOIN sys.availability_groups AS ag
	ON ag.group_id = drs.group_id
INNER JOIN sys.availability_replicas AS ar 
	ON drs.group_id = ar.group_id AND 
	drs.replica_id = ar.replica_id)
----------------------------------------------------------
----------------------------------------------------------

SELECT @Body =(
SELECT
td = ServerName,
td = DBName,
td = AGHealthStatus,
td = LogSendSecs,
td = RedoSecs,
td = RedoSize,
td = RedoRate
FROM
SecondaryReplicaStats
where (LogSendSecs+RedoSecs)>-1
for XML raw('tr'), elements)
SET @Body = REPLACE(@Body, '<td>', '<td align=left width="200"><font face="calibri">')
SET @TableHTML = @TableHTML + @Body + '</table></div></body></html>'
SET @TableHTML = '<div style="color:Black; font-size:10pt; font-family:Calibri">' + @TableHTML + '</div>'
-------------------------------
----- Sending email --------
-------------------------------
if(@Body is not null)
begin
exec msdb.dbo.sp_send_dbmail
@Profile_name = 'Your_SQL_Database_Mail_Profile',
@Recipients = 'your.email@domain.com',
@Body = @TableHTML,
@Subject = @EmailSubject,
@Body_format = 'HTML'
end

Depending on your settings email may looks like following:

The last step would be to create an Agent job on the replica you want to monitor.

There are few counters you can use as alert to trigger this job, I am personally using:

“Recovery Queue”

Amount of log records in the log files of the secondary replica that have not been redone.

About Me

My name is Zoran, currently living in Auckland, New Zealand. I am a data specialist with more than 15 years of hands-on experience in database administration and optimisation. I am also, Certified Microsoft Trainer (MCT) and Microsoft Certified Solutions Expert (MCSE) with a Master‘s degree in Information Technology. Sharing knowledge and contributing to the SQL Server community is my passion. I am also an organiser of the Auckland SQL User Meetup Group. As well I am active blogger and speaker at different SQL events such as SQL Saturdays, Meetups etc.

SQL Server / Availability Group / Readable Secondary Replica Delay / Parallel Redo Operations

Similar Posts:

Leave a Reply Cancel reply

About Me

Email Subscription

Index Usage Report

Auckland SQL Group

MSDN PROFILE

Archives

SQL SERVER UPDATES

Recent Posts

Tag Cloud