The worst part about this is that the prod server is BRAND NEW and this is the first of our plethora of SQL apps to consolidate onto the new hardware....

Thank you for the idea, I tried OPTION(MAXDOP 1) again and it has no affect on the slow query execution plan nor execution time.

According to the MS Component Checker:

Test is MDAC 2.8 RTM

Prod is MDAC 2.8 RTM

I think that is up to date, thanks for the idea!

Thank you for the suggestion Igor! I searched that syscacheobjects table and found about 15 or 20 items similar to my query. I cleared the cache and then verified the...

Thanks for everyone's consideration. I might put a support call in to MS because I can't resolve it. Facts:

1. Original Excel query always runs fast in test server, ~5 seconds
2. Original Excel...

Try creating the table at least one time in the target database. That way the job can happily delete it and recreate it. If you recently created this table in...

Slight variation on Mike's answer...

Use Profiler to capture typical queries being run. Take the most used and run them in query analyser with SHOWPLAN_TEXT turned on. The results should show...

You might create a custom SQL Server Agent performance condition alert with notification that dumps the info you are looking for while it is happening.

Thanks for the tip, but no dice Noel. I tried that once before on all 5 joined tables, but I did it again just now WITH FULLSCAN to no avail.

Aaron

I ran this and then re-ran the prod server query. I stopped it after 3 minutes, doesn't seem to have helped. I set it back to 0.

Thanks for the idea.

Thanks Steve, I didn't think of using profiler to simulate usage. If the query optimizer uses utilization as a statistic then it could be affecting. However, I can't the slow...

Prod has 2 cpus, Test has 1.

Difference in execution plans (excerpt):

Prod (15 minutes)

...

Someone may know better than I, but I think you are looking at a stored procedure for that one. You might be surprised how fast it could run.

You would...

Thanks for your help Igor. My test and production servers are nearly identicle, except test has less ram. They are the same os and SQL versions. Even though the FROM...

The slow query becomes fast by converting the joins to INNER JOINS, and returns the same rows. Any ideas why? I feel there must be something different in my dev/prod...