|
|
Posted by Erland Sommarskog on 08/15/06 21:28
rawheiser (rawheiser@gmail.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@sommarskog.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
Navigation:
[Reply to this message]
|