官术网_书友最值得收藏!

  • Learn T-SQL Querying
  • Pedro Lopes Pam Lahoud
  • 632字
  • 2021-06-24 14:38:18

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.

Keep in mind that hints force certain behaviors with T-SQL statement optimization and execution. Microsoft recommends that hints are thoroughly tested and only used as a last resort. Hinted statements must be reviewed with every upgrade to a new major version to determine whether they are still needed, as new versions may change behavior, rendering the hint unnecessary or even harmful.

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 13Managing Optimizer Changes with the Query Tuning Assistant, we will also cover a tool that can be used to discover such hints.
主站蜘蛛池模板: 乐至县| 拜城县| 丰县| 昆明市| 甘孜| 丰镇市| 旬阳县| 偏关县| 中宁县| 得荣县| 正镶白旗| 合作市| 白沙| 木兰县| 中西区| 焦作市| 罗城| 白银市| 广西| 德惠市| 秦安县| 辉南县| 汤原县| 武穴市| 贵定县| 新宁县| 珠海市| 永年县| 通辽市| 铁岭市| 波密县| 白山市| 资源县| 阳江市| 博罗县| 辽宁省| 凤阳县| 西城区| 漳浦县| 乌苏市| 福贡县|