Thanks everyone. I've tried the different ideas and run them against the queries to see what produces the best result.

From all of the testing this has proven to be...

Craig,

You are correct in that the main_ref table is being used to getting a subset of records from table main. The main_ref table is one of several tables that...

Thank you both for your responses. I misspoke on the ID using the NEWID it is actually using the NEWSEQUENTIALID as the default value. For the foreign key...

Found the issue. One table has two indexes in the partition however the table itself was not in the partition. I moved the indexes out of the partition...

SQL 2008 R2 SP1