Thursday, 17 August 2017

Simple way to identify blocking in SQL Server

In layman’s point of view, a blocking can be anything that restricts from being accessed. Say for example, a person is logged on to a computer which is with a single user account, in case a new person comes up to login to the same account, then the scenario is considered to be blocking.


In SQL Server, the similar scenarios comes up wherein we experience locks and blocks. General consequences would be one session being blocked by another session. Idea behind is simple, where a session would be working on a resource and a second session takes birth to access the same resource. Usually, the blocking behavior in SQL server is very normal. 

To check the SQL Server blocking, simply use the command as:

EXEC SP_WHO2 ACTIVE

This above command lists the SPIDs along with the status of the process and blocked sessions as “BlkBy” by default using the master system database. The screenshot for the same is mentioned below:


From screenshot, we find that the 83 SPID is using some resource to block the SPID 2. Here the option to stop the blocking bleeding is to use the command as shown below:

KILL 83

However, doing so will stop the blocking bleed, but there are high changes more problems may crept in case very important sessions are killed in the production server. Henceforth, always keep in mind that killing session should be considered very critical and should be carried by a person much familiar in the field. 

0 comments: