Query Optimization - Limit Tables in Join

Years ago, I was called in to stabilize the SQL Server databases for a large internet platform. Among the problems discovered was that there was really no concern for limiting how many tables should be included in a query. It was just assumed that the query optimizer would automagically work, For one table, two tables, or dozens of tables.

Digging through the internal statistics and various DMV's, a few queries were found to be causing most of the problems.  One query in particular was found to have 192 tables as part of the join. Given that a join with 10 tables can have over one million possible join orders, 192 tables was most likely astronomical.  On his blog, Benjamin Nevarez has written about the number of possible permutations as the number of tables in the join increases.  You can find it here:  Optimizing Join Orders.

If you just want a quick overview,
here is a reprint of the table posted in the article by Benjamin Nevarez:

Tables	Left-Deep Trees	Bushy Trees
1	1	1
2	2	2
3	6	12
4	24	120
5	120	1,680
6	720	30,240
7	5,040	665,280
8	40,320	17,297,280
9	362,880	518,918,400
10	3,628,800	17,643,225,600
11	39,916,800	670,442,572,800
12	479,001,600	28,158,588,057,600