After SP4, proc w. Cursor doesn't release keylocks

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • rawheiser
    #1

    After SP4, proc w. Cursor doesn't release keylocks

    Existing Stored Procedure, has been running well on SQL since 7.0.
    (but needed some tweaking to migrate to 2000).

    Now all of a sudden after installing SP4 of SQL 2000,
    this process slows down, and SQL Spotlight shows the number of locks
    just climbing throughout the processing run.

    According to the MS Knowledge Base Articles on KeyLocks .. this was a
    problem that was *fixed* in the service pack ... where as for me it is
    now broken.

    Article ID : 260652
    PRB: Nested Loop Join That Uses A "BOOKMARK LOOKUP ...WITH PREFETCH"
    May Hold Locks Longer http://support.microsoft.com/kb/260652/

    Article ID : 828096
    FIX: Key Locks Are Held Until the End of the Statement for Rows That
    Do Not Pass Filter Criteria http://support.microsoft.com/kb/828096/

    Anybody else have this issue, or have any "eazy" solutions?

    The proc cursors thru a list and runs a proc on each item in the "work
    list".
    This is an existing system
    with no plans to turn the process into a set oriented one,
    as is going away shortly.

    Tags: None
    • Erland Sommarskog
      #2
      Re: After SP4, proc w. Cursor doesn't release keylocks

      rawheiser (rawheiser@gmai l.com) writes:
      Now all of a sudden after installing SP4 of SQL 2000,
      this process slows down, and SQL Spotlight shows the number of locks
      just climbing throughout the processing run.
      >
      According to the MS Knowledge Base Articles on KeyLocks .. this was a
      problem that was *fixed* in the service pack ... where as for me it is
      now broken.
      >
      Article ID : 260652
      PRB: Nested Loop Join That Uses A "BOOKMARK LOOKUP ...WITH PREFETCH"
      May Hold Locks Longer http://support.microsoft.com/kb/260652/
      >
      Article ID : 828096
      FIX: Key Locks Are Held Until the End of the Statement for Rows That
      Do Not Pass Filter Criteria http://support.microsoft.com/kb/828096/
      >
      Anybody else have this issue, or have any "eazy" solutions?
      >
      The proc cursors thru a list and runs a proc on each item in the "work
      list".
      This is an existing system with no plans to turn the process into a set
      oriented one, as is going away shortly.
      It's not clear why think your problem is related to the problem discussed
      in the KB article. Apparently, your procedure has a cursor, so there
      might be other reasons for these locks.

      Is the entire loop one transaction?

      As a shot in the dark, if the cursor declaration just says:

      DECLARE cur CURSOR FOR

      try adding INSENSITIVE before CURSOR.


      --
      Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

      Books Online for SQL Server 2005 at
      http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

      Books Online for SQL Server 2000 at
      http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

        Comment

        • rawheiser
          #3
          Re: After SP4, proc w. Cursor doesn't release keylocks

          It IS an insensitive cursor.

          Looking thru DBArtisan I see the KeyLocks in its lock display window,
          and in SQL Spotlight I see the number of locks jump by 1000 or so every
          few seconds.

          How can I monitor what procedure is locking what resource?

          Other than injecting some debugging code into the procedures?

            Comment

            • Erland Sommarskog
              #4
              Re: After SP4, proc w. Cursor doesn't release keylocks

              rawheiser (rawheiser@gmai l.com) writes:
              It IS an insensitive cursor.
              As I said, it was a shot in the dark.
              Looking thru DBArtisan I see the KeyLocks in its lock display window,
              and in SQL Spotlight I see the number of locks jump by 1000 or so every
              few seconds.
              And it retains the locks?
              How can I monitor what procedure is locking what resource?
              Maybe my aba_lockinfo can be of help? If your procedure releases the locks,
              then you will have to get the right moment, but if it retains the locks
              you will see what it is. You find aba_lockinfo at
              http://www.sommarskog.se/sqlutil/aba_lockinfo.html.


              --
              Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

              Books Online for SQL Server 2005 at
              http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

              Books Online for SQL Server 2000 at
              http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

                Comment

                • rawheiser
                  #5
                  Re: After SP4, proc w. Cursor doesn't release keylocks

                  Thanks for the link, I will look at it for future issues.
                  (and browse about your site for other goodies).

                  I did find the problem,
                  though I am not exactly sure of the mechanism involved.

                  The outer procedure was calling a procedure
                  that a procedure three levels down was also calling,
                  when I removed the duplicate updating,
                  it then flew like the wind.

                  I also changed a Set NoCount OFF to ON, which may have helped as well.

                  The proc is called by SQLAgent,
                  and I'm not exactly sure where all that 'client chatter'
                  goes to when the server is running the process.
                  (the NULL device?).

                    Comment

                    Previous template Next
                    Working...