Subscription latency

Microsoft.SQLServer.Replication.Windows.Monitor.SubscriptionLongEstimatedTimeMonitor (UnitMonitor)

This monitor checks the latency for commands in the Distribution database waiting to be delivered to the Subscribers. Note that this monitor is disabled for Merge Subscriptions.

Knowledge Base article:

Summary

This monitor collects information similar to the replication monitor that displays pending commands in the undistributed commands tab along with the estimated time to deliver those commands. If the estimated time is longer than in the time of threshold period, an alert is fired to display information. Note that this monitor is disabled for Merge Subscriptions.

http://msdn.microsoft.com/library/ms183374.aspx

Causes

The Distribution Agent may be stopped.

There may be large amounts of pending transactions with many commands committed on the Publisher database and replicated to the Distribution database.

The Distribution Agent may be experiencing performance issues reading from Distribution database or writing to Subscriber database.

Resolutions

If '-MaxDeliveredTransactions' and '-Continuous' are both specified, the Distribution Agent delivers the specified number of transactions and then stops (even though '-Continuous' is specified). You must restart the Distribution Agent after the job is complete.

If the alert is due to a large transactions volume in the Publisher database, evaluate the estimated time of delivery. Consider re-initializing the subscription if the estimated delivery time is longer than creating and applying a snapshot.

If the Distribution Agent is experiencing performance issues reading the Distribution database, run update statistics with the fullscan option against the Distribution tables.

If the Distribution Agent is experiencing performance issues writing to the Subscriber, check and address any blockings or resources waits on the Subscriber database.

Additional

Test if changing the below parameter values can help the Distribution Agent performance:

CommitBatchSize

The number of transactions to be issued to the Subscriber before a COMMIT statement is issued. The default number of transactions is 100.

QueryTimeOut

The number of seconds before the query times out. The default number is 1800.

SubscriptionStreams

The number of connections allowed per Distribution Agent to apply batches of changes in parallel to a Subscriber. For a SQL Server Publisher, a range of values from 1 to 64 is supported.

External

https://msdn.microsoft.com/library/ms173804.aspx

Overrideable Parameters

Name

Description

Default Value

Alert Priority

Defines Alert Priority.

Normal

Alert Severity

Defines Alert Severity.

Warning

Enabled

Enables or disables the workflow.

Yes

Generates Alerts

Defines whether the workflow generates an Alert.

Yes

Interval (seconds)

The recurring interval of time in seconds in which to run the workflow.

300

Synchronization Time

The synchronization time specified by using a 24-hour format. May be omitted.

 

Warning Threshold

Warning Threshold.

60

Timeout (seconds)

Specifies the time the workflow is allowed to run before being closed and marked as failed.

200

Timeout for database connection (seconds)

The workflow will fail and register an event if it cannot access the database during the specified period.

15

Element properties:

TargetMicrosoft.SQLServer.Replication.Windows.Subscription
Parent MonitorSystem.Health.PerformanceState
CategoryPerformanceHealth
EnabledTrue
Alert GenerateTrue
Alert SeverityWarning
Alert PriorityNormal
Alert Auto ResolveTrue
Monitor TypeMicrosoft.SQLServer.Replication.Windows.MonitorType.SubscriptionLongEstimatedTime
RemotableTrue
AccessibilityPublic
Alert Message
MSSQL on Windows Replication: High Latency for Commands in the Distribution Database Detected for Subscription.
The Subscription '{0}' has detected a long estimated time for pending commands waiting to be applied.
Estimated time: {1} second(s)
RunAsMicrosoft.SQLServer.Core.RunAs.Monitoring

Source Code:

