>>but change this one index and it all goes down the gurgler for no apparent reason.

Maybe it simply isn't apparent to you. You have changed the information and options available to the optimizer in a very complex construct. It does what it can with that information.

>>We can't expect that the optimiser will _always_ find a good plan, but when it doesn't it would be great to be able to feed it some hints to nudge it in the right direction.

But it seems that the hints are exactly that - only hints which may be ignored - and what I'm trying to find out is whether there is some way to do this more reliably rather than hint and pray, ie: am I missing something ?

I have tried the hints which would be logical here with no success.

hints are not a panacea, and again just because they may seem logical to you does not mean they will be so 'logical' to the optimizer engine. 🙂 BTW, have you tried unwinding all of the views in your query and maing them into a single statement and trying to work from that from a 'hint' perspective?

>>Is there any way of giving the optimiser a bit more time to work out its plan ?

Not to my knowledge

>>I realise the temp tables are a viable workaround (and one I have used in places), but this is for reporting and it means rather than simply constructing a query for a report I have to deal with instantiating and dropping possibly several temp tables with dynamic names (since I have multiple sessions and each name needs to have the sessionid added to the end). It raises the complexity of things greatly and given I have about 200 reports in the database, you can understand what this means in terms of time.

Actually I don't understand this. Why do you need dynamic temp table names with the sessionid added to the end of the names?? You can execute the same code in 50 different SSMS windows or 50 different calls of the same sproc at the same time without any temp table name collisions.