Home
  •  

Subscription latency

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

This monitor checks the latency for commands in the Distribution database waiting to be delivered to the Subscribers.

Knowledge Base article:

Summary

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

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

If the alert is due to large transactions volume in 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 is 100.

QueryTimeOut

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

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

Synchronization Time

 

Threshold

Threshold

60

Timeout (seconds)

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

300

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.2012.Replication.Subscription
Parent MonitorSystem.Health.PerformanceState
CategoryPerformanceHealth
EnabledTrue
Alert GenerateTrue
Alert SeverityWarning
Alert PriorityNormal
Alert Auto ResolveTrue
Monitor TypeMicrosoft.SQLServer.2012.Replication.MonitorType.SubscriptionLongEstimatedTime
RemotableTrue
AccessibilityPublic
Alert Message
MSSQL2012 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}s
RunAsMicrosoft.SQLServer.Replication.Monitoring.RunAs.Monitor

Source Code:

<UnitMonitor ID="Microsoft.SQLServer.2012.Replication.Monitor.SubscriptionLongEstimatedTimeMonitor" Accessibility="Public" Enabled="true" Target="MS2RD!Microsoft.SQLServer.2012.Replication.Subscription" ParentMonitorID="Health!System.Health.PerformanceState" Remotable="true" Priority="Normal" TypeID="Microsoft.SQLServer.2012.Replication.MonitorType.SubscriptionLongEstimatedTime" ConfirmDelivery="false" RunAs="MSRL!Microsoft.SQLServer.Replication.Monitoring.RunAs.Monitor">

  <Category>PerformanceHealth</Category>

  <AlertSettings AlertMessage="Microsoft.SQLServer.2012.Replication.Monitor.SubscriptionLongEstimatedTime.AlertMessage">

    <AlertOnState>Warning</AlertOnState>

    <AutoResolve>true</AutoResolve>

    <AlertPriority>Normal</AlertPriority>

    <AlertSeverity>Warning</AlertSeverity>

    <AlertParameters>

      <AlertParameter1>$Target/Property[Type='MSRL!Microsoft.SQLServer.Replication.Library.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>

    <SqlTimeout>15</SqlTimeout>

    <ConnectionString>$Target/Property[Type="MSRL!Microsoft.SQLServer.Replication.Library.GenericSubscription"]/Distributor$</ConnectionString>

    <SubscriptionType>$Target/Property[Type="MSRL!Microsoft.SQLServer.Replication.Library.GenericSubscription"]/Type$</SubscriptionType>

    <PublisherName>$Target/Property[Type="MSRL!Microsoft.SQLServer.Replication.Library.GenericSubscription"]/Publisher$</PublisherName>

    <Subscriber>$Target/Property[Type="MSRL!Microsoft.SQLServer.Replication.Library.GenericSubscription"]/Subscriber$</Subscriber>

    <PublisherDb>$Target/Property[Type="MSRL!Microsoft.SQLServer.Replication.Library.GenericSubscription"]/PublicationDatabase$</PublisherDb>

    <SubscriberDb>$Target/Property[Type="MSRL!Microsoft.SQLServer.Replication.Library.GenericSubscription"]/SubscriptionDatabase$</SubscriberDb>

    <Publication>$Target/Property[Type="MSRL!Microsoft.SQLServer.Replication.Library.GenericSubscription"]/PublicationName$</Publication>

    <ReplicationType>$Target/Property[Type="MSRL!Microsoft.SQLServer.Replication.Library.GenericSubscription"]/ReplicationType$</ReplicationType>

    <EstimatedTimeSecondsThreshold>60</EstimatedTimeSecondsThreshold>

    <IntervalSeconds>300</IntervalSeconds>

    <SyncTime/>

    <TimeoutSeconds>300</TimeoutSeconds>

  </Configuration>

</UnitMonitor>