Introduction

Welcome to the SQLSurfer blog. I specialize in performance tuning, among other things, so you’ll see that focus in my posts. I still post in other places, but this gives me a chance to quickly put some thoughts down, quick posts that don’t fit into the framework of the larger sites. I’m also going to use this for some opinion posts, ramblings to be more precise. I hope you enjoy it. Let me know if there is something you’d like to see that I haven’t covered.

I was preparing a post about finding implicit data type conversions in SQL Server queries, when I had to work through a few technical issues. That post will come later, but this created an interesting diversion based on the query used to solve those problems. I’ve written about regular expressions (regex) in SQL Server before, and that was on my mind as I analyzed extracting implicit data type conversions from query plans.

Deadlocks and excessive blocking is the bane of many development teams and DBAs. They are often at the root of performance problems. Understanding each of these problems and how to solve the root cause can be difficult. I find that the terms are used interchangeably and frequently mixed. Understanding the difference, and diagnosing the issue is crucial to resolving these problems. Both happen due to incompatible locks being held or requested on tables, but they differ in their root cause. Some troubleshooting methods work for both problems, but deadlocks generally require logic changes to fix permanently and consistently.
This post is aimed at giving a general background on blocking and deadlocks. It also shows some basic methods to find these problems and general ways to fix them. I’m not going into extreme detail. This is just an introduction.