This chapter describes query processing options that affect the optimizer's choice of join order, index, I/O size and cache strategy.
If you have turned to this chapter without fully understanding the materials presented in earlier chapters of this book, be careful when you use the tools described in this chapter. Some of these tools allow you to override the decisions made by SQL Server's optimizer and can have an extreme negative effect on performance if they are misused. You need to understand their impact on the performance of your individual query, and possible implications for overall performance.
SQL Server's advanced, cost-based optimizer produces excellent query plans in most situations. But there are times when the optimizer does not choose the proper index for optimal performance or chooses a suboptimal join order, and you need to control the access methods for the query. The options described in this chapter allow you that control.
In addition, while you are tuning, you want to see the effects of a different join order, I/O size, or cache strategy. Some of these options let you specify query processing or access strategy without costly reconfiguration.
SQL Server provides tools and query clauses that affect query optimization and advanced query analysis tools that let you understand why the optimizer makes the choices that it does.
Note: This chapter suggests workarounds to certain optimization problems. If you experience these types of problems, call Sybase Technical Support.