Posted by Erland Sommarskog on 11/04/06 23:46

laststubborn (arafatsalih@gmail.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, 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

[Back to original message]