SQL Server Performance

Trigger causing Locking Issues

Discussion in 'Performance Tuning for DBAs' started by aruram, Jan 30, 2008.

  1. aruram New Member

    We have been facing locking/blocking issues with our application and after some monitoring, tracing activity, I have found that the following trigger (Please see code below) seems to be causing majority of the pb -
    The table tblTest has 48 million rows
    It has clustered index on id col and non-clustered indexes on serial_fk, test_fk , sequence cols
    I am guessing the best thing would be to eliminate or rewrite the trigger and not use CURSORs.
    But, a couple of questions here are -
    On reviewing the output of syslockinfo,.sp_lock, i see that the UPDATE from this trigger is causing multiple page-level IX locks and escalated to a table level X lock. Is this occuring because of the CURSOR or the UPDATE ?
    What are some of the suggestions to improve performance in this scenario? Any feedback is appreciated .. Thanks
    CREATE TRIGGER [update_test_status] ON [dbo].[tblTest]

    declare @id int
    declare @Sequence int
    declare @serial_fk int
    declare @test_fk int
    declare @f_status int

    declare testdata_cur CURSOR LOCAL FOR
    select [id],test_fk,serial_fk,[sequence] from inserted where status=0

    OPEN testdata_cur
    FETCH NEXT FROM testdata_cur
    INTO @id,@test_fk,@serial_fk,@sequence

    set @f_status=@@FETCH_STATUS
    while @f_status=0
    update tblTest set status=1 where id<>@ID and serial_fk=@serial_fk and test_fk=@Test_fk and sequence=@Sequence

    FETCH NEXT FROM testdata_cur
    INTO @id,@test_fk,@serial_fk,@sequence
    set @f_status=@@FETCH_STATUS

    CLOSE testdata_cur
    DEALLOCATE testdata_cur
  2. ndinakar Member

    I think you can replace your entire cursor logic to something as simple as this:update
    tblTest Tjoin
    inserted i on t.serial_fk= i.serial_fk and t.test_fk = i.test_fk and t.Sequence = i.Sequencewhere
    t.id<> i.ID and i.status = 0
  3. ScottPletcher New Member

    > Is this occuring because of the CURSOR or the UPDATE ? <
    Because of the UPDATEs. Specifically, when the total space taken by locks exceeds a certain amount, SQL will escalate all eligible locks to table locks instead of row locks.
    You should definitely get rid of the cursor. And, unless you are updating an unusually large number of rows in that table, you should also look into getting more RAM.

Share This Page