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

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.

主站蜘蛛池模板: 信宜市| 浏阳市| 阜城县| 含山县| 泸定县| 永宁县| 泾阳县| 宜城市| 斗六市| 赤水市| 安康市| 萝北县| 乾安县| 榕江县| 黄石市| 延津县| 商洛市| 南京市| 仁怀市| 伊金霍洛旗| 开江县| 松桃| 桂林市| 连云港市| 沙坪坝区| 安岳县| 板桥市| 瓦房店市| 芒康县| 阜南县| 洛扎县| 夏邑县| 黔江区| 瑞安市| 郴州市| 漳平市| 高雄县| 米脂县| 花莲市| 冕宁县| 正宁县|