Does NOLOCK really applies no lock? No. NOLOCK takes a Sch-S, or schema stability, lock. Here is a simple script to see it for yourself.
Diagnosing Blocking Chains With sys.dm_exec_requests
Trace blocking chains from active requests to a candidate head session, inspect sleeping transactions, and verify the owner before action.
Full-Text Index Blocking Redo on an Always On Secondary
A full-text index can block the redo thread of a readable Always On secondary. This post shows the three clues to read in sys.dm_exec_requests, how to find the internal table and how to check change tracking. It ends with the fix: manual tracking and a scheduled population.
Decoding wait_resource: From a KEY or PAGE Lock to the Table
Decode wait_resource for blocked requests, map KEY and PAGE locks to objects, and keep undocumented row-hash checks on test copies.
ROWLOCK Hint and Slow Performance in SQL Server
A ROWLOCK hint makes SQL Server lock each row it reads instead of whole pages. This post counts the locks of the same scan with and without the hint, times both versions, shows how an open transaction on a busy server changes the result, and explains how ROWLOCK differs from UPDLOCK.
Kill All Processes for a Database in SQL Server
To kill all processes for a database, set it to SINGLE_USER with ROLLBACK IMMEDIATE and then back to MULTI_USER. This post tests that method against two open sessions, compares it with a KILL script and with RESTRICTED_USER, and explains when each one fits. It also covers what the killed sessions see and why you should not do this on production without asking.
Restart Azure SQL Database: What Works and What Doesn’t
You can’t restart Azure SQL Database the way you restart SQL Server, because Microsoft runs the service. This post shows what to do instead: find and end the blocking session, scale the database, or trigger a failover. It tests the blocking query on a demo database and explains why a stack dump is not a restart.