Some tips for using Snapshot Replication in SQL Server 2016
Avoid publishing unnecessary data.
Try to restrict the amount of published data. This can results in good performance benefits,
because SQL Server will publish only the amount of data required. This can reduce network
traffic and boost the overall replication performance.
Do not configure the Distribution Agent to run continuously.
Try to schedule the Distribution Agent to run at regular intervals instead of running
continuously. By using it, you can decrease the total SQL Server overhead.
Consider specifying a simple or bulk-logged recovery model for the subscription
database.
Specifying a simple or bulk-logged recovery model for the subscription database allows minimal
logging of the bulk inserts performed during the application of the snapshot at the Subscriber.
Use native mode snapshots instead of the character mode snapshots.
Only non-SQL Server subscribers and subscribers running SQL Server Mobile Edition require a
character mode snapshot. Because native mode snapshots are generally faster than character
mode you should use native mode snapshots whenever possible. For example, the Distribution
Agent can use the BULK INSERT command when applying snapshot files to the Subscriber only
with the native mode snapshots.
Consider placing snapshot folder on a disk that is not used to store database
or log files.
Placing the snapshot folder on a disk that is not used to store database or log files
reduces contention among the disks and increases the snapshot process performance.
Run the Snapshot Agent as infrequently as possible.
The Snapshot Agent bulk copies data from the Publisher to the Distributor, which results
in some performance degradation. So, try to schedule it during CPU idle time and slow
production periods.
Include a publication database in AlwaysOn Availability Group.
In snapshot replication, a publication database can be part of an availability group. In this
case, the publisher instances must share a common distributor.
Specify the “min server memory” option.
This option is used to set a minimum amount of memory allocated to SQL Server. You can set
this option when SQL Server works on the same computer with other applications. In this case,
the “min server memory” option is used to allow SQL Server works when other applications
pretend to use all available memory.
Specify the “max server memory” option.
This option is used to set a maximum amount of memory allocated to SQL Server. You can specify
this option to avoid having the operating system page to disc for memory. You can also set this
option when SQL Server works on the same computer with other applications. In this case, the
“max server memory” option is used to prevent SQL Server to use all memory available.
Place the distribution component of replication on its own dedicated server.
This topology is used for performance reasons when the level of replication activity increases
or the server resources become constrained. It reduces Publisher loading, but it increases
overall network traffic. This topology requires separate Microsoft SQL Server installations,
one for the Publisher and one for the Distributor.
Consider using the sp_redirect_publisher, sp_get_redirected_publisher,
sp_validate_redirected_publisher and sp_validate_replica_hosts_as_publishers
stored procedures.
These stored procedures provide replication support for AlwaysOn Availability Groups.
So, you can use them to get advantages from the AlwaysOn Availability Groups.
Do not configure the distribution database to expand or shrink automatically.
Microsoft recommends setting a fixed size for the distribution database. Setting a database
to automatically grow results in some performance degradation, therefore you should set a
reasonable initial size of the distribution database.
Place the published database log and distribution database log on the separate
disk drives.
Because logging is more write-intensive, it is important that the disk arrays containing
the SQL Server log files have sufficient disk I/O performance.
Consider using snapshot replication to memory-optimized tables.
In SQL Server 2016, tables acting as snapshot replication subscribers can be configured
as memory-optimized tables. To configure the subscriber database for supporting replication
to memory-optimized tables, you should set the @memory_optimized property to true by using
sp_addsubscription or sp_changesubscription system stored procedure.
You can minimize the performance affect of history logging by selecting 1 for the
HistoryVerboseLevel property of the Distribution Agent and the Snapshot Agent.
This property specifies the amount of history logged during distribution operation (for a
Distribution Agent) or during a snapshot operation (for a Snapshot Agent).
Consider compressing the snapshot files.
If you have a central publisher with remote distributor topology (when the distribution
component of replication resides on its own dedicated server) and Publisher connected
with the Distributor over slow LAN or WAN, you can decrease network traffic by compressing
the snapshot files.
Create an index on each of the columns that is used in the filter’s WHERE clause.
If you do not use indexes on columns used in filters the SQL Server must perform a table
scan, which can results in performance degradation.
Consider using the -UseInprocLoader Distribution Agent parameter.
When this parameter is set the Distribution Agent uses the BULK INSERT command when applying
snapshot files to the Subscriber. This improves the performance of the initial snapshot, but
this parameter is not compatible with the XML data type and cannot be used with character mode
snapshots. If you do not replicate XML data and use native bcp mode snapshots, consider using
-UseInprocLoader Distribution Agent parameter.
Set the “Maximize Throughput for Network Applications” option.
This can increase SQL Server performance, because Windows will allocate more RAM to SQL Server
than to its file cache.
Consider setting the OutputVerboseLevel parameter of the Distribution Agent
and Snapshot Agent to 0.
This parameter specifies whether the output should be verbose. There are three available values:
0 – only error messages are printed
1 – all of the progress report messages are printed
2 – all error messages and progress report messages are printed
The default value is 2 for the Distribution Agent and 1 for the Snapshot Agent. You can increase
performance when only error messages are printed. So, during the testing period you can use the
default values of this parameter, but when you move your databases to production, consider
setting the OutputVerboseLevel parameter to 0.
Increase the MaxBcpThreads parameter of the Snapshot Agent.
This parameter specifies the number of bulk copy operations that can be performed in parallel.
If you increase the value of the MaxBcpThreads parameter, the bulk copy operations can run
faster, because they will be performed in parallel.
Note. Do not set this parameter too high, it can results in some performance degradation,
because SQL Server will have to spend extra time managing the extra threads. Increase this
parameter to 2 and continue monitoring.
Consider using snapshot replication to Azure SQL Database.
SQL Server 2016 introduces supporting snapshot and one-way transactional replication
to Azure SQL Database, peer-to-peer transactional replication and merge replication are
not supported.
Use only a single snapshot folder for each publication.
When you configure the publication properties, you have the option to create snapshot
files in the default snapshot folder, an alternate folder, or both. For best performance,
you should use a single snapshot folder, not both.