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;
SQL Server Query Plan
Scanno
SQL Server sp_WhoIsActive Locks
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);
SQL Server Query Plan
Sought
SQL Server sp_WhoIsActive Locks
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!

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.



One thought on “SQL Server 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.

Comments are closed.