After SP4, proc w. Cursor doesnt 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.rawheiser (rawheiser@.gmail.com) writes:

Quote:

Originally Posted by

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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||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?|||rawheiser (rawheiser@.gmail.com) writes:

Quote:

Originally Posted by

It IS an insensitive cursor.

As I said, it was a shot in the dark.

Quote:

Originally Posted by

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?

Quote:

Originally Posted by

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@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||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?).

After creating database, running script to create tables?

I am in my apps master database and I have a Proc to create a project database. However, once that database is created, I have a long script that I need to run to create the tables, indexes, and views.

What is the best way to run this?

I'd intended to just make this part of my stored proc -- create database, then tables, then views. However, to simply create the database, I had to build a string to concatenate the passed in value of the new database. I'd hate to have to do this for every line of this script.

Should I simply take this script as is and create it as a stored proc in the new database and then run that proc?Well, you don't need to store it as a stored procedure in order to run it. You could just switch to the new database and run it through Query Analyzer. Alternatively, you could put a USE database statement at the beginning of the script, and that would switch the database focus for you. (You'll want to make sure it succeeds though, because otherwise you could end up creating all your objects in the Master database.)

Is this supposed to be part of an automated process?

blindman|||Originally posted by blindman
Well, you don't need to store it as a stored procedure in order to run it. You could just switch to the new database and run it through Query Analyzer. Alternatively, you could put a USE database statement at the beginning of the script, and that would switch the database focus for you. (You'll want to make sure it succeeds though, because otherwise you could end up creating all your objects in the Master database.)

Is this supposed to be part of an automated process?

blindman

Yes. I'm trying to allow admin users on our web app the ability to create new projects on the fly. Part of this is creating the new database.|||Originally posted by blindman
Well, you don't need to store it as a stored procedure in order to run it. You could just switch to the new database and run it through Query Analyzer. Alternatively, you could put a USE database statement at the beginning of the script, and that would switch the database focus for you. (You'll want to make sure it succeeds though, because otherwise you could end up creating all your objects in the Master database.)

Is this supposed to be part of an automated process?

blindman

Bummer: Server: Msg 154, Level 15, State 1, Procedure CreateclaimDexDB_SP, Line 22
a USE database statement is not allowed in a procedure or trigger.|||No, USE is not allowed in a procedure or trigger. I've gotten around that in the past by creating dynamic SQL statements, but I imagine your code is rather long for that.

Personally, I think this is only one of the drawbacks you will encounter by creating separate databases for each project. By far the easiest way to handle an application like this is to add scalability to your database to allow it to handle multiple projects. This will make it much faster and easier to add, delete, and manage projects. It will also prevent a lot of duplicated information between databases which are sure to get our of synch, and will allow for powerful comparison analysis between projects.

I think you are heading into an administrative nightmare by creating separate databases, and this problem that you have now is just the tip of the iceberg.

It is also not a good idea to be storing procedures like this in the master database. If you must, then create a separate database to store the procedures involved in creating project databases.

Other options include using SQL to load a template database from a backup file. The template database could have all your objects already stored.

...or your front end application could switch focus to the new database before executing the procedure for creating all the objects.

blindman|||Originally posted by blindman
No, USE is not allowed in a procedure or trigger. I've gotten around that in the past by creating dynamic SQL statements, but I imagine your code is rather long for that.

Personally, I think this is only one of the drawbacks you will encounter by creating separate databases for each project. By far the easiest way to handle an application like this is to add scalability to your database to allow it to handle multiple projects. This will make it much faster and easier to add, delete, and manage projects. It will also prevent a lot of duplicated information between databases which are sure to get our of synch, and will allow for powerful comparison analysis between projects.

I think you are heading into an administrative nightmare by creating separate databases, and this problem that you have now is just the tip of the iceberg.

It is also not a good idea to be storing procedures like this in the master database. If you must, then create a separate database to store the procedures involved in creating project databases.

Other options include using SQL to load a template database from a backup file. The template database could have all your objects already stored.

...or your front end application could switch focus to the new database before executing the procedure for creating all the objects.

blindman

I appreciate your comments, but in this case, separate databases is the right way to go. They truly are for distinct projects, clients, etc. and CAN be scaled across different servers if need be. In addition, some tables will start out the same, but can be altered by each client independently. This is tying into another existing application.

When I said master DB, I forgot there was a SQL database called master. I meant the main application database, which keeps track of the different projects, users, clients, etc.

As a general rule, I would agree with you, but in this case, we're doing the right thing.