Compare upsert patterns under concurrent calls, protect missing-key ranges, test MERGE with HOLDLOCK, and keep unique keys as a guard.
A Retry Loop in T-SQL for Deadlock Victims and Lock Timeouts
Build a bounded retry loop for deadlock and lock-timeout errors, roll back each failed transaction, and keep diagnostics beside retries.
SQL SERVER – FIX – Property IsLocked is Not Available for Login
Opening sa login properties in SSMS failed with Property IsLocked is not available for Login. I used Profiler to find the query returning NULL, and here is the fix.
Finding Hot Tables With sys.dm_db_index_usage_stats
Identify hot tables with index usage, lock and latch counters, table sizes, and retained snapshots that describe a real workload interval.
SQL SERVER – Find Blocking Using Blocked Process Threshold
My friend Jim came back with more blocking questions. Here is how the blocked process threshold option, a feature hidden in plain sight, helps find blocking.
SET XACT_ABORT ON: Stopping Timeouts From Leaving Open Transactions
Use SET XACT_ABORT ON to prevent client timeouts from leaving transactions and locks open, with TRY CATCH and a sleeping-session query.
A Shrink That Stops Blocking Others: SHRINKFILE WAIT_AT_LOW_PRIORITY
A shrink at 4 PM on Friday can block your whole app. See what the low-priority option does, and why shrinking is still a rare tool.