SQL: Fixing Locking and Blocking Issues in SQL Server - Part 1

SQL: Fixing Locking and Blocking Issues in SQL Server - Part 1

I get lots of requests to help customers with their SQL Server systems. Many, many requests start with customers asking me if I can help them fix their blocking issues. Most customers aren’t actually aware of true blocking issues. Invariably what they mean, is that they’ve started to have deadlocks and things are blowing up.

[caption id=“attachment_4816” align=“alignnone” width=“384”]Awesome image by Jens Johnsson Awesome image by Jens Johnsson[/caption]

Is blocking really the issue?

I always start by working out if they really have a blocking issue. When I see performance problems, after eliminating obvious hardware and configuration issues, I’d say the issues fall into these buckets:

  • Application Design 50%
  • Indexing 35%
  • Blocking/concurrency 10%
  • Other tricky stuff 5%

While wait statistics and other intricate details are interesting and geeky, don’t mess your head with them at this point. The number 1 cause of blocking issues of any type (including deadlocks), is actually query performance. If your queries are running much longer than they should be, you have far more chance of them blocking each other.

So often, once I fix the query performance issues, all the blocking problems have vanished.

Fixing the application design issues is hard, and often political, but fixing the indexing issues is usually a lot easier.

To fix the query performance issues caused by indexing, you can do the following:

  • Find out which queries are causing the problems (it’s not worth wasting your time on the others)
  • Fix the obvious indexing issues

If you don’t know how to do that, I have a free course (4 Steps to Faster SQL Server Applications) that walks you through an effective process. It’s online, and on-demand so you can do it right now. It’ll only take a few hours.

You’ll find it here:

https://training.sqldownunder.com/p/4-steps-to-faster-sql-server-applications

That covers the really obvious issues. For trickier indexing issues, we have another course (SQL Server Indexing for Developers). It’s not free but it’s great value with helping you understand SQL Server indexing. You’ll find it here:

https://training.sqldownunder.com/p/sql-server-indexing-for-developers

In Part 2 of this series of posts, I’ll assume you’ve already fixed these issues and your queries aren’t running slow.

2019-04-18