Lock Problem and application is slowed down

[laststubborn]

Dear Memebers,

I have a critical problem. I have an application is running on 64 bit
machine. It used to be running on 32 bit machine. That application is
using a Stored Procedure that uses SELECT, INSERT, UPDATE statements.
Whenever this applicataion is being run all the processes are locked
and INSERTING operation becomes cumbersome. We tested again on 32 bit
machine however it happened again. So what might be the problem? Can
somebody help me ? Application creates a lot of processes in a minute
Should SQL Server be caple of recieving these fast inserting processes?
As a DB Admin what should I do to find out whether this problem is
coming from SQL Server or not?

If you respond me ASAP I really appreciate it

Regards

LS

[Erland Sommarskog]

laststubborn (ar*********@gm ail.com) writes:

I have a critical problem. I have an application is running on 64 bit
machine. It used to be running on 32 bit machine. That application is
using a Stored Procedure that uses SELECT, INSERT, UPDATE statements.
Whenever this applicataion is being run all the processes are locked
and INSERTING operation becomes cumbersome. We tested again on 32 bit
machine however it happened again. So what might be the problem? Can
somebody help me ? Application creates a lot of processes in a minute
Should SQL Server be caple of recieving these fast inserting processes?
As a DB Admin what should I do to find out whether this problem is
coming from SQL Server or not?

This question is difficult to answer because of lack of hard information,
and I'm afraid that I will have to ask for clarification.

So there is a stored procedure running. Do I understand that there are
multiple instances of the procedure running? What processes are blocked?
Other processes that are running the same stored procedure? Which operations
are blocked?

Which version of SQL Server do you have?

In general terms, the way to address blocking issues to investigate if
there are any indexes missing. The longer time a query takes to run,
the bigger the risk for blocking. Of course, you also need to know
what is blocked and where in the procedure blocking occurs. I have a
stored procedure that can assist with that, check out
http://www.sommarskog.se/sqlutil/aba_lockinfo.html.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[laststubborn]

Hi Erland ,

Sorry for the late respond. Eventhough we solved the problem and the
problem was coming from the application, I would like to know my
necessary steps to take the action on SQL Server along with your
suggestions.

Here are the answers of your questions:
-Yes the same SP was kept locing the Database
-The other processes are not the same we have other SELECt or UPDATE or
INSERT processes on our Database
-Our database is MS SQL 20000
Thanks

LS

Erland Sommarskog wrote:

laststubborn (ar*********@gm ail.com) writes:

I have a critical problem. I have an application is running on 64 bit
machine. It used to be running on 32 bit machine. That application is
using a Stored Procedure that uses SELECT, INSERT, UPDATE statements.
Whenever this applicataion is being run all the processes are locked
and INSERTING operation becomes cumbersome. We tested again on 32 bit
machine however it happened again. So what might be the problem? Can
somebody help me ? Application creates a lot of processes in a minute
Should SQL Server be caple of recieving these fast inserting processes?
As a DB Admin what should I do to find out whether this problem is
coming from SQL Server or not?

This question is difficult to answer because of lack of hard information,
and I'm afraid that I will have to ask for clarification.

So there is a stored procedure running. Do I understand that there are
multiple instances of the procedure running? What processes are blocked?
Other processes that are running the same stored procedure? Which operations
are blocked?

Which version of SQL Server do you have?

In general terms, the way to address blocking issues to investigate if
there are any indexes missing. The longer time a query takes to run,
the bigger the risk for blocking. Of course, you also need to know
what is blocked and where in the procedure blocking occurs. I have a
stored procedure that can assist with that, check out
http://www.sommarskog.se/sqlutil/aba_lockinfo.html.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Erland Sommarskog]

laststubborn (ar*********@gm ail.com) writes:

Sorry for the late respond. Eventhough we solved the problem and the
problem was coming from the application, I would like to know my
necessary steps to take the action on SQL Server along with your
suggestions.

Here are the answers of your questions:
-Yes the same SP was kept locing the Database
-The other processes are not the same we have other SELECt or UPDATE or
INSERT processes on our Database
-Our database is MS SQL 20000

I'm afraid that I don't have much to add than teh suggestion to use
aba_lockinfo to get an overview of who is locking whom, and from this
try to understand why.

One situation that I should have mentioned is that if your application
has set up a command timeout (which is 30 seconds by default in many
APIs) and cancels the batch after this time, the application should
always submit a

IF @@trancount 0 ROLLBACK TRANSACTION

since a timeout expired does not rollback any transactions, and not rolling
back in this situations can lead to locks piling up.

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx