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 the hint you're no longer getting a timeout. It's still a horrible plan - you're reading the same tables again and again. If you post up the view definitions, it might be possible to work through them to generate a cleaner query with less reads of the same tables.
Thanks Chris, I agree the plans are horrible, and some of the joins in the views are horrendous, unfortunately it is a 3rd party application and I am stuck with the queries that are being ran, also the queries work fine on another instance (which is the actual current live system). They also work fine on the slow system when the instance has been restarted.
Sean Pearce (7/8/2013)
Both plans are timing out, but one in a second and one in 10 minutes.<QueryPlan DegreeOfParallelism="0" MemoryGrant="5968" CachedPlanSize="1632" CompileTime="659453" CompileCPU="649780" CompileMemory="715616">
<QueryPlan DegreeOfParallelism="0" MemoryGrant="7296" CachedPlanSize="1720" CompileTime=" 1183" CompileCPU=" 1055" CompileMemory=" 52024">
It is possible the slow one is due to a request for automatic statistics maintenance. If this is the case you could try setting Auto Update Statistics Asynchronously.
Thanks Sean, what do you mean both plans are timing out? I do get results on both systems. Thank you for your suggestion, I tried setting auto update stats asynchronously, and I also tried turning off auto stats updates but didn't see any results immediately.
I am going to try installing the latest cumulative update for sql server 2008r2 SP2 just in case, at the very least it will rule this out. Though I can't see my specific problem listed in any of the cumulative updates.