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;
Scanno
Objectified

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);
Sought
Only Keys Now

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!

1 thought on “Index Key Column Order And Locking”

  1. all true, but has not muchto do with locking.
    a bad index forces more rows to be read, which certainly will cause more rows to be locked.
    nevertheless, always nice to read your postings.

Leave a Reply

Your email address will not be published. Required fields are marked *