Monthly Archives March 2015

Capture Blocking Locks – SQL Server

by Snehashish Ghosh

Scenario


SQL Server database used for a busy OLTP production database comprises of more blocking locks compared to oracle server for similar application design. So while managing a sql server production database we observe that blocking locks are in common place. While locks are required for maintaining data integrity blocking locks causing halt to simultaneous transactions are something application always want to avoid. This is an approach to capture blocking locks in the SQL Server database.     

 In SQL Server query execution is not only delayed by poorly written sql queries but also due to blocking sessions causing delay in query execution.

 Solution


 1.  Check all sql server process and blocking process

sp_who2

sp_who2 BlkBy

sp_who2 output

Check the BlkBy column in the outp...

Read More