How to configure and troubleshoot the SubscriptionStreams parameter of the Distribution Agent in SQL Server 2005

In a transactional replication in Microsoft SQL Server 2005, you can use the SubscriptionStreams parameter to enable multiple connections that the Distribution Agent uses to apply batches of changes in parallel to the subscriber. At the same time, the Distribution Agent can still maintain many of the same transactional characteristics as when the Distribution Agent uses a single connection to apply the changes.

Note In the following sections, a session refers to a connection that the Distribution Agent opens to the instance of SQL Server at the subscriber.

This article describes the following topics:

• How to troubleshoot the problem when the Distribution Agent switches to use only one session.
• How to determine an efficient value for the SubscriptionStreams parameter.

The behavior of the Distribution Agent after you specify the SubscriptionStreams parameter

The Distribution Agent maintains the number of sessions that you specify in the SubscriptionStreams parameter. The Distribution Agent uses these sessions to apply changes on the subscriber.

However, after you specify the SubscriptionStreams parameter and the Distribution Agent runs for some time, the Distribution Agent may switch to use only one session to apply changes to the subscriber.

Reasons for the Distribution Agent to switch to use only one session

The Distribution Agent may switch to use only one session for many reasons. The following are the most common reasons:

• When the Distribution Agent is applying changes, one of the sessions raises an error.
  
  For example, the Distribution Agent inserts a row into a child table by using one session. If this occurs before the Distribution Agent inserts the corresponding row into the parent table by using another session, a foreign key constraint violation raises an error message.
• The blocking monitor thread detects blocking. Blocking may occur for one of the following reasons:
  • The Distribution Agent performs an INSERT operation and an UPDATE operation on a table at the subscriber by using different sessions. If the table contains a unique nonclustered index, blocking between the two sessions may occur when the Distribution Agent updates the index keys of the table.
  • At the subscriber, the Distribution Agent runs DML statements on multiple tables. If an indexed view is defined on these tables, blocking between the two sessions may occur when the indexed view updates the shared index keys.
  • The Distribution Agent runs a DML statement against a table at the subscriber by using one session. DML triggers are defined on this table. The DML triggers run DML statements on another table that is being updated by using another session. In this situation, blocking between the two sessions may occur.

We highly recommend that you do not use the following database objects at the subscriber database:

• Foreign key constraints
• Unique nonclustered indexes
• Indexed views
• DML triggers that can cause blocking between sessions

How to determine whether the Distribution Agent has switched to use only one session

To do this, use one of the following methods.

Note Although you can confirm that the Distribution Agent has not switched to use one session by using method 1, you must use method 2 or method 3 to confirm that the Distribution Agent has switched to use one session.

Method 1

Query the sys.dm_exec_sessions Dynamic Management View (DMV) for the connection sessions to the subscription database. If you see only one connection session, the Distribution Agent may have switched to use one session. If you see more than one connection session, the Distribution Agent is still using the specified number of sessions.

To confirm that the Distribution Agent has switched to use one session, use method 2 or method 3.

Method 2

Query the comments column of the msdistribution_history table in the distribution database. If the result of the query contains the following entry, the Distribution Agent has switched to use one session:

Method 3

Examine the output file of the Distribution Agent. The Distribution Agent has switched to use only one session if the output file contains the same error message as Method 2.

The following output file is an example:

How to troubleshoot a Distribution Agent that switches to use only one session

1. Run the SQL Server Profiler on the subscriber to capture the Blocked process report event and the Exception event. These events record blocking and errors that occur when the Distribution Agent applies changes.
   
   Note The Exception event can be caused by any kind of error that may be associated with the problem. For example, the error may be caused by a foreign key constraint violation.
2. Use one of the methods in the "How to determine whether the Distribution Agent has switched to use only one session" section to monitor the Distribution Agent.
3. If the Distribution Agent has switched to use one session, stop the trace.
4. From the output file of the Distribution Agent or from the start_time column of the msdistribution_history table, obtain the time stamp of the following entry:
   The process failed to complete last batch in multi-streaming mode, it has been reset to single connection mode and is retrying the operation.
5. Open the trace (.trc) file from the subscriber. Locate a blocking script or an exception event whose time stamp is the same as or very close to the time stamp that you obtained in step 4.
6. If you notice an exception, examine the details of the exception to determine the cause. For example, the exception may be caused by a foreign key constraint violation. If this is the case, we recommend that you remove the foreign key constraint in the subscriber database.
   
   If you notice a blocking script, the problem is caused by blocking. The following is a sample blocking script:

   <blocked-process-report monitorLoop="41589">
    <blocked-process>
     <process id="process3a6d438" taskpriority="0" logused="24592" waitresource="KEY: 6:72057594375700480 (0100e420fa5a)" waittime="9937" ownerId="568644832" transactionname="user_transaction" lasttranstarted="2008-05-05T04:55:04.430" XDES="0xa5619e370" lockMode="X" schedulerid="11" kpid="6104" status="suspended" spid="58" sbid="0" ecid="0" priority="0" transcount="2" lastbatchstarted="2008-05-05T04:55:04.553" lastbatchcompleted="2008-05-05T04:55:04.430" clientapp=<DistributionAgentProgram> hostname=<servername> hostpid="3980" loginname=<SQLAgentAcct>  isolationlevel="read committed (2)" xactid="568644832" currentdb="6" lockTimeout="4294967295" clientoption1="671090784" clientoption2="128056">
      <executionStack>
       <frame line="5" stmtstart="642" stmtend="1600" sqlhandle="0x0300060057a14477a8c6dd00609a00000100000000000000"/>
      </executionStack>
      <inputbuf>
   Proc [Database Id = 6 Object Id = 2000986455]   </inputbuf>
     </process>
    </blocked-process>
    <blocking-process>
     <process status="sleeping" spid="68" sbid="0" ecid="0" priority="0" transcount="1" lastbatchstarted="2008-05-05T04:55:04.570" lastbatchcompleted="2008-05-05T04:55:05.103" clientapp=<DistributionAgentProgram> hostname=<servername> hostpid="3980" loginname=<SQLAgentAcct> isolationlevel="read committed (2)" xactid="568644998" currentdb="6" lockTimeout="4294967295" clientoption1="671090784" clientoption2="128056">
      <executionStack/>
      <inputbuf>
   Proc [Database Id = 6 Object Id = 1172459501]   </inputbuf>
     </process>
    </blocking-process>
   </blocked-process-report>
   

   The blocking script records a blocked session and a blocking session. The blocked session starts from the <blocked-process> tag. The blocking session starts from the <blocking-process> tag.
7. Locate the object ID of the Proc object in the blocked session and in the blocking session.
   
   In the sample blocking script, the object ID of the Proc object in the blocked session is 2000986455. The object ID of the Proc object in the blocking session is 1172459501.
8. In the subscription database, query the sys.objects view by specifying the object_id column to be equal to the object IDs that you obtained in step 7. When you do this, you can determine the object names.
   
   For example, run the following query in the context of the subscription database:

   USE <SubDBName>
   GO
   SELECT name FROM sys.objects
   WHERE object_id = 1172459501 OR object_id = 2000986455
   

   Notes
   • The <SubDBName> placeholder represents the name of the subscription database.
   • Usually, these objects are stored procedures that are used in the replication.
9. Determine the index or the indexed view that causes blocking. To do this, follow these steps:
   1. In the blocking script, locate the value of the waitresource property.
      
      In the sample blocking script, the value of the waitresource property is 72057594375700480.
   2. Query the sys.partitions view to obtain the object ID and the index ID by specifying the PARTITION_ID column to be equal to the value of the waitresource property that you obtained in step 9a.
      
      For example, run the following query:

      SELECT object_id, index_id FROM SYS.PARTITIONS WHERE PARTITION_ID=72057594375700480

   3. In the subscription database, query the sys.indexes view to determine the index by using the object ID and the index ID that you obtained in step 9b.
      
      For example, run the following query:

      USE <SubDBName>
      GO
      SELECT name, type_desc, is_unique FROM sys.indexes
      WHERE object_id = <objID> and index_id = <idxID>
      

      Note The <objID> placeholder represents the object ID that you obtained in step 9b. The <idxID> placeholder represents the index ID that you obtained in step 9b.
10. If blocking is caused by an indexed view, we recommend that you drop the indexed view. If blocking is caused by a unique nonclustered index, we recommend that you can drop the index, and then re-create a non-unique index.

Description of the blocking monitor thread

The Distribution Agent maintains a blocking monitor thread that detects blocking between the sessions. If the blocking monitor thread detects blocking between the sessions, the Distribution Agent switches to use one session to reapply the current batch of commands that the Distribution Agent could not apply previously.

For more information about the blocking monitor thread, click the following article number to view the article in the Microsoft Knowledge Base:

How the Distribution Agent resumes multiple sessions

Before the Distribution Agent can resume multiple sessions, the Distribution Agent must execute the sp_MSget_repl_commands stored procedure to requery the distribution database for the commands that have not been applied at the subscriber. Then, the Distribution Agent must apply all these commands at the subscriber before the Distribution Agent can resume multiple sessions. In a latent replication environment, the Distribution Agent cannot resume multiple sessions because the Distribution Agent must apply many commands at the subscriber before the Distribution Agent can resume multiple sessions.

To track the whole process, examine the output file of the Distribution Agent.

For more information about how to use the SubscriptionStreams parameter to improve disk subsystem throughput, click the following article number to view the article in the Microsoft Knowledge Base: