Blocking in SQL servers happens when a connection to SQL server blocks one or more query, and another connection to SQL server requires a conflicting lock type on query, or query locked by the primary connection. This leads to the another connection waiting until the primary connection releases its locks. One connection can block another connection, no matter whether or not they are from identical application or separate applications on different client computers.
A few seconds of blocking is normal and avoidable. But an excessive amount of blocking can cause connections (applications or user) to attend extensive periods of your time, foremost important information to get during a blocking condition is that blocking SPID (SQL process id), and what it is doing.
Gathering Blocking Information :
The query below identifies SPID (SQL process ID) at top of blocking chain and therefore SQL Statement –
Select * from sysprocesses where blocked <> 0 ;
Output : All columns like spid, kpid, blocked, waittime, waittype, waitresource, lastwaittype, dbid, uid, cpu, etc are empty.
After you have identified blocking SPID, issue subsequent command to urge the content –
dbcc inputbuffer (SPID)
To remove blocking, use the KILL command.
Note : Once you KILL a process, you are ending it and this will cause unpredictable results. Using SQL KILL command quite once against an equivalent spid kills newer processing that is reusing this spid. Use KILL command just one occasion unless you verify that method has not changed with prior KILL command.
When blocking increase to purpose where there is an impact on system performance, it is usually due to one among below reasons –
- A SPID holds locks on a group of resources for an extended period of your time before releasing them. This sort of blocking resolves itself over time but can cause performance degradation.
- A SPID holds locks on a group of resources and never releases them. This sort of blocking does not resolve itself and prevents access to the affected resources indefinitely.
To Identify the head of blocking chain by using features that are provided through SQL Server Management Studio.
To try this, use the below steps –
Select server object, expand Reports, expand Standard Reports, then click Activity – All Blocking Transactions.
Below report shows transactions at top of blocking chain if any –
If you expand the transaction, report will show transactions that are blocked by top transaction. This report will also show “Blocking SQL Statement” and therefore the “Blocked SQL Statement.”
- Difference between Structured Query Language (SQL) and Transact-SQL (T-SQL)
- Delete statement in MS SQL Server
- SQL Server Mathematical functions (SQRT, PI, SQUARE, ROUND, CEILING & FLOOR)
- SQL Server Identity
- SQL SERVER | Conditional Statements
- SQL Server | STUFF() Function
- SQL Server | SERVERPROPERTY()
- Mean and Mode in SQL Server
- Allow only alphabets in column in SQL Server
- SQL SERVER | IN Condition
- Reverse Statement Word by Word in SQL server
- Copy tables between databases in SQL Server using Import-and-Export Wizard
- Difference between MySQL and MS SQL Server
- Comparisons between Oracle vs SQL Server
- Introduction of MS SQL Server
- Select statement in MS SQL Server
- Order by in MS SQL Server
- Distinct clause in MS SQL Server
- Where clause in MS SQL Server
- Difference between Couchbase and MS SQL Server
If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to firstname.lastname@example.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.
Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.