Common causes and solutions for blocking:
1. Due to the blocking caused by the long running time of the statement, the statement itself is running normally and only needs to wait for some system resources.
Solution:
A. Is there room for optimization of the statement itself? B. What is the overall performance of SQL Server? Is there a resource bottleneck that affects the statement execution speed, such as memory, hard disk, and CPU?
2. Blocking caused by a transaction not committed as expected
The feature of this type of blocking is that the problematic connection has long been idle (sysprocesses. status = 'sleeping' and sysprocesses. CMS = 'awaiting command '), but if you check sysprocesses. open_tran, it will be found that it is not 0, and the transaction is not committed. Many of these problems are caused by an execution timeout or other reasons. The statements executed at that time were terminated in advance, but the connection was retained. The application does not follow the transaction commit or rollback command, causing a transaction to be left in SQL Server.
Solution:
ApplicationProgramYou must be aware that any statement may experience unexpected termination and handle errors properly. These tasks include:
· When calling SQL server, an error capture and processing statement must be added: If @ trancount> 0 rollback Tran; (set if @ error <> 0 rollback Tran in the program; this statement cannot always be executed) · Set the connection property "set xact_abort on ". If there is no way to standardize the error capture and processing statements of the application, one of the fastest way is to run "set xact_abort on" after each connection is established, or when a problematic stored procedure starts" · Whether to disable the connection pool. Run the sp_reset_connection command to clear all objects left over from the current connection, including rolling back uncommitted transactions.
3. The statement runs for a long time because the client does not retrieve the result set in time.
The total execution time of a statement in SQL Server includes not only the execution time of SQL Server, but also the time when the result set is sent to the client. If the result set is large, the SQL server will package and send the result several times. If the result set is not sent once, wait for confirmation from the client. SQL server sends the next result set package only after confirmation. After all the results are sent, SQL Server considers that the statement execution is complete and releases the requested resources (including lock resources ). For some reason, if the processing result of the client application is very slow or does not respond, or simply ignores the request sent by SQL Server to the result set, SQL Server will wait patiently, bank calls may cause blocking when the statement is executed for a long time. Solution: A. Return a large result set with caution B. If a cannot be implemented in a short term, try the connection of the large result set to use the read uncommitted transaction isolation level, so that the query will not apply for the s lock.
4. The blocked source is always in the rollback status.
This situation is derived from the first type of situation. Sometimes it is found that a connection is blocked by others. To solve the problem, the connection can be automatically exited or forcibly exited (directly kill the connection ). In most cases, these measures eliminate blocking. But there are also exceptions. To maintain the consistency of database transactions at the time of connection exit, SQL Server will roll back the transactions that have not been submitted yet. SQL Server needs to find all the records modified by the current transaction and change them back to the original state. Therefore, if a delete, insert, or update task runs for one hour, it may take one hour to roll back. Some users may not be able to wait and restart SQL Server directly. When SQL Server is disabled, rollback is interrupted, and SQL Server is quickly disabled. However, this rollback will start again the next time the SQL server restarts. If the rollback cannot be completed quickly during restart, the entire database will be unavailable.
Solution:
The best way is to avoid such large modifications during working hours. These operations should be completed at midnight or on weekends as much as possible. If the operation has been performed for a long time, it is best to wait for it to finish. If you need to do this when there is a workload, it is best to divide a large operation into several small operations to complete the distribution
5.Life and death locks in applications
RelatedArticle: SQL Server lock resource mode detailed description SQL Server 2008 R2 connection too high Solution Using sys. sysprocesses checks the blocking and deadlock of SQL Server. This article is first published in the blog: common causes and solutions for blocking of SQL Server
