Performance Tuning SQL Server Snapshot Replication

When snapshots are performed, try to schedule them during less busy times of the day. A snapshot not only has to perform a SELECT on the entire table being replicated, but it also has to move the selected data off the publishing server onto the distribution server, which can produce a lot of I/O overhead. On the subscribing servers, each one has to receive the data and then insert it into a local table, also causing great I/O overhead. [6.5, 7.0, 2000, 2005] Updated 3-6-2006

*****

If your replication servers are very busy, consider locating the snapshot folder on a disk array that is not being used to store database or logs files. This helps to reduce I/O contention, boosting overall SQL Server and replication performance. You may also want to consider using a single snapshot folder per publication for even better performance. [6.5, 7.0, 2000, 2005] Updated 3-6-2006

*****

In SQL Server, if your snapshot files are large, or if they have to transverse a slow LAN or WAN, consider compressing the files. Compressing snapshot files reduces the size of the physical data, greatly speeding its movement over network connections. On the other hand, these files must first be compressed, then decompressed, which adds to server overhead. Sometimes the trade-off between using and not using compressed snapshot files is hard to determine. You should consider testing this option before putting it into production. [2000, 2005] Updated 3-6-2006

*****

To speed the initial snapshot taken for merge, transactional, or snapshot replication in SQL Server, consider setting the UseInprocLoader property. When you set this option (it is not a default setting), agents will use the BULK INSERT command to create the snapshot files, which is much faster than the default method. For this to work, it cannot be combined with character mode BCP, it cannot be used by ODBC or OLE DB subscribers, and the user account used by the subscriber must have read permissions on the folder where the snapshot files are located.

To turn on this property, right-click on the agent you want to configure and select "Agent Properties." Then from the "Steps" tab, double-click the agent step and then add "-UseInprocLoader" to the "Command" text box. [2000] Updated 3-6-2006

*****

To speed the initial snapshot taken for merge, transactional, or snapshot replication in SQL Server 7.0 and 2000, consider changing the MaxBCPThreads property. This parameter tells SQL Server the number of bulk copy operations that it can perform in parallel. By default, this is set to 1. By increasing this value, bulk copy operations will run faster, and the initial snapshot will be performed faster.

As a rule of thumb, try a value equal to the number of articles you are publishing. Only one thread will be assigned per article, so there is no benefit of using a value that exceeds the number of articles you are publishing. You may want to experiment with different values to see which one works best for your particular configuration. If you set this value too high, performance might actually decrease because SQL Server will have to spend extra time managing the extra threads, outweighing any benefits provided by the extra threads. [7.0 2000] Updated 3-6-2006

*****

The snapshot folder can be located on either the publisher or the distributor. There are performance pros and cons to selecting either option. If the snapshot folder is located on the distributor, the publisher only has to make one copy to the distributor, and then the distributor is responsible for copying the snapshot to the various subscribers that need it. This helps to reduce overhead on the publisher.

On the other hand, locating the snapshot folder on the publisher reduces network traffic when the snapshot is originally created, but increases the overhead on the publisher when multiple subscribers request a snapshot. In addition, the snapshot will occur faster if located on the publisher because it will reduce the time that locks are held on the database when the snapshot is made.

So which option is best? Generally, and your particular situation may be different, locating the snapshot folder on the distributor provides the overall best performance. [7.0, 2000, 2005] Added 12-26-2001

*****

In SQL Server, when configuring publication properties, you have the option to create snapshot files in the default snapshot folder, an alternate folder, or both. For best performance, do not choose both. If you do, SQL Server will have to work harder when creating the two snapshot files, which will increase overhead and hurt performance. [2000, 2005] Added 10-9-2001

*****

If the only type of replication you will be using is snapshot replication, and assuming the server acting as the publisher is not already experiencing bottlenecks, consider locating both the publisher and the distributor on the same physical server, instead of on separate servers, as is generally recommended.

Snapshot replication can actually be boosted if both the publisher and the distributor are on the same physical server. This is because the distributor plays only a very small role in snapshot replication, and because of this, incurs very little overhead.

By locating the publisher and distributor on the same server, there is no network traffic as the snapshot is taken and written to the snapshot folder. And because there is no network traffic, locks are held for less time on the replicated database, helping to increase concurrently and overall performance of the database.

But, if you are doing other types of replication, or if the publisher is already very busy, then you probably should separate the publisher from the distributor. [7.0, 2000, 2005] Added 12-26-2001

*****

In SQL Server 7.0, when a snapshot is generated, SQL Server puts shared locks on all of the tables that are being published for replication. As you can imagine, this can affect users who are trying to update records in the locked tables. Because of this, you may want to schedule snapshots to be created during less busy times of the day. This is especially true if there are many tables, or if the tables are very large.

In SQL Server 2000, this behavior changed. Assuming that all subscribers will be SQL Server 7.0 or 2000 servers, then SQL Server 2000 will use what is called concurrent snapshot processing, which does not put a shared lock on the affected tables, helping to boost concurrency. [2000] Added 10-9-2001