RE: Dead Zune Club

One brute-force approach might help. This is controversial, but may help
significantly.

If the data you're dealing with doesn't require absolute consistency at all
times (e.g., financial or medical data), you might consider a policy of
using WITH(NOLOCK) in all queries unless you have a specific reason not to.
Simply eliminating all of the read locks can go a huge distance to improving
high-volume performance.

The downside to doing this is that there's a risk that you'll get
inconsistent results. If another process is in the process of changing data,
you may see the results of its *partial* update. In real life, I've never
experienced a problem with that, at least as far as anyone has noticed. But
the possibility is there.

My policy is that all SELECT statements get WITH(NOLOCK), unless they're
part of a unit of work that is using the actual returned data to make
updates.

Which reminds me. While I think that SQL Server is a great DB, and find SSMS
a productive environment, I have to agree with you that T-SQL is an awful
language. 

Start with the overloading of the "WITH" keyword, and how it's constantly
re-used in different contexts to shoehorn in new features. Then there's the
bizarre syntactic differences that force you to choose between temp #tables
and table @variables. And, you can treat the results of a stored procedure
as the source for an INSERT (but you can't use it that way in any other
context, and you can't nest this feature (which is an undocumented
limitation), thereby destroying encapsulation). And, well, don't get me
started...