CODEWITHSUNDEEP
sql-serversql-server-2012database-replicationtransactional-replication
Mads Ravn
Mads Ravn

Reputation: 629

Table schema affect on transactional replication performance

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

Answers (1)

TheGameiswar
TheGameiswar

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

enter image description here

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

Related Questions