That's the SQL startup routine.

The error log has the latest events at the beginning and the oldest at the end.

Just after it stops, look for the file ERRORLOG.1 That...

Then ask him why and how he suggest going about it.

It doesn't make any sense to say...

Why do you want to remove the second left join? What's wrong with the query as-is?

If you want that rewritten, you're going to have to give us a bit more...

That doesn't look like the SQL Server error log. It looks like the SQL Agent log, which is not going to gove anything useful regarding teh SQL service itself.

I'm asking...

Switch traceflag 1222 on. That will result in a deadlock graph been written to the error log every time a deadlock occurs. Post the result of that graph here.

DBCC TRACEON(1222,-1)

Block...

How big are those indexes? (how many pages)

Heaps, clustered index or nonclustered index?

X is an exclusive lock and U is an update lock. Neither will be taken by a select unless there is a locking hint.

Can you post the entire deadlock graph?

Please post new questions in a new thread in future.

If you look while the query is running, there will be at least one thread that doesn't have a cxpacket, that...

You left out "pay for it, attend the 4 weeks of...

There's enough information around for indexing strategies and the like. My blog's got some, all of the books I mention have lots on that. That's not what I'd call deep...

What's about unique indexes? Do you want to drop those as well?

You don't need the distinct in the outer query.

What's the last three entries in the SQL error log?

And previous backups? You know, the ones you've been taking on a daily or weekly basis since you became responsible for this database?

Query sys.procedures and use the OBJECTPROPERTY function with the 'IsEncrypted' property.