Optimized locking wait stats are the new lock waits SQL Server 2025 records when optimized locking is on. On Night 24 at the Clipboard Diner, a new state code swaps booth cards for wristbands. Then I show what LCK_M_S_XACT_MODIFY and LCK_M_S_XACT_READ mean, how to check the setting, and why a long transaction still hurts.
THREADPOOL Wait Stats: When Worker Threads Run Out
THREADPOOL wait stats mean a task is waiting for a worker thread because every worker is already busy. On Night 16 at the Clipboard Diner, every cook holds a ticket that can’t move, and nobody is free to take a new order. Then two queries show how close your server is to its worker limit, and how to get in when nobody else can.
LCK_M Wait Stats: Lock Waits and Blocking
LCK_M wait stats show how long queries wait for a lock that another session holds. On Night 15 at the Clipboard Diner, a party in booth 7 pays and then won’t leave, and three parties wait behind it. Then two queries find the blocking chain and its head blocker on your own server.
Live Wait Stats: What Is Waiting Right Now
Live wait stats show what every request in SQL Server is waiting on at this moment, and who is in its way. On Night 4 at the Clipboard Diner, the Saturday rush stalls behind a party in booth 7 that isn’t waiting for anything. Then three queries find the waiting tasks, the head blocker and one session’s own waits.
Loading a Fact Table Without Blocking Reports
Loading a fact table without blocking reports calls for isolated staging, aligned partition switching, short locks, and a tested reader path.
Indexing Foreign Keys
Indexing foreign keys can speed joins and parent deletes when child-side indexes are missing; learn how to find gaps and test write tradeoffs.
SQL SERVER – Difference Between Update Lock and Exclusive Lock
What is the difference between Update Lock and Exclusive Lock? An Update Lock can sit on a row that has a Shared Lock, then turns Exclusive when it writes.