Posted by bobdurie@gmail.com on 07/25/07 20:31

On Jul 24, 6:16 pm, Erland Sommarskog <esq...@sommarskog.se> wrote:
> bobdu...@gmail.com (bobdu...@gmail.com) writes:
> > Thank you very much for the insight!! More followups:
>
> > For this query/purge idead:
> > SELECT @loadid = MAX(LoadID)
> > FROM loads
> > WHERE CreateDate < dateadd(SECOND, -40, CURRENT_TIMESTAMP);
> > DELETE loads WHERE LoadID <= @loadid;
>
> > Why would i need an index on CreateDate,LoadiD ? Is it just for the
> > first query, or would the DELETE actually need it too?
>
> Only for the first query, obviously.
>
> > I'd be tempted to do the first query with READ_UNCOMMITTED transaction
> > level, then do the DELETE on another default transaction isolation level
> > connection.
>
> Most likely, it will scan the table which is not good for overall
> performance. But, yes, from a purely functional standpoint, you can
> run with READ UNCOMMITTED.
>
> > One other thing that i don't understand - whats with the DESC
> > indexes? I would understand for queries where you'r doing a DESC
> > orderby, but what other purpose to they serve?
>
> For the index on (Type, LoadID), I added DESC since you had that in
> the ORDER BY clause. For the suggested index on (CreateDate, LoadID)
> I will have to admit that I don't for sure that it's useful. But I
> was think that the optimizer would come in from right, and it would
> be good if the first row with the matching CreateDate and the good
> LoadID.
>
> --
> Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

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? 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.
Theses fields aren't updated though, so i think i'm ok.

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...

[Back to original message]