Locking, Blocking, and Isolation Levels

Monitoring Snapshot Isolation with Perfmon in SQL Server (video)

Snapshot Isolation can be a great way to reduce lock waits and speed up your SQL Server, but long running transactions could still slow you down. Join Kendra Little to learn how to monitor for these sneaky killers using performance monitor. Brent says: if you’re a developer and you’re getting started building a new SQL…
Read More

Testing ALTER INDEX REBUILD with WAIT_AT_LOW_PRIORITY in SQL Server 2014

One of the blocking scenarios I find most interesting is related to online index rebuilds. Index rebuilds are only mostly online. In order to complete they need a very high level of lock: a schema modification lock (SCH-M). Here’s one way this can become a big problem: An online index rebuild starts against a large index A…
Read More

Read Committed Snapshot Isolation: Writers Block Writers (RCSI)

When learning how Read Committed Snapshot Isolation works in SQL Server, it can be a little tricky to understand how writes behave. The basic way I remember this is “Readers don’t block writers, writers don’t block readers, but writers still block writers.” But that’s not so easy to understand. Let’s take a look at a simple test…
Read More

Finding Blocked Processes and Deadlocks using SQL Server Extended Events

A lot of folks would have you think that Extended Events need to be complicated and involve copious amounts of XML shredding and throwing things across the office. I’m here to tell you that it doesn’t have to be so bad. Collecting Blocked Process Reports and Deadlocks Using Extended Events When you want to find…
Read More

Implementing Snapshot or Read Committed Snapshot Isolation in SQL Server: A Guide

How to change isolation levels without losing your marbles. A client said the coolest thing to me the other day. He said, “We talked before about why we would want to start using optimistic locking in our code. How do we get there?” If you’re not a SQL Server geek, that comment probably doesn’t even…
Read More
Menu