Fixing Locking and Blocking Issues in SQL Server - Part 3 - Indexing

Fixing Locking and Blocking Issues in SQL Server - Part 3 - Indexing

This is part 3 in a series of posts:

  • Part 1 covered being sure there is a locking and blocking issue
  • Part 2 covered the impact of RCSI

Today, though, I want to look at the next potential issue, and that’s indexing.

Indexing impacts on locking

It might seem obvious that you need to fix indexing issues in your database applications but it mightn’t be so obvious that locking is one of the main things that is affected.

There are two ways that poor indexing impacts locking:

  • Query duration
  • Number of columns (and data volume) involved in the locks

One of the key points that I made in the previous post was that the longer queries run for, the longer they hold locks.

Before you start investigating any detailed issues around locking, you must ensure that the queries run as quickly as possible. 

So often, as soon as the queries run fast, the locking and blocking issues disappear.

But another more subtle issue surrounding indexes is that they can avoid the need to lock entire rows of data when reading. Take the following query plan running against AdventureWorks as an example:

This query just needs StateProvinceID from the address table. There is an index on the StateProvinceID column so SQL Server reads from that index rather than from the table. No big surprise there but note that only a small amount of data will be read and locked.

Now, look at this query plan:

In this case, only the City column was needed but note the index that SQL Server chose to read. It is an index on AddressLine1, AddressLine2, City, StateProvinceID, and PostalCode. Notice that City was not the first component of the index. So why did SQL Server choose to use this index?

Because it was the smallest object it could read that contained the information, even though it had to read the whole index.

Whenever it can, SQL Server tries to minimize the amount of data it’s reading, so once again, the index here will reduce the amount of locking that’s going on. And less locking means less potential for locking and blocking issues.

Next time I’ll talk about the issue that seems to get the most attention: deadlocks.

Learning about indexing

If indexing isn’t your “thing” (yet), now would be a great time to take our online on-demand course on SQL Server Indexing for Developers. (It will help DBAs too).

2019-05-02