Get-sqmAgRedoDiagnosis Always On

Finds out why an Always On secondary does not catch up. The log arrives (small send queue) but the redo queue keeps growing: the function samples every redo thread for a minute and names the likely bottleneck per database, with the numbers behind the verdict. One collection run answers the question, so it fits systems where every round trip is expensive.

Execution Flow

Synopsis

Run it on the secondary replica (not the listener) while the redo queue is large and the send queue is small. It samples sys.dm_hadr_database_replica_states, the redo threads in sys.dm_exec_requests (DB STARTUP and PARALLEL REDO tasks), sys.dm_os_schedulers and the wait statistics of the interval, and turns them into one verdict per database. The verdict is a heuristic from point samples and CPU deltas, not proof: the Evidence column lists the numbers behind it so you can confirm it.

Requires dbatools and VIEW SERVER STATE on the secondary. A database on a primary replica is skipped; if no database on the instance is a secondary, a warning is written and nothing is returned. Get-sqmWaitStatistics hides the PARALLEL_REDO waits as benign, this function reads them.

Verdicts

Verdict	Meaning	What to check
BlockedByQuery	Redo threads blocked by locks.	Queries on the readable secondary, Get-sqmBlockingReport.
SingleThreadedRedo	Parallel redo not active, the one redo thread is CPU-bound.	Core count, trace flag 3459, parallel redo worker limit.
SchedulerPressure	Redo threads runnable but not getting CPU.	VM CPU contention, affinity, resource governor, THREADPOOL.
PageReadIO	Redo waits for data page reads.	IOPS and queue depth limits, Get-sqmFileLatencyReport with a sample.
LogReadIO	Redo reads the log from disk, not from the log cache.	Sequential read throughput of the secondary log file.
TransactionDependency	Workers wait for commit ordering or drain each other.	What generated the log on the primary: huge transactions, rebuilds, DDL.
DispatchBound	The log-parsing thread is the limit while the workers idle.	Many small commits (small log blocks), per-core CPU speed.
WorkerSkew	One redo worker saturated, the others idle.	A hot object on the primary (single-table load or index operation).
ApplyCpuBound	All redo threads busy on CPU.	More or faster cores, less workload on the secondary.
RedoIdle	Redo idle although a backlog exists.	Look upstream: log read throughput, flow control.
Suspended / NoRedoTask / NoBacklog / Inconclusive	Data movement suspended / no redo thread found / queue below `-MinRedoQueueMB` / no rule matched.	Resume, run on the secondary, nothing to do, or sample longer.

Syntax

Get-sqmAgRedoDiagnosis
    [-SqlInstance <String>]
    [-SqlCredential <PSCredential>]
    [-Database <String[]>]
    [-SampleSeconds <Int>]
    [-IntervalSeconds <Int>]
    [-MinRedoQueueMB <Int>]
    [-Quiet]
    [-EnableException]

Parameters

Parameter	Type	Default	Description
-SqlInstance	String	$env:COMPUTERNAME	The secondary replica. Run it per replica, not against the listener.
-SqlCredential	PSCredential	—	Credential for the SQL connection.
-Database	String[]	all	Only these databases (wildcards allowed).
-SampleSeconds	Int	60	Length of the measurement, 10 to 3600. Use longer for a fluctuating redo rate.
-IntervalSeconds	Int	2	Time between two samples, 1 to 60.
-MinRedoQueueMB	Int	100	Databases with a smaller redo queue get the verdict NoBacklog.
-Quiet	Switch	false	No progress bar.
-EnableException	Switch	false	Throw terminating exceptions instead of Write-Error.

Output

One object per secondary database, largest redo queue first: RedoQueueMB, RedoQueueStartMB, TrendMBPerMin (positive: the backlog grows), DrainEtaMinutes (only when it drains), SendQueueMB, AvgRedoRateKBs / MinRedoRateKBs / MaxRedoRateKBs, DispatcherCpuPct, HelperCount, AvgHelperCpuPct, MaxHelperCpuPct, Verdict, Evidence, Recommendation, InstanceTopWaits (instance wide, not per database), Samples.

Examples

Example 1, Diagnose the local secondary

Get-sqmAgRedoDiagnosis -SqlInstance "SQL02"

Example 2, Longer sample, worst database with the numbers behind the verdict

Get-sqmAgRedoDiagnosis -SqlInstance "SQL02" -SampleSeconds 300 | Select-Object -First 1 | Format-List DatabaseName, RedoQueueMB, TrendMBPerMin, Verdict, Evidence, Recommendation

⬆ Index

All Commands

📄 Related

Get-sqmAlwaysOnQueueStatus