Most everything you need to know and more is here:  http://www.sommarskog.se/dyn-search.html. 

Sorry, I wasn't very clear in my post!  I leave the noted amount of ram available for stuff OTHER than sql server.  So for a 12GB box, I set sql...

It is usually right to set an upper limit to the ram sql server can use.  There is no magic number, however, but I use the following rough formula:  for...

. . . correction - OF the supervisor!  LOL

I highly recommend an upgrade to the supervisor! 

I would do two queries.  The first is a simple SELECT COUNT(*) using all of the rest of the query (except reduce derived tables to the bare minimum fields required...

SET SHOWPLAN_TEXT ON will work. 

But if the data is different, why would you expect similar execution times??

1) that's not a very efficient query:  distinct, 3 joins (one with <=), correlated subquery and a function call in the where clause.  Oh, and an ORDER BY AND XML...

1) Remote joins are not a good idea.

2) Is the remote table indexed appropriately (both for the join as well as for insert/update activity from local apps against it)?  Are...

Eliminating a join will certainly be more efficient.  As to what is better, I would say that if you control the dictionary table, use the known key values as you...

NO!!!! JUST THE OPPOSITE!  If you DO clean out the %win%\INSTALLER directory you will be HOSED!  And a LOT more than just sql server service packs will barf too.  Virtually...

<SOAPBOX ON>

I have spent about 25 hours so far on the phone with MS tech support because I can't install sp2 on a box.  The problem is that every damn...

1) Anything that loops to kill spids should have an 'escape clause' to avoid getting stuck in an 'infinite' loop.  Some spids can take a LONG time to rollback.  Not...

Personally I think the worst thing from a performance standpoint is if you routinely add columns to existing table it will fragment the hell out of your storage of said...

Please also post execution parameters that cause 1000cpu and others that make it jump to 5000.  It could well be parameter sniffing and one or more situations cause it to...