As described in "Optimizing Joins", SQL Server optimizes joins by considering permutations of four tables at a time. If you suspect that an incorrect join order is being chosen for a query that joins more than four tables, you can use the set table count option to increase the number of tables that are considered at the same time. The syntax is:
set table count int_value
The maximum value is 8; the minimum value is 1. As you decrease the value, you reduce the chance that the optimizer will consider all the possible join orders.
Increasing the number of tables considered at once during join ordering can greatly increase the time it takes to optimize a query.
With SQL Server's default four-at-a-time optimization, it takes 3,024 permutations to consider all the join possibilities. With eight-table-at-a-time optimization, it takes 40,320 permutations.
Since the time to optimize the query increases with each additional table, this option is most useful when the actual execution savings from improved join order outweighs the extra optimizing time.
Use statistics time to check parse and compile time and statistics io to verify that the improved join order is reducing physical and logical I/O.
If increasing table count produces an improvement in join optimization, but increases CPU time unacceptably, rewrite the from clause in the query, specifying the tables in the join order indicated by showplan output, and use forceplan to run the query. Your routine performance maintenance checks should include verifying that the join order you are forcing still improves performance.