No, Lock
Locks are necessary, I’m told. There’s this whole “ACID” thing that makes databases work. It’s generally a good idea for people who into correct answers.
But blocking is one of the most common performance problems I see. Why? Because a long time ago Microsoft made the silly choice to use a pessimistic isolation level by default.
That changes in Azure SQL DB, but what it means for everyone else is that when modification queries come trotting along with their incessant need to lock things, other queries that might want to work with that data have to wait.
For other write queries, that’s a totally acceptable scenario. For queries that need to read data, it’s not cool at all.
In Your Area
Other database platforms use an optimistic isolation level, often called multi-version concurrency control (MVCC).
Under those implementations, queries that need to read data currently being modified will read the previously committed version. Think of it as reading the last known good version of the data.
You can do that in SQL Server using Read Committed Snapshot Isolation (RCSI), or Snapshot Isolation (SI). I don’t want you to think it doesn’t exist — it totally does — it’s just not the default. You have to turn it on.
There are interesting differences between the two, which you can read about here. The biggest general difference is that when you turn on RCSI, all read queries will start using it unless your request asks for a different one. You can do that in a connection string, by setting the transaction isolation level, or using table hints in the query (like NOLOCK).
Overall, this is my favorite way to compensate for locking waits. Sure, you’ll still have blocking for write queries, but your read queries will have a much easier time of things.
Writes vs Writes
When you need to get write queries to cooperate, there are some general steps you can take.
- Get rid of under-used indexes
- Batch modifications into smaller chunks
- Tune the modification queries to be as fast as possible
- Make sure your modification queries have the right indexes
- Don’t do goofy things with triggers and foreign keys (especially cascading ones)
- Only use Change Tracking or Change Data Capture if you really need to
Is this stuff covered in glory? No, not usually. But it’s taking care of the little stuff along the way that makes life easier as your database and userbase grows.
- Cleaning up poorly utilized indexes will give you fewer objects to lock when queries need to modify data.
- Batching modification queries gives you a more reliable number of locks, and is kinder on your transaction log
- Tuning modification queries to get rid of inefficiencies will give you shorter locking durations
- Giving update and delete queries good indexes to find rows of interest will do the same
- Not making SQL Server do extra work as data changes gives you fewer side effects to worry about
We’ve covered a lot of ground this month, and I hope you’ve been enjoying it. The last thing we have to talk about is query plans, which is going to help us start tying a lot of stuff together.
Thanks for reading!
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.