I have a bunch of stored procedures with queries using multiple joins running fine in terms of performance in my development enviornment (SQL 2005 64bit, 4 gig RAM, Dual Xeon).  When I push these same stored procedures to production (SQL 2005 64bit, 12 gid, Quad CPU, SAN attached) these queries perform horribly (some to the point of never finishing).  When I run the execution plan there are no "bad" operations and no single task hogging the majority of time.  When I use the tuning advisor it says it will give me a 99% increase but applying the recommendations never has an impact.  The only thing I have found to regain the lost performance is to break the joins up to use temp tables.  Example:

 original (select [field list] from [t1] join [t2] join [t3] join [t4] join [t5] join [t6] join [t7] where [conditions])

converted (select field list into #tmp1 from [t1] join [t2] join [t3] - select [field list] from #tmp1 into #tmp2 join [t4] join [t5] join [t6] - select [field list] from #tmp2 join [t7] where [conditions])

Does anybody have an insight into why I would see this behavior or recommendations as to other ways to troubleshoot?

Couple of things you need to check to find the real Issue:

• Are statistics up-to-date in Production environment.?
• Do the Indexes in good state. (No fragmentation etc)
• Is the table having less fragmentations
• Is there any difference in the execution plans (between production and Dev)
• can you check whether your queries create a blocking to other tasks?
• Is the database placed in the best performing hard disks?

Temporary tables help you to improve the performance by reducing the locks of 'Live" tables. Whne you join the temporary tables, there will be no locks as they are personal to you. Also as Temp tabels are created in tempdb, they can reduce the load on the live servers.