几个月前,我建议将以下设置作为大多数SQL服务器实例的默认设置：

• 设置 并行性的成本阈值 到 50
• 使残废 轻量级池 如果它被启用
• 使残废 优先级提升 如果它被启用
• 设置 针对特定工作负载进行优化 到 使能够
• 设置 最大服务器内存（兆( 敬一杯 自定义值 与乔纳森 凯亚斯的算法一致
• 设置 备份压缩默认值 到 使能够
• 设置 省电设置 在窗子上到 高性能 如有可能
• 提供选择 刷新计划缓存 根据需要

在接下来的几篇文章中,我将深入探讨 为什么。 本周,我们来看看启用 针对特定工作负载进行优化, 这与其说是性能特性,不如说是内存优化特性。

很简单，SQL Server中的内存被划分为计划缓存中的查询计划和缓冲池中的数据（内存的其他用途不在本文讨论的范围内).

所有查询计划都会占用计划缓存中的空间。 一旦它们出现,有多种方法可以冲洗它们,包括但不限于：

• SQL服务器可能会使用最少使用的算法清除旧计划
• 我们可能会发出手动命令,从缓存中清除一个或多个计划
• 我们可能会安排一个常规的结构化查询语言代理作业来清除所有计划
• 配置更改可能会刷新整个计划缓存
• 存储过程重新编译可能会强制刷新旧计划
• 统计信息更新和索引重建可能会强制刷新计划
• 函数或视图等代码更改可能会强制刷新计划
• 在SQL Server 2016或更高版本上发出命令

术语”临时”的意思是”根据需要“，在SQL服务器的情况下,它指的是单次使用的查询计划,这意味着计划是为特定的查询生成的,并且不会再次使用。 单次使用计划在使用表单的数据库支持的应用程序中很常见（对象关系映射工具,如实体框架、Dapper、Hibernate、NHibernate、LINQ到结构化查询语言等)，在使用字符串连接生成T-SQL代码的情况下也很常见。

这些单次使用的计划需要内存,但是一旦运行了查询,它们就会继续驻留在计划缓存中,这就是我们所说的 计划膨胀。 由于这些计划再也不会被使用,我们可以安全地将它们从缓存中删除。 问题就变成了—尤其是在繁忙的情况下—我们如何高效地做到这一点？

解决的方法之一 计划膨胀 是启用强制参数化。 此设置强制将T-SQL查询参数化。 举例来说,这意味着这个包含文字值的查询：

变成如下：

在这个人为的例子中,我们看到的第一件事是原始查询中的空白（换行符和空格(被压缩,这样查询就在一行上,并且在包括逗号在内的所有内容周围都有一个空格。 最后，在哪里条件被参数syncnavigator 化。 这样做是积极地将查询强制成一致的格式,以便有更好的机会重用现有的计划。

强制参数化的主要问题是一个称为 参数嗅探。 如果我们的数据以任何方式倾斜,并且SQL服务器根据数据的分布方式提出了一个特定的查询计划（假设它执行嵌套循环连接而不是哈希匹配)，那么该计划将被重用,即使对于不同连接类型更好的查询也是如此。 埃里克 达林在这篇文章中谈到了一些细节。

没有真实世界的数据是均匀分布的,所以不建议将强制参数化作为默认设置,但是（这是一个重要的注意事项(这并不意味着它没有用。 如果 你们 发现强制参数化是有用的,请使用它。

此外,如果您的数据库处于兼容模式150或更高（在SQL Server 2019和蓝色SQL数据库上可用)，这 参数嗅探 自适应连接在很大程度上缓解了问题。 在SQL Server 2017上,可以使用过滤列存储索引黑客来强制自适应连接肯德拉·利特尔写道。

启用 针对特定工作负载进行优化 配置设置将减少所有查询计划使用的内存量 第一次 他们被处决了。 不是存储完整的计划,而是在计划缓存中存储一个存根。 一旦该计划再次执行,整个计划才会存储在内存中。 这意味着,对于在上运行多次的所有计划来说,开销都很小 第二次执行。

官方文件有这样的说法：

如果单次使用计划的数量占用了OLTP服务器中很大一部分的数据库引擎内存,并且这些计划是临时计划,请使用此服务器选项来减少这些对象的内存使用。

在启用此设置的情况下,单次使用计划消耗的内存要少得多,并且在绝大多数使用ORM或字符串连接来生成T-SQL查询的基于数据库的应用程序中（这是我见过的最真实的SQL服务器实例)，这是一个一次性配置选项,它将在重要的地方提供更多的内存：缓冲池。

强制参数化可能很棒,尤其是在SQL Server 2019上,但不是每个人都可以访问它。 同时,启用 针对特定工作负载进行优化 将减少计划缓存中单次使用计划的内存占用,并保持系统运行更干净。

请在下面的评论中分享您的内存优化技巧。

cs