ROFL-ROFL-ROFL!!

In my defense I note that the OPer is both a newbie and also seemed quite happy with his proffered solution, so I didn't want to TOTALLY deflate him with...

I missed a VERY important statement in the OP: "...I am planning a 3GHz Xeon with hyperthreading and..."

If only 1 cpu, this baby will be a TOTAL DOG. ...

To address the original post, if you have each of 4 sql instances take 2GB of RAM on an 8GB box, what is the OS going to use?? I...

***********************

select top 1 CampaignID from

(

select top 1 CampaignID,SentTime DT from mttransactions where ClientNumber = @CN and RouteID = @RID order by SentTime DESC

union

select top 1 CampaignID,ReceiveTime DT from motransactions where...

1) Possibly there is some foreign key and cascade delete between the two tables?

2) The statements shown are simply the final piece of the deadlock puzle. Somewhere else up...

It is obviously physically impossible to process many if not most 'natural keys' as efficiently as surrogate ones, therefore your pining about this 'evil' is to no avail. At...

1) >>Comments are not necessarily free. They can be costly at maintenance time. There are many occasions when the code is changed and the comments are not...especially when the changes...

IIRC, this is one of the worst things you can do with a UDF. It essentially equates to RBAR (Row By Agonizing Row) processing under the covers - as...

Again a bad example from a performance perspective.

1) Using a bigint vice tinyint in this case requires 7 extra bytes of storage and 7 extra bytes to chew through on...

I will go on record as strongly disagreeing with the stuff espoused by the Enums article (and even more strongly with using CLR to implement them). Comments cost nothing...

Dave, I absolutely LOVE your Signature Phrase!!!!

Sounds like you are new to performance monitoring. I strongly recommend getting a consultant for an hour or two of time to connect up and help you out. ...

Kenney - was your problem on a 6.5- sql server installation, or a box without battery backups for the server, disk subsystem and raid controller cache? It was my...

tempDB is a good unexplored avenue here actually. MS recommmends one file per CPU for tempdb, preferably on separate I/O systems. Fragmentation could be an issue here too...

Unfortunately all of this stuff about drives and files only gets to the heart of improving overall performance. Your specific issue of having a poor query performance all of...