You really get a feel for the performance downside of dynamic SQL if you develop web applications.
In most cases the difference in cost between dynamic and static SQL is very small, but multiply this up by 100,000 users and all of a sudden you have a massive load on your servers.
To the gentleman who found that SQL with a lot of ORs in his WHERE statement made dynamic SQL faster than compiled SQL.
Look at the execution plan, OR statements tend to force table scans. If possible use UNION queries with selects against indexed columns. Although long winded the compiled query will run faster.
Secondly let us suppose you have three tables, all linked on a single common field. A human being will join A to B and B to C knowing that A is implictly joined to C. SQL doesn't know this. You may find that explictly stating the A=C join (although long winded) allows SQL to choose a better execution plan.
Thirdly, watch what you try and bring back from a query and which table you get it from.
A clustered index holds the data in its leaves where as a non-clustered index holds a pointer to data in its leaves.
If you want to retrieve the common field shared between A, B and C then if that common field is in a clustered index on table A, make sure that you retrieve the data from table A. This will be quicker than getting it from B or C.
Ditto watch out for the dreaded SELECT * FROM. This will force a table scan, which is inherrently slow. By carefully specifying a limited number of fields you can improve performance.