Post reply

Curious Locking

  • March 10, 2005 at 9:31 am

    #87805

    Hi Guys,

    I wonder if you can help.

    I have recently upgraded two separate test environments with the same procedures, functions and indexes..pretty straightforward.

    However, on one system (A) a job runs overnight and takes 4 hours on the other (B) it takes 8-9 hrs .

    The servers are both of reasonable spec.

    Having investigated, there is a simple update procedure that uses a function. On system A this procedure runs with relatively few locks, on system B I see a whole list (a few hundred Key locks for the particular process). It is only updating 25000 rows ????

    Any thoughts on what could cause such a difference.

    Cheers..Graeme

  • March 10, 2005 at 10:07 am

    #546429

    Try use the MAX DOP option in your query and see if that helps. Could be it is causing a parallel execution and locking that way. Also check the execution plan of both.

  • March 11, 2005 at 1:29 am

    #546554

    Thanks for that..had a look.

    The execution plans were identical.

    I tried varying the indexes on this table. One index in particular was dropped and it cured the problem....the query now runs in about 5 minutes instead of an hour.

    Funny thing is the same indexes exists on a similar test system and that runs ok??

    Thanks again..Graeme

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply