SQL Server Performance

Performance problems updating Subscribers

Discussion in 'SQL Server 2005 Replication' started by matto, Jan 4, 2007.

  1. matto New Member

    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!
  2. matto New Member

    Just to clarify, the identity column does not have to be in the primary key.

Share This Page