SQL2005 Performance problem updating subscribers with identities
-
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!
Matto
-
This was removed by the editor as SPAM
-
Just to clarify, the identity column does not have to be in the primary key.
Matto
-
I sent your example to someone recognized as a SAGE when it comes to replication. I am interested as I am in the middle of a project where surrogate keys are used rather than "normal" value keys. So I'll let you know if the SAGE has a response. Of course statistics have been updated prior to or on a regular basis! It seems everytime I have a performance problem those statistics have been a major factor. Just shooting in the dark until the SAGE speaks.
---------------------------------------
The glass is always half empty and never half full!
-
Here is the SAGE's response:
Put your subscriber database in the snapshot isolation mode. This
should improve performance dramatically.
---------------------------------------
The glass is always half empty and never half full!
-
Thanks for the response. I tried the following on the subscriber and received an error:
alter
database my_db set allow_snapshot_isolation on
set
transaction isolation level snapshot
update
id_range_test set DATA =1
Msg 3996, Level 16, State 1, Procedure trg_MSsync_upd_ID_RANGE_TEST, Line 105
Snapshot isolation level is not supported for distributed transaction. Use another isolation level or do not use distributed transaction.
Matto
-
Can you post the query plan for the slow update?
-
The sage has asked: "Where is the performance problem? On the
subscriber, or on one of two subscribers?"
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
---------------------------------------------------------
The glass is always half empty rather than half full!
-
Herb, are you his/her agent?
-
Andy:
No, not any form of agent or business connection. The SAGE (I call him) helped me a few months back. I asked a PASS member and was directed to him. I asked him if he might know because I am in the process of developing a database supporting a new application. I have gone the surrogate key route using identity generated values. I noticed the question's originator made reference to the use of identity values and wanted to know if I too might have problems down the road. I put in the reference link since it has helped me in this area. If I violated a rule by doing this, please let me know and I'll edit it out.
---------------------------------------------------
The glass is always half empty rather than half full!
-
Was a tongue in check question:-) I dont see any issue with the link, think its well within the bounds of what the community would consider ok. The comment was really referring that I'd rather see the sage post directly, but certainly nothing wrong with you attributing items you've learned from someone else.
-
Please relay this message to the mighty sage...
The update trigger on the subscriber (trg_MSsync_upd_ID_RANGE_TEST) calls the stored procedure sp_MSsync_upd_ID_RANGE_TEST_1 that performs the update on the publisher. The slowness seems to be in that proc on the publisher. While I was experimenting I put a RETURN at the beginning of the sproc and it made no peformance difference - it was still slow. However, if I removed all the code from the sproc it was fast (although obviously it hoses the data). That made me think it was some sort of weird recompile issue. We've seen that sort of behavior with user defined functions.
I actually have an open call with MS on this and they have reproduced it and are determining if this is a design issue.
Matto
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply