Maximizing Performance in SQL Server: Best Practices for avoiding Locking and Blocking

In a developer's lifetime, you will be faced with investigating SQL Query issues. One of them is a blocking query.

But what is that? Why is that? And how you can avoid them?

Blocking Queries

Blocking queries in SQL Server can cause significant performance issues and can lead to delays in executing other queries. Blocking occurs when one query holds a lock on a resource, preventing another query from accessing it. When this happens, the blocked query has to wait until the lock is released, and this can result in a significant decrease in performance.

There are different types of locks in SQL Server, and each has a different level of granularity. The most common type of lock is a shared lock, which is used to prevent other queries from modifying a resource while it is being read. Another type of lock is an exclusive lock, which is used to prevent other queries from accessing a resource while it is being modified.

There are several reasons why blocking can occur in SQL Server. One reason is that a query is holding a lock for an extended period, preventing other queries from accessing the resource. Another reason is that a query is waiting for a resource that is being held by another query.

To better understand the different types of blocking queries in SQL Server, let's take a look at some examples:

1. Deadlock
2. Latch contention
3. Long-running queries
4. Lock escalation

Let's deep dive into the different types, for example, let's assume you have the following table structure

Deadlock

A deadlock occurs when two or more queries are waiting for resources that are being held by each other. In this case, neither query can proceed, and they remain in a deadlock state until one of them is terminated. Deadlocks can be difficult to detect and resolve, and they can cause significant performance issues.

Here are two example Queries

Query 1

Query 2

In this example, Query 1 updates the price of a product and the total price of an order, while Query 2 updates the status of an order and decreases the quantity of a product. If these queries run simultaneously, they can result in a deadlock because each query is waiting for a resource that is being held by the other query.

Latch contention

Latch contention occurs when multiple queries are trying to access the same data page simultaneously. This can result in one query being blocked while another query holds the latch. Latch contention can occur when multiple queries are accessing the same index or when there are hotspots in the data.

Query 1

Query 2

In this example, both queries are accessing the same index on the "products" table, which can result in latch contention. If Query 1 acquires the latch on the index, Query 2 will be blocked until the latch is released.

Long running queries

Long-running queries can cause blocking if they are holding locks for an extended period. If other queries are waiting for the resource that is being held by the long-running query, they will be blocked until the lock is released.

Query 1:

Query 2:

In this example, Query 1 updates the price of a product and waits for 10 minutes before committing the transaction. During this time, Query 2 will be blocked because it is waiting for the lock on the "products" table.

Lock escalation

Lock escalation occurs when a query acquires too many locks on a resource, and SQL Server decides to escalate the locks to a higher level of granularity. This can result in other queries being blocked while the locks are being escalated.

Query 1:

Query 2:

In this example, both queries are trying to acquire shared locks on the "products" table. If SQL Server decides to escalate the locks to a higher level of granularity, other queries that are trying to acquire shared locks on the table will be blocked until the locks are released.

How to avoid them

To minimize blocking queries in SQL Server, it is essential to optimize queries, use appropriate indexes, and avoid long-running transactions. Additionally, it is crucial to monitor SQL Server for blocking queries and take appropriate action to resolve them quickly.

I created a small checklist of best practices to walk through when you developing SQL-Queries

1. Use the appropriate transaction isolation level: The transaction isolation level determines how much locking is used when accessing data in a transaction. By default, SQL Server uses the "Read Committed" isolation level, which can result in a lot of locking. You can use a lower isolation level like "Read Uncommitted" or "Snapshot" to reduce lockings but be aware that these levels may allow dirty reads or inconsistent data.
2. Keep transactions short and sweet: Long-running transactions increase the chances of locking and can cause other queries to wait for a long time. Try to keep transactions as short as possible, and commit or roll back them as soon as the work is done.
3. Avoid unnecessary locking: Locks are acquired when a query needs to modify data, but they can also be acquired when reading data. For example, if you use the "FOR UPDATE" clause in a SELECT statement, SQL Server will acquire a shared lock on the rows returned by the query, even if you don't actually modify the data. Try to avoid unnecessary locking by using the appropriate clauses and hints.
4. Use row-level locking: SQL Server supports different levels of locking, from page-level locking to row-level locking. Row-level locking is more granular and can reduce contention and blocking. You can use the "ROWLOCK" hint to force row-level locking.
5. Monitor and tune your queries: If you notice that some queries are causing locking or blocking issues, you can use SQL Server Profiler or other monitoring tools to identify the problems and tune the queries. For example, you can add appropriate indexes to speed up queries or rewrite queries to use fewer locks.
6. Use optimistic concurrency control: Optimistic concurrency control (OCC) is a technique that allows multiple users to access and modify data concurrently without blocking each other. OCC uses versioning or timestamps to detect conflicts and resolve them without blocking. SQL Server supports OCC through the "rowversion" data type and the "READ_COMMITTED_SNAPSHOT" database option.

Conclusion

In conclusion, blocking queries in SQL Server can cause significant performance issues and can lead to delays in executing other queries. Understanding the different types of blocking queries and taking appropriate action to resolve them is crucial for maintaining the performance and availability of SQL Server. Please keep in mind that this process is an ongoing process, because your database increase and it will get more complex. SO in fact you will be faced very fast to a blocking scenario.