Now that this has made it through testing and pushed out to production I'll give you the results. Adding the LOOP join hint to each of the RIGHT JOINS...

I am sorry to hear of your data loss. It is standard operating procedure at my company to always take a backup of any database, live or test, before...

Jeff,

Thanks for your reply.

No, I have not tried using that between executions of the query. I will do so in a bit and let you know what...

Yes, I meant tuning advisor. As soon as I posted that I realized my mistake.

I am doing my testing on my local machine on a restored backup...

Here are the existing indexes. For some reason I thought I had already included them in my original post.

The index you asked about was actually suggested by the query...

Jeff, I'll google your suggestion now.

GilaMonster, attached are the execution plans for both queries. The main difference between the two is with index seeks changing to scans for tblPolicy...