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

To cache or not to cache

In general, caching and reusing query plans is a good thing, and writing T-SQL code that encourages plan reuse is recommended.

In some cases, such as with a reporting or Online Analytic Processing (OLAP) workloads, caching queries might make less sense. These types of systems tend to have a heavy ad hoc workload. The queries that run are typically long-running and, while they may consume a large quantity of resources in a single execution, they typically run with less frequency than OLTP systems. Since these queries tend to be long-running, saving a few hundred milliseconds by reusing a cached plan doesn't make as much sense as creating a new plan that is designed specifically to execute that query. Spending that time compiling a new plan may even result in saving more time in the long run, since a fresh plan will likely perform better than a plan that was generated based on a different set of parameter values.

In summary, for most workloads in SQL Server, leveraging stored procedures and/or parameterized queries is recommended to encourage plan reuse. For workloads that have heavy ad hoc queries and/or long-running reporting-style queries, consider enabling the Optimize for Ad hoc Workloads server setting and leveraging the RECOMPILE hint to guarantee a new plan for each execution (provided that the queries are run with a low frequency). Also, be sure to review Chapter 8Building Diagnostic Queries Using DMVs and DMFs, for techniques to identify single-use plans, monitor for excessive recompilation, and identify plan variability and potential parameter-sniffing issues.

主站蜘蛛池模板: 都兰县| 特克斯县| 阿鲁科尔沁旗| 梓潼县| 曲麻莱县| 仲巴县| 华阴市| 屏山县| 泽库县| 黄平县| 兰坪| 安顺市| 黑龙江省| 沂源县| 泊头市| 丹江口市| 西峡县| 松滋市| 深水埗区| 井冈山市| 津南区| 黄石市| 梁山县| 城口县| 绍兴市| 监利县| 平陆县| 聊城市| 辽宁省| 大港区| 吉安市| 阿图什市| 百色市| 襄樊市| 兴文县| 博白县| 渭源县| 浮山县| 麻栗坡县| 桃源县| 永宁县|