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

1) I am at a loss to make a comment on the overall architecture here!!  Good luck with it . . . 🙂

2) As for XML being overhead in a...

Take a look at COALESCE in BOL.  You may be able to use it in the WHERE clause (and other places as well) to get exactly the results you desire.

1) Get (MUCH) bigger hardware.

or

2) Use the query governor cost limit to prevent 'runaway' queries from executing.  In conjunction with this you may run a profiler to trap said calls...

The root cause of poor performance of the while-loop-no-tran solution is log flushes.  REALLY BAD from a performance standpoint.  Adding in explicit trans holds off on the flush until complete...

David Reed:  I had looked into the MCA and decided not to pursue it due to cost and the month+ onsite at Redmond.  I was hoping that SQL Ranger was...

Hey, how can I get to be one of those SQL Ranger type dudes?!?! 

1) When you want help tuning a query, ESPECIALLY a complex one such as this, you simply MUST include table definitions (to include indexes and keys, etc) and table data...

If I were the OPer, I would look for what has changed.  If something worked and now it doesn't, SOMETHING has been altered somewhere, somehow.

I would check name resolution stuff...

Isn't Enterprise Edition 5 times as expensive as Standard??  That could be one reason for not upgrading! 🙂

I actually don't know the answer to the 4CPU or 4Core question.  I...

I thought that table variables, while created as an object in tempdb like a temp table, contain all their data in memory given that sufficient memory exists - at which time...

1) All but one of the numerous situations I have tested using hyperthreaded CPUs on SQL Server boxes resulted in OFF being faster - often significantly so.  The remaining situation...

That's a bit too broad of a generalization, Irfan.  Table variables come with significant limitations that make them less performant than temp tables (when they are both used optimally).  Also,...

The actual overhead of the variables is essentially negligible, even for hundreds of them I would bet.  They are RAM and CPU constructs, and thus operate in the micro- and...