Knobs for query optimization

As advanced as the query optimization process is, inefficient plans are still a possibility, which is why a database developer can use hints in the T-SQL statement and guide the Query Optimizer toward producing an intended plan. There are several classes of thoroughly-documented query hints that affect query optimization, and it is important to call out a few that can be useful when troubleshooting a query performance issue, some of which we will use in upcoming chapters.

Let's look at some available hints for the Query Optimizer:

• FORCE ORDER: This is a hint that will prevent any join-reordering optimizations and has a tangible impact on the query optimization process. When joining tables or views, we discussed in the Quick plan section how join reordering is driven by the goal of reducing row count flowing through the operators in a query plan as early as possible. There are edge cases however, where join reordering may negatively affect the search for a good-enough plan, especially when estimations are based on skewed or outdated statistics. If the developer knows that the join order, such as it was written in the T-SQL statement, should be efficient enough, because the smaller tables are already used upfront to limit the row count for subsequent table joins, then testing the use of this hint may yield good results in such scenarios.
• MAXDOP: This hint overrides the system-wide Max Degree of Parallelism (MAXDOP). Depending on its setting, this hint can affect parallel plan eligibility. For example, if a query has excessive waits on parallelism, using the MAXDOP hint to lower or remove parallelism may be a valid option.
• NOEXPAND: This hint directs the Query Optimizer to skip access to underlying tables when evaluating an indexed view as a possible substitute for part of a query. When the NOEXPAND hint is present, the Query Optimizer will use the view as if it were a table with a clustered index, including automatically creating statistics if needed. For example, if a query uses an indexed view that is being expanded by the Query Optimizer and this results in an inefficient query plan, the a developer can include the NOEXPAND hint to make the Query Optimizer forcibly evaluate the use of an index on a view. Note that Azure SQL Database, while sharing the exact same database engine code, doesn't require this hint to automatically use indexed views.
• USE HINT: This hint is not a single hint, like the other query hints, but rather a new class of hints introduced in SQL Server 2016. Its goal is to provide knobs to purposefully guide the Query Optimizer and query execution toward an intended outcome set by the developer. Every version of SQL Server since 2016 has introduced new USE HINT hints, and the list of supported hints can be accessed using the dynamic management view: sys.dm_exec_valid_use_hints. Hints included here can change some Query Optimizer model assumptions, disable certain default behaviors, or even force the entire Query Optimizer to behave as it would under a given database compatibility level. There are many uses for these hints, depending on the query performance troubleshooting scenario that database professionals may face, and we will look further into some of these in upcoming chapters. In Chapter 13, Managing Optimizer Changes with the Query Tuning Assistant, we will also cover a tool that can be used to discover such hints.