Performance problems, sleeping and blocking processes
-
Hi,
I had posted a little while back about a weird performance problem we were having. We have a stored procedure that after a certain amount of time (~1 week) it will start running slowly. The asp page that calls that sp will quit responding. If we delete and recreate the sp in query analyzer, it will begin performing as expected. This problem still occurs occasionally, but now we are also finding a lot of sleeping and blocking processes. I don't know if these problems are related, but they sometimes occur at the same time. We have several different apps accessing the same sql server, but the blocking processes all belong to our app. Looking at the spid info and analyzing the indexes used we can narrow the problem down to about 6 pages that could be causing the problem. The blocking process will usually have a sleeping status with 1 open transaction and it will be AWAITING COMMAND. If I try and get the Last TSQL command for the SPID it will either be blank or sp_cursorclose 1. We are not specifically calling out cursors anywhere. Whenever this block occurs pages in our app will begin timing out.
What do I need to look for to figure out what is causing this problem? I understand it's hard to diagnose with this limited information, so please let me know what other information I need to gather to find the root cause of this problem.
Thanks,
Shawn
-
use sp_recompile which Causes this stored procedure to be recompiled the next time they are run.
Syntax
sp_recompile [ @objname = ] 'object'
Arguments
[@objname =] 'object'
Is the qualified or unqualified name of a stored procedure, trigger, table, or view in the current database. object is nvarchar(776), with no default. If object is the name of a stored procedure or trigger, the stored procedure or trigger will be recompiled the next time it is run. If object is the name of a table or view, all the stored procedures that reference the table or view will be recompiled the next time they are run.
Return Code Values
0 (success) or a nonzero number (failure)
I hope this help u
Alamir Mohamed
Alamir_mohamed@yahoo.com -
Do you try 'keep plan' option within your SP. I had some simliar problem, somehow the same SP after running a while, it start using the wrong plan.
Second, since you use ASP, do you know they are using any kind of jdbc driver ? Early release of jdbc driver has problem with cursor operation which may cause some memory leak. Download the latest one from Microsoft should avoid this problem.
-
Perhaps the first thing you may like to do is resolve the blocking issue. Apparently when a connection from an application accessing your database holds a lock, your application connection requires conflicting lock type. This forces your application connections to wait (they enter the BLOCKED status). Keep in mine that one connection can block another connection, regardless of whether they are initiated from the same application or separate applications. Here are some reasons for blocking:
· If the application cancels a query without also issuing the required number of ROLLBACK and COMMIT statements. The applications must roll back or commit the transaction before canceling the query. In this case make sure the offending query takes care of transaction nesting levels by committing or rolling back canceled transactions.
· If the application doesn’t retrieve the entire result set from a query. If this is happening, locks could be left and therefore blocking other applications. In this case make sure that the offending query retrieves all rows from the result set. If the offending query can not be designed to retrieve the entire result set then you may be unable to resolve the blocking problem. In this case you may have to restrict this application to a reporting server.
· A DELETE or UPDATE transaction affecting many rows normally hold many locks. This may happen when developers mix online analytical processing queries and online transaction processing queries on the same database. In this case optimize the offending query by creating or changing indexes. If the offending query is very large and complex break it into smaller and simple queries.
· If the application generates a distributed client/server deadlock. This is the worst blocking case, because SQL Server can not automatically detect this type of block. Normally this type of lock occurs if the application opens more than one connection to SQL Server and initiates a query asynchronously. Let’s assume that a single application thread with two open connections performs the following steps:
§ asynchronously starts a transaction and initiates a query on the first connection
§ Asynchronously starts another transaction and initiates a query on another connection
§ The application waits for the results
§ SQL Server returns the result set for one of the connections
§ The application starts to process the result set and continues to process until no more results are available due to blocking the query generating the results by the query executed on the other connection. At this point the first connection is blocked, waiting indefinitely for more results to process.
§ At the same time the second connection tries to return results to the application. However, because the application is waiting for results on the first connection, the results for the second connection are never processed.
In this case to resolve the issue you must use a QUERYTIME-OUT for each query or a LOCK TIME-OUT for each query or a BOUND CONNECTION.
You may find detail information about LOCKS, BLOCKING and designing applications to avoid blocking in SQL Server BOL.
Use SQL Server Profiler to capture and analyze the queries arriving to the server.
-
Thanks so much for the help. Here is some more information. We were able to deduce what page was most likely to be causing the problem and we temporarily disabled access to that page (it's not mission critical) to see if the problem reappears. Some of our pages use stored procs and some use inline queries. The page in question calls oConn.BeginTrans and then goes into a loop that modifies items in the record set. Because this is in the asp my (uneducated) guess is that a user can close their browser and the server never gets a RollbackTrans or a CommitTrans. This leads to an open transaction and other processes start getting blocked.
Our plan is to rewrite the asp to use a stored proc instead of an inline query. Does this seem like a good solution?
Thanks again for all the help.
-
Congratulations! apparently you found the culprit. Definitely, make sure you code efficient transactions in your stored procedure. For information on designing and coding efficient transactions see the SQL Server BOL.
Regards
-
Issuing begintrans commands outside of T-SQL/SP's is not a good idea....as you've found out....keeping it in SQL, allows the SQL engine to figure out/realise that the connection has been killed and can be rolled back....
In 99.9% of your DB accesses should be done via SP's.....for best performance/ security/ problem determination(prevention).
Good luck with the fix........
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply