|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-01-13 : 17:17:11
|
| Here is what I got from Microsoft Technet(SQL 2000):Factors Affecting Transactional Delivery RatesIn most cases, the Subscriber is the bottleneck, because data cannot be written or applied quickly enough. Factors include the following:• Subscriber's physical computer.• Slower processor and/or lower number of processors. • Low processor availability and/or high processor load. • Low amount of available memory. • Slower disk subsystem. • Subscription database or SQL Server setup.• Database log not on a separate disk. • Database on RAID 5 disk (RAID 10 provides better performance). • SQL Server memory available, and whether it is dynamic or fixed: The amount of memory that is appropriate and whether it should be fixed or dynamically allocated depends on your application. • SQL Server protocol used: TPC/IP is generally slightly faster than other network protocols. • SQL Server Personal Edition, which is generally slower, used. • Windows 98 or Windows Millennium, which are generally slower, used. • Network speed or connection• The Subscriber can become I/O bound if using a very fast network (100 MB or faster) and the Subscriber has a slower disk subsystem or the log is not on a separate disk. • Reliability of the connection: more retries may be necessary if the connection is unreliable. • Different indexes exist on the Subscriber. Often a reporting server is heavily indexed, and index management results in more I/O. Using the CUSTOMER table shown earlier, the average latency increases to 4 seconds (with a maximum of 6 seconds) when four nonclustered indexes are added at the Subscriber on [Lname, Fname], [DOB], [Email], and [Tel]. • User triggers firing at the Subscriber. Subscriber triggers not marked NOT FOR REPLICATION are fired for each relevant operation. As triggers frequently use the inserted and/or deleted tables and often perform other operations, the costs can be dramatic. By moving the trigger code into a custom stored procedure, some of the costs can be avoided. Using the earlier CUSTOMER example: it takes 88 seconds, or 1,140 commands per second, for the Distribution Agent to deliver 100,000 insert commands to the Subscriber. The Subscriber has the following insert trigger defined:CREATE TRIGGER CUSTOMER_INS_TRG ON CUSTOMER FOR INSERT AS INSERT INTO BADRATINGS (Id, Cust_Id, Rating, Rating_Dt) SELECT NewId(), Cust_Id, Rating, GetDate() FROM inserted WHERE (Cust_Id % 3) = 0 Then the trigger is dropped and the relevant code is added to the autogenerated insert stored procedure, which is called by the Distribution Agent:CREATE PROCEDURE sp_MSins_CUSTOMER......IF((@c1 % 3) = 0)INSERT INTO BADRATINGS (Id, Cust_Id, Rating, Rating_Dt)SELECT NewId(), @c1, @c9, GetDate() It now takes only 52 seconds (1,932 commands per second) for the Distribution Agent to deliver 100,000 commands to the Subscriber. This is 1.7 times faster than using triggers, dramatically affecting latency and throughput.If user triggers are still required (to trap local data changes made by users, for example), they should be marked as NOT FOR REPLICATION. The triggers then fire only when local data changes are made by users.• Replicating stored procedure execution. SQL Server can replicate the execution of stored procedures rather than the data changes caused by the execution of those stored procedures. This is useful in replicating the results of maintenance-oriented stored procedures that may affect large amounts of data. Replicating the changes as one stored procedure statement can greatly increase the efficiency of your application, but this feature should be used with care.Each time a published stored procedure is executed at the Publisher, the execution and the parameters passed to it for execution are forwarded to each Subscriber to the Publication. The stored procedure is then executed with these parameters at the Subscriber. This is vastly different from the Log Reader Agent picking up the changes in the log (for possibly thousands of rows), building the SQL statements for each and then having them applied to the Subscriber.Using the CUSTOMER table example (with an existing 100,000 rows) earlier in this paper, the following stored procedure was executed at the Publisher:CREATE PROCEDURE PROC_CUSTOMER_ADMIN_RATING @DOB smalldatetime AS UPDATE CUSTOMER SET Rating = Rating + 1 WHERE DOB < @DOBExecuting EXEC PROC_CUSTOMER_ADMIN_RATING '1966-01-01' resulted in 59,972 rows being updated, picked up by the Log Reader Agent and written to the distribution database. The Distribution Agent then applies 59,972 updates to the Subscriber, which takes one minute and 51 seconds to complete. In contrast, when replicating the execution of the stored procedure, only the actual EXEC statement is written to the distribution database and is then executed at the subscribing database. This takes only 1.7 seconds. Therefore, replicating stored procedure execution both reduces the volume of commands requiring forwarding to Subscribers and increases the performance of your application by executing fewer dynamic SQL statements at each Subscriber. |
 |
|
Replication Question.