Posted by Erland Sommarskog on 07/25/07 20:51

bobdurie@gmail.com (bobdurie@gmail.com) writes:
> I've tried adding the appropriate indexes, and it does seem to help.
> Just to clarify though, is the theory behind adding indexes simply
> that the less of the table the select queries scan, the less lock
> contention there will be, and hence fewer deadlocks?

Right. It's really two-dimensional, space and time. Less space is
scanned in less time, which means that the risk for two processes to
collide in time or space is reduced. But if there is action enough on
the system, there can still be deadlocks.

> None of those sql server locking/blocking articles talked directly about
> adding indexes as a solution to deadlocks, and i've found in the past
> adding indexes to frequently updated fields actually CAUSED deadlocks.

It's correct, that adding indexes also adds new oppurtunities for deadlocks.
And it does not only have to be index on updated columns. Say that one of
the tables you purge has 50 columns, and equally many indexes. Say
further that there is only the same queries as for the Loads table. There
would be a lot more risk for deadlock on this table than on Loads, because
both the INSERT and the purge needs to update these indexes.

> If my above theory assumption is true, when does it become cumbersome
> to continually add indexes? If my table has N unique queries, does it
> need N indicies to ensure no deadlocks? This doesn't seem right...

Usually, there is a big overlap between queries, so the same index can
serve several queries. Also, it matters a lot how often queries are run.
But for frequently running queries like yours, you should make sure that
all queries has a suitable index. Not necessarily the most optimal, but
you should definitely make sure that queries descend to scanning
tables.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]