Whenever I end up on a situation where we have lots of blocking issues with my client, I depend on the script of the Blocking Tree.
Reading Deadlock Graphs From the system_health Session
Read deadlock graphs from system_health, identify victims and resource owners, reproduce a two-session cycle, and choose a targeted fix.
Create an Index Online in SQL Server: What ONLINE = ON Locks
To create an index online in SQL Server, add WITH (ONLINE = ON) to CREATE INDEX, and writers keep working while the index builds. This post compares a plain build with an online build on a three million row table, measures what each one blocks and what the online build costs, and shows where it still waits.
Application Locks With sp_getapplock: One Job at a Time
Use sp_getapplock to serialize a job, compare transaction and session ownership, handle return codes, and inspect a two-session test.
Counting Locks per Session With sys.dm_tran_locks
Count locks per session by resource and mode, connect open transactions, and recognize escalation while keeping snapshot limits clear.
Query Runs Slow Occasionally: Five Checks to Find Why
When a query runs slow occasionally, the cause sits around the query: a lock, a job, a file growth, tempdb or a shortage of worker threads. This post gives one tested T-SQL check for each and a small demo for blocking and file growth. It also says what to do when all five checks find nothing.
READPAST Query Hint: Skip Locked Rows in SQL Server
The READPAST query hint makes a read skip rows that another transaction has locked, instead of waiting for them. This post builds a five-seat demo, runs it in two windows and lists the cases where the hint does nothing. It also shows the work queue pattern that pairs READPAST with UPDLOCK.