Performance problems updating Subscribers | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Performance problems updating Subscribers

Since we’ve upgraded to 2005 we’ve had major performance problems with updating subscriber tables that contain identity PK columns set to manual or auto range management. If a table has identity range management set to ‘None’ (the "old" 2000 way), the peformance is fine. Below are sample tables: CREATE TABLE [dbo].[ID_RANGE_TEST](
[ID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[DATA] [int] NULL
CONSTRAINT [PK_ID_RANGE_TEST] PRIMARY KEY CLUSTERED
( [ID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[ID_NONE_TEST](
[ID] [int] IDENTITY(1,1) NOT NULL,
[DATA] [int] NULL
CONSTRAINT [PK_ID_NONE_TEST] PRIMARY KEY CLUSTERED
( [ID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
Populate each table with 1000 rows. Add ID_RANGE_TEST to transactional replication w/updatable subscribers using manual identity range management. Add ID_NONE_TEST to the publication but with ‘none’ as the identity range management. Since you can’t do this using the UI, you have to run the stored proc: exec sp_addarticle @publication = N’MY_PUBLICATION’, @article = N’ID_NONE_TEST’, @source_owner = N’dbo’, @source_object = N’ID_NONE_TEST’, @type = N’logbased’, @description = N”, @creation_script = N”, @pre_creation_cmd = N’drop’, @schema_option = 0x000000000000CFFF, @identityrangemanagementoption = N’none’, @destination_table = N’ID_NONE_TEST’, @destination_owner = N’dbo’, @status = 24, @vertical_partition = N’false’, @ins_cmd = N’CALL [sp_MSins_dboID_NONE_TEST]’, @del_cmd = N’VCALL [sp_MSdel_dboID_NONE_TEST]’, @upd_cmd = N’VCALL [sp_MSupd_dboID_NONE_TEST]’ Finally, run these updates on the subscriber and note the performance difference. update ID_NONE_TEST set data = 1 –takes almost no time update ID_RANGE_TEST set data = 1 –takes about 30 times longer This kills us when we update large numbers of rows. Has anyone run into this or can help explain?
Thanks!
Just to clarify, the identity column does not have to be in the primary key.
]]>