just to let you know the tempdbs didn't sort it, the restart sorted it temporarily but then the issue returned. Eventually tracked it down to trace flag T8780 being turned...

Sorted! Your comments led me to the following website, I couldn't understand why the query was taking so long, and then timing out.

http://mssqlwiki.com/2012/10/07/optimizer-timeout-or-optimizer-memory-abort/

quote from the site below

"We can...

i see what you mean now, thanks for that what on earth could be causing the optimiser to take ten minutes and only then timeout! ridiculous. hopefully cumulative update will...

ChrisM@Work (7/8/2013)

---

The join hint restricts the number of possible plans which the optimiser has to evaluate to the point where it has time to pick a suitable plan, hence with...

I should mention that the objects being referenced in the initial query are views and not tables. These views use joins too, some quite complex which explains the size of...

Thank you, but i am running your revised query and it too is taking a long time. It is worth mentioning that I have since added a join hint to...

Thanks for your response. I am running the same query each time and not changing the parameters so don't think parameter sniffing is the problem. This query has been running...

FYI, solved this by adding 7 extra tempdb files to match the 8 cores, then restarted the sql server. Now the query plans match exactly, and the query returns in...

update on this, restored the backups to another sql server with exactly the same OS and SQL server version and SP, and the query returns in seconds, even on the...

mayurkb I switched parametrization to forced on the offending database, and now when I run the query (after it's initial 10 minute run into the cache), even if I change...

The amount of data in the databases should be very similar, the database on the slower of the servers was last updated by an automatic job early this morning before...

They are running sql server 2008r2 standard edition, though the quicker performer is at sp1, the slower at sp2.

Yes it takes a very long time to compile, once I...

I have attached the execution plan from the slow performing server, and from one of the quicker performing servers. (the query plans for the 2 quicker performing servers live and...

Yes sorry, I am at home now so do not have the actual query or plan to hand. I will add it to the post when I log in later....

Thanks for this, put me on the right track. but I had to change the database in the drop down to MSDB rather than master or any other. my query...