Reputation: 629
We've implemented transactional replication (push model) over a WAN and are sometimes seeing slow-downs during bulk updates of a specific table (ie. we are seeing a high number of 'commands not replicated' for that specific table). The table in question has the format
CREATE TABLE [Table](
[Id] [int] IDENTITY(1,1) NOT NULL,
[FrequentlyUpdated_0] [float] NULL,
[FrequentlyUpdated_1] [float] NULL,
[FrequentlyUpdated_2] [float] NULL,
[RarelyUpdated_0] [varbinary](max) NULL,
[RarelyUpdated_1] [varbinary](max) NULL,
[RarelyUpdated_2] [varbinary](max) NULL
)
where the RarelyUpdated_n columns can contain data that is quite large relative to the FrequentlyUpdated_n columns (eg. 20 MBs). The question now is whether it will likely improve performance if we split the table in question into two distinct tables like the following
CREATE TABLE [FrequentlyUpdatedTable](
[Id] [int] IDENTITY(1,1) NOT NULL,
[FrequentlyUpdated_0] [float] NULL,
[FrequentlyUpdated_1] [float] NULL,
[FrequentlyUpdated_2] [float] NULL
)
CREATE TABLE [RarelyUpdatedTable](
[Id] [int] IDENTITY(1,1) NOT NULL,
[RarelyUpdated_0] [varbinary](max) NULL,
[RarelyUpdated_1] [varbinary](max) NULL,
[RarelyUpdated_2] [varbinary](max) NULL
)
Or phrased in another way: Does performance depend on row data size or just the size of the updated data?
PS. All servers involved in the setup are not heavily loaded, so I suspect the performance issue is related to I/O.
Upvotes: 0
Views: 56
Reputation: 28860
Does performance depend on row data size or just the size of the updated data?
Transactional Replication works by reading the transaction log.when reading that log,it will try to filter out records of articles which are marked for replication and sends those in the form on INS/DEL/UPD commands to distributor
Dividing a table into two tables to reduce data size that will be transferred to distributor won't help,since when you update,SQL won't transfer entire row,but only the part which was changed as UPD command
You will have to troubleshoot ,the bottle neck in your replication topology first to have more understanding on the bottle neck..
there are many ways to identify bottle neck..
1.Tracer tokens: You can insert tracer tokens to monitor the flow
2.After identfying the latency,you can troubleshoot indvidual agents ,by logging their operations
How to enable replication agents for logging to output files in SQL Server
3.finally ,you also can tweak settings.Say for example,if you have identified log reader is grouping few transactions in one command and if you want to change that to test and improve latency.You can tweak settings here as well
Enhance Transactional Replication Performance
Upvotes: 1