Initializing a subscription is primarily a snapshot generated by the Distribution Agent before the Snapshot Agent is distributed and applied, so the optimized body is the Distribution Agent.
1. Initializing subscriptions
- First create a local subscription, the Publisher, Distributor, and Subscriber are on the same server, just to test the production environment do not do this
- Distribution Server Parameter configuration
- Initialize subscription run time
Several points to note:
- The Snapshot Agent actually executes a BCP program that generates a snapshot file:
- The Snapshot Agent uses locks during the initial phase of snapshot generation, albeit with a short time, but with a large range of effects.
- You cannot make changes to the structure of a table during snapshot generation.
2. Optimized parameter testing
- Create a new Distribution Agent configuration file named "Distribution Agent tuning Parameters"
- Find the profile_id for this agent configuration file
SELECT * from WHERE = 3 EXEC @agent_type = 3
- Modifying a configuration file
EXECSp_change_agent_parameter@profile_id = -, @parameter_name = 'MaxBCPThreads',@parameter_value = 4 EXECSp_change_agent_parameter@profile_id = -, @parameter_name = 'BcpBatchSize',@parameter_value = 100000 EXECSp_change_agent_parameter@profile_id = -, @parameter_name = 'PollingInterval',@parameter_value = 500000
- after optimization Subscription Library Generation The test results show no significant optimization from the test results. MaxBCPThreads does not implement multi-threaded data import, background query only one session in the execution of bulk in operation, bcpbatchsize on the parameters of small, the proposed setting is larger; the other two parameters do not affect the efficiency of the subscription database generation. In fact, subscription library generation is the entire process of performance bottlenecks, if you can use multithreading or a good solution, I hope to see the blog students have a good way to provide.
SQL Server improves transactional replication efficiency Optimization (iii) subscription initialization optimization
