One of the most common performance problems with SQL Server databases in production is the blocking of queries, which happens because database resources are locked. Understanding why locking happens is just half the battle. Being able to resolve locking, which will resolve blocking issues as well, is the second half.
What Are Locking and Blocking?
Locking is the process by which the SQL Server database engine ensures no changes are made to a row, page, partition, or table while another user (or a system query, or the SQL Server itself) is reading the same part of the system.
Locking can happen at several different levels of the table:
- Record (row level)
- Page
- Partition
- Table
- Database
Page-level locks are most common within SQL Server, because these are what it uses the most by default. Get more information about SQL Server locks.
(Note: For simplicity, this post will describe locking at the page level, but locks can be taken at the row, partition, table, or database levels as well.)
Blocking is a natural side effect of locking. When a page is locked by one user, the next user who tries to read or write that page is unable to do so, because they’re waiting for the lock held by the first user to be released. While they’re waiting for this lock to be released, we say they’re being blocked, and blocking is now occurring with the database.
Why Locking Happens
Locking happens with every transaction executed against the database. From the simple SELECT * FROM table queries to extremely complex updates with subqueries, everything requires locks. Normally these locks are extremely short, just a millisecond or two. However, locks last for the duration of the query, so if an update takes one second, the locks will be held for one second. We can see this visually in Figure 1.
Figure 1: Effect of locking on another query
Resolving Locking and Blocking
Resolving locking issues (and the related blocking) requires looking at the database query causing the locking, not looking at the query being blocked. Monitoring the SQL Server database reveals queries that cause excessively long locking and blocking.
When you check the internal behavior of a query that’s blocking another, you can (or will) usually see poorly optimized behavior. For instance, a query may be performing an index scan instead of an index seek. In either case, performance tuning to optimize the behavior can make the blocking query more efficient, releasing the lock sooner
To identify potential optimizations, look at the execution plan and scrutinize the columns SQL Server is accessing. When SQL Server is attempting an index seek, but the index doesn’t satisfy the entire query, the tool tip for the index seek within the query plan will look similar to Figure 2, containing both a seek predicate (which is causing a seek) and a predicate (which is causing a scan of the rows returned by the seek).
Figure 2: An index seek must be performed when an index scan does not satisfy a query.
When an index scan is executed, the tool tip for the index scan within the query plan will look similar to the tool tip shown in Figure 3, with a predicate but no seek predicate.
Figure 3: An index seek is performed instead of an index scan.
In either case, we can improve the speed of the query causing this performance problem by creating an index with the values listed by the seek predicate and the predicate as key columns within a single index and adding any columns shown in the output section of the tool tip (not shown in Figure 2 or Figure 3) as included columns within the index.
As you research monitoring tools, look at SolarWinds® SQL Sentry. This product is built to provides the necessary visibility into your data to keep your pipeline humming, including the integrated Plan Explorer scoring algorithms designed to make looking at execution plans much easier to help with performance tuning. To get started with a free trial, click here. See for yourself all they can do for you.
