Linq to SQL produces expression trees. These expression trees are interpreted by the query provider. In the case of Linq to SQL the query provider produces SQL for the SQL Server dialect. The SQL can be optimized. This SQL is sent to SQL Server which then parses and optimizes the execution plan for the SQL (or not if it is already in the procedure cache).
Are you saying that the Linq expression to SQL optimization will replace the SQL Server SQL plan optimization?
No, I'm not saying that Linq to SQL will replace the SQL Server plan optimization; I'm merely pointing out that a well-optimized (and perhaps dynamic, using reflection) query provider could probably generate better, more efficient SQL queries than 99% of developers in 99% of use cases. You might miss out on some potential optimizations simply because you're trying to get the code working right instead of spending hours to shave a few milliseconds off of your query's execution time (plus, nobody's perfect!)
Are you saying that the Linq expression to SQL optimization will replace the SQL Server SQL plan optimization?
The gory details of producing a Linq query provider are covered in this series of blog posts: http://blogs.msdn.com/mattwar/pages/linq-links.aspx