Sqlserver blocking location

Many people have encountered such a situation. When the website reaches a certain access volume, the database will become a bottleneck and cause blocking. Some people think that this may be the limit of hardware, so they want to increase hardware devices. I personally think the culprit of the problem may be the SQL script with low performance, which causes blocking. If you and I share the same view, let's think together

Many people have encountered such a situation. When the website reaches a certain access volume, the database will become a bottleneck and cause blocking. Some people think that this may be the limit of hardware, so they want to increase hardware devices. I personally think the culprit of the problem may be the SQL script with low performance, which causes blocking. If you and I share the same view, let's think together

Many people have encountered such a situation. When the website reaches a certain access volume, the database will become a bottleneck and cause blocking.

Some people think that this may be the limit of hardware, so they want to increase hardware devices. I personally think the culprit of the problem may be the SQL script with low performance, which causes blocking.

If you and I share the same view, we can work together to find out the source of the problem.

Case 1.

One day I was told that our bookstore website could not be accessed. I immediately checked and found that two iis servers in bookstore showed service unavailable, I initially concluded that the SQL Server database is blocked because the same program makes it unlikely that two iis servers can be used at the same time.

To check whether a blocking occurs, check the sysprocess table of the master database to see if any process blocks other processes. The statement is as follows:

Select * from master .. sysprocesses where blocked> 0

Soon I found that a blocked = 51 blocked a lot of processes (view the blocked column visible), which is indeed consistent with my judgment; in order to further find out the blocking statement, the following statements are used:

Dbcc inputbuffer (51 );

The result is as follows:

EventType Parameters EventInfo

------------------------------------------------

RPC Event 0 p_Book_content; 1

It can be seen from the above that it is caused by p_Book_content (a stored procedure), but in this process, multiple tables are operated at the same time. What is the problem with the statement?

Next we will further locate the blocking location:

Sp_lock

The result is as follows (most data is omitted)

Spid dbid objid indid type resource mode status

Bytes -------------------------------------------------------------------------------------------------

51 14 206623779 0 TAB X WAIT

52 14 0 0 DB S GRANT

53 14 0 0 DB S GRANT

...

...

...

Now let's take a look at the spid = 51 line, mode = X indicates the exclusive lock, status = WAIT indicates waiting (BLOCKED), dbid = 14 indicates the database id, objid = 206623779 is the id of the locked object. We can use the following functions to obtain the database and table:

Select db_name (@ dbid) ----- "book_db

Select object_name (@ objid) ------- "t_book

That is, the t_book table of the book_db database is locked. At this time, check the p_Book_content stored procedure and find that only one statement operates on the t_book:

Update t_book set hitcount = hitcount + 1 where bookid = @ bookid

This statement is used to update the number of book clicks. Why does the above statement cause blocking? In my opinion, the most likely situation is that too many people access the table at the same time and too many update operations on the table, so I finally switched to another method instead of performing update operations on the t_book table in real time, instead, insert a record to an intermediate table for each access, and then use a job to update the book clicks every 10 minutes. After this improvement, the problem is finally solved.

Relevant information can be found on book online. Keywords: sp_lock, sysprocesses, dbcc inputbuffer, db_name (), object_name ()

Note: It was originally released in csdn, but now it is ridiculous. We will try to cultivate it here.