SQL Server Index Key Column Order And Locking

Fall Guy

---

Let’s say we have a super important query. It’s not really important.

None of this is important.

SELECT   u.DisplayName, u.Reputation, u.CreationDate
FROM     dbo.Users AS u
WHERE    u.CreationDate >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
AND      u.Reputation < 6
ORDER BY u.CreationDate DESC;

Maybe it’ll find users who created accounts in the last day who haven’t gotten any upvotes.

Shocking find, I know.

An okay index to help us find data and avoid sorting data would look like this:

CREATE INDEX ix_apathy 
    ON dbo.Users(CreationDate DESC, Reputation);

So now we know whose fault it is that we have this index, and we know who to blame when this happens.

Blocko

---

UPDATE u
SET u.LastAccessDate = GETDATE()
FROM dbo.Users AS u
WHERE u.Reputation = 147;

What’s going on here is that the optimizer chooses our narrower index to find data to update.

It’s helpful because we read far less pages than we would if we just scanned the clustered index, but the Reputation column being second means we can’t seek to rows we want.

The optimizer isn’t asking for a missing index here, either (okay, I don’t blame it for a query that runs in 145ms, but stick with me).

Switcheroo

---

If we change our index to have Reputation first, something nice happens.

To this query.

CREATE INDEX ix_whatever 
    ON dbo.Users(Reputation, CreationDate DESC);

With index order switched, we take more fine-grained locks, and we take them for a shorter period of time.

All That For This

---

If you have a locking problem, here’s what you should do:

• Look at your modification queries that have WHERE clauses, and make sure they have the right indexes
• Look at your modification queries that modify lots of rows, and try batching them
• If your modification queries are horror shows, see if you can separate them into parts
• If your critical read and write queries are at odds with each other, look into an optimistic isolation level

Thanks for reading!

Going Further

---

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.