I will add one thing (in addition to the very important set max memory already covered).  There is almost NEVER a good reason to shrink a database.  Make it as...

The only issue I had that didn't seem to be well documented in the setup steps was adding the login account used for sql services to the DatabaseMailUserRole in msdb.

To my knowledge neither version of sql server will allow updates to an identity column in a table unless that table has the set identity_insert property ON.

You say you have ruled out MSDTC Security Settings, but I still think that is the problem.  I turned on the top 6 checkboxes (Network DTC Access, Both Client Admin and...

Excessive temporary object creation can cause blocking in tempdb system tables.  I have seen this bring production systems to a halt.  The only solution is to rework your code to...

>>As for why the 10 fold increase in performance, see http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=65&messageid=366109 Yes it was a whole mess of IO that didn't have any reason for occurring.

I knew it!! 

>>I'm having...

Even if you had 1000 of these IFs/CASEs in your sproc, I would not expect more than a millisecond or two difference between the two versions.  You are talking registers...

I can pretty much GUARANTEE that futzing around with differences between IFs and CASEs you presented will be meaningless for improving performance.  Those are CPU issues and thus have a...

Excellent suggestion Gabor!  This is especially important for OLTP environments that include dated inserts (such as ERP systems) because the stats will not reflect the newer rows until a relatively...

Indeed it is 2005.  The = (i.e. inner join) still works.  However, per 2005 BOL reference ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/36b19e68-94f6-4539-aeb1-79f5312e4263.htm

The FROM clause supports the SQL-92-SQL syntax for joined tables and derived tables....

I will add a few "guesses/hints" here too:

1) As already mentioned, fragmentation.  This includes data/indexes inside sql server as well as OS FILE fragmentation.  If this database was created with...

I am still struggling to envision a reason for this behavior (as I am sure you are)!

1) was the use of parallelism the ONLY difference in the estimated (or better...

I just recognized that you have a select * in the query.  Do you REALLY bring back 4.5MILLION records to the front end??  And EVERY COLUMN for those 4.5M records? ...

1) When you dropped/recreated the sproc (without the variable kludge), did you execute it immediately with an appropriate batchid BEFORE executing the SELECT statement in isolation? 

2) If possible (be...

>>Dropping and recreating the proc helps to remove any previous query plans from the proc. Generally its a good practice to drop/recreate as

>>Is this technic any better than...