SQL Server blocking causes and solutions

The previous article describes the OLTP system performance analysis of SQLServer application mode. Analyze SQL Performance issues from five perspectives. This chapter is still in five ways of SQL Performance: blocking and deadlock. Here, we analyze the blocking source by combining the Field Values in sysprocesses. Blocking can be divided into the following five common types (see table ). Waittype, open_tran, status, all are sy

This article describes the OLTP system performance analysis of the SQL Server application mode. Analyze SQL Performance issues from five perspectives. This chapter is still in five ways of SQL Performance: blocking and deadlock. Here, we analyze the blocking source by combining the Field Values in sysprocesses. Blocking can be divided into the following five common types (see table ). Waittype, open_tran, status, all are sy

This article describes the OLTP system performance analysis of the SQL Server application mode. Analyze SQL Performance issues from five perspectives. This chapter is still one of the five aspects of SQL Performance: "blocking and deadlock"

Here, we use a combination of field values connected to sysprocesses to analyze the blocking source. In Hong Kong Space, blocking can be divided into the following five common types (see table ). Waittype, open_tran, and status are all values in sysprocesses. "Self-repair ?" The column indicates whether blocking can automatically disappear.

5 common blocking types

Type waittypeopen_transtatus self-repair reason/other features

1 is not 0> = 0runnable yes. After a statement is run, it takes a long time to run. You need to wait for some system resources (such as hard disk read/write, CPU, or memory) during running ).

20x0000> 0sleeping is not supported. However, if you run the KILL statement, this link can be easily terminated. The client may encounter a statement execution timeout or cancel the execution of the previous statement, however, the transaction is not rolled back. You can see an Attention event in SQL Trace.

3 0x0000
Zero x 0800
0x0063> = 0runnable cannot. If you know that all the results of the client are actively removed, the website space, or actively disconnected, you can run the KILL statement to terminate it, however, it may take up to 30 seconds for the client to remove all results in time. In this case, open_tran = 0, and the transaction isolation level is also the default (read committed ), however, this connection will lock resources.

40x0000> 0rollback yes. In SQL Trace, we can see that this SPID has sent an Attention event, indicating that the client has encountered a timeout event, or actively asked for transaction rollback.

5. Various values may be greater than or equal to 0 runnable, until the client cancels the statement or actively disconnects. You can run the KILL statement to terminate it, but it may take up to 30 seconds for the application to run a life-and-death lock, which is embodied in blocking in SQL Server. The hostname value of the blocked connection in Sysprocesses is the same

The following describes the causes of these types and solutions.

Type 1: Blocking caused by long statement running time. The Hong Kong server is rented. The statement itself is running normally and only needs to wait for some system resources.