<UnitMonitor ID="Microsoft.SQLServer.Replication.Windows.Monitor.SubscriptionLongEstimatedTimeMonitor" Accessibility="Public" Enabled="true" Target="SQLReplWD!Microsoft.SQLServer.Replication.Windows.Subscription" ParentMonitorID="Health!System.Health.PerformanceState" Remotable="true" Priority="Normal" TypeID="Microsoft.SQLServer.Replication.Windows.MonitorType.SubscriptionLongEstimatedTime" ConfirmDelivery="false" RunAs="SqlCoreLib!Microsoft.SQLServer.Core.RunAs.Monitoring">
<Category>PerformanceHealth</Category>
<AlertSettings AlertMessage="Microsoft.SQLServer.Replication.Windows.Monitor.SubscriptionLongEstimatedTime.AlertMessage">
<AlertOnState>Warning</AlertOnState>
<AutoResolve>true</AutoResolve>
<AlertPriority>Normal</AlertPriority>
<AlertSeverity>Warning</AlertSeverity>
<AlertParameters>
<AlertParameter1>$Target/Property[Type='MSRL!Microsoft.SQLServer.Replication.Core.GenericSubscription']/SubscriptionName$</AlertParameter1>
<AlertParameter2>$Data/Context/Property[@Name='EstimatedTime']$</AlertParameter2>
</AlertParameters>
</AlertSettings>
<OperationalStates>
<OperationalState ID="Health" MonitorTypeStateID="Health" HealthState="Success"/>
<OperationalState ID="Warning" MonitorTypeStateID="Warning" HealthState="Warning"/>
</OperationalStates>
<Configuration>
<MachineName>$Target/Host/Host/Property[Type='SqlCoreLib!Microsoft.SQLServer.Core.DBEngine']/MachineName$</MachineName>
<NetbiosComputerName>$Target/Host/Host/Property[Type='SqlCoreLib!Microsoft.SQLServer.Core.DBEngine']/NetbiosComputerName$</NetbiosComputerName>
<InstanceName>$Target/Host/Host/Property[Type='SqlCoreLib!Microsoft.SQLServer.Core.DBEngine']/InstanceName$</InstanceName>
<SqlTimeoutSeconds>15</SqlTimeoutSeconds>
<ConnectionString>$Target/Property[Type="MSRL!Microsoft.SQLServer.Replication.Core.GenericSubscription"]/Distributor$</ConnectionString>
<MonitoringType>$Target/Host/Host/Property[Type="SqlDiscW!Microsoft.SQLServer.Windows.DBEngine"]/MonitoringType$</MonitoringType>
<SubscriptionType>$Target/Property[Type="MSRL!Microsoft.SQLServer.Replication.Core.GenericSubscription"]/Type$</SubscriptionType>
<PublisherName>$Target/Property[Type="MSRL!Microsoft.SQLServer.Replication.Core.GenericSubscription"]/Publisher$</PublisherName>
<Subscriber>$Target/Property[Type="MSRL!Microsoft.SQLServer.Replication.Core.GenericSubscription"]/Subscriber$</Subscriber>
<PublisherDb>$Target/Property[Type="MSRL!Microsoft.SQLServer.Replication.Core.GenericSubscription"]/PublicationDatabase$</PublisherDb>
<SubscriberDb>$Target/Property[Type="MSRL!Microsoft.SQLServer.Replication.Core.GenericSubscription"]/SubscriptionDatabase$</SubscriberDb>
<Publication>$Target/Property[Type="MSRL!Microsoft.SQLServer.Replication.Core.GenericSubscription"]/PublicationName$</Publication>
<ReplicationType>$Target/Property[Type="MSRL!Microsoft.SQLServer.Replication.Core.GenericSubscription"]/ReplicationType$</ReplicationType>
<EstimatedTimeSecondsThreshold>60</EstimatedTimeSecondsThreshold>
<IntervalSeconds>300</IntervalSeconds>
<SyncTime/>
<TimeoutSeconds>200</TimeoutSeconds>
<CheckMachineAndInstanceName>false</CheckMachineAndInstanceName>
</Configuration>
</UnitMonitor>