SQL Server Performance

Update over linked server connection ... CURSORUPDATE?

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by null, Oct 24, 2008.

  1. null New Member

    I'm performing a very simple update over a linked server connection. I'm setting a bit column in a table to 1, marking it as "replicated". I have a post replication job that runs on the Distributor that references these rows and deletes them on the Publisher. This scenario uses nightly snapshot replication, with the Distributor and Publisher being on different servers (hence the need for a linked server connection). Here's the update that's implemented in a job step that exists on the Distributor:
    update [publisherServerName].dbo.someTable set replicated = 1
    The update is performing very slowly when compared to running the update directly on the Publisher. sp_who2 on the Publisher shows that a CURSORUPDATE is being performed. Why is a cursor being implemented here ... does it seem usual for a cursor to be performed in cases like this? Would a better option be for the Distributor's job step to call a stored procedure on the Publisher? ... maybe w/the procedure and updates residing locally on the Publisher when they're performed it'll perform better??
    Any help is appreciated....thanks.
  2. satya Moderator

    What is the version of SQL & their service pack levels used here between the replication instances?
  3. null New Member

    Both servers are running Sql Server 2005 SP 2.
    Btw, I refactored the 2 jobs such that the remote call is issued on a stored procedure, vs. embedding the query directly in the job step. Per sp_who2, the operation changed to UPDATE vs. CURSORUPDATE. The job completed in a few minutes vs. a few hours. So the problem was solved, but if you have something to add I'm all ears.

Share This Page