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.
1. Check all sql server process and blocking process
Check the BlkBy column in the outp...Read More