建议的SQL Server针对Ad Hoc工作负载进行优化

几个月前，我建议以下设置应该是大多数SQL Server实例的默认设置：

将并行度的成本阈值设置为50

如果启用了轻量级池，则禁用它

如果启用，则禁用优先级提升

设置优化特设工作负载来启用

将最大服务器内存（MB）设置为与Jonathan Kehayias算法一致的自定义值

将备份压缩默认设置为启用

设置节能设置在Windows平台上的高性能如果可能的话

提供根据需要刷新计划缓存的选项

在接下来的几篇文章中，我将深入了解原因。本周，我们将研究如何针对临时工作负载进行优化，这更多是内存优化功能而非性能功能。

计划缓存

非常简单，SQL Server中的内存在计划缓存中的查询计划和缓冲池中的数据之间划分（对于本文范围之外的内存还有其他用途）。

所有查询计划都占用计划缓存中的空间。有一些方法可以在它们存在后冲洗它们，包括但不限于：

SQL Server可能会使用最少使用的算法清除旧计划

我们可能会发出一个手动

DBCC FREEPROCCACHE

命令来清除缓存中的一个或多个计划

我们可能会安排一个常规的SQL代理作业来清除所有计划

配置更改可能会刷新整个计划缓存

存储过程重新编译可能会强制刷新旧计划

统计信息更新和索引重建可能会强制刷新计划

代码更改（如函数或视图）可能会强制刷新计划

ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE

在SQL Server 2016或更高版本上发出命令

一次性计划

术语“ad hoc”表示“根据需要”，在SQL Server的情况下，它指的是一次性查询计划，这意味着为特定查询生成计划并且从不再使用。一次性计划在使用ORM的数据库支持的应用程序中很常见（对象关系映射工具，如Entity Framework，Dapper，Hibernate，NHibernate，LINQ to SQL等等），以及T-SQL代码的情况是使用字符串连接生成的。

这些一次性使用计划需要内存，但是一旦运行了查询，它们就会继续坐在计划缓存中，加起来我们称之为计划膨胀。由于这些计划永远不会再次使用，我们可以安全地从缓存中删除它们。问题变得 - 特别是在繁忙的实例上 - 我们如何有效地做到这一点？

强制参数化

解决计划膨胀的方法之一是启用强制参数化。此设置强制T-SQL查询变为参数化。举例来说，它表示此查询包含文字值：

SELECT col1, col2, col3

FROM table

WHERE col1 = 256

变成以下：

SELECT col1 , col2 , col3 FROM table WHERE col1 = @i

我们在这个人为的例子中看到的第一件事是原始查询中的空格（换行符和空格）被压缩，以便查询在一行上，并且在包括逗号在内的所有内容周围放置单个空格。最后，WHERE条件被参数化。这样做会积极地强制查询采用一致的格式，以便更有可能重用现有计划。

强制参数化的主要问题是称为参数嗅探的功能。如果我们的数据以任何方式出现偏差，并且SQL Server根据数据的分布方式提出了特定的查询计划（假设它执行嵌套循环连接而不是散列匹配），即使对于查询，该计划也将被重用不同的连接类型会更好。Erik Darling在这篇文章中详细介绍了一些细节。

没有实际数据均匀分布，因此不建议将强制参数化作为默认设置（但这是一个重要的注释）并不意味着它没有用处。如果您发现强制参数化很有用，请使用它。

此外，如果您的数据库处于兼容模式150或更高（在SQL Server 2019和Azure SQL数据库上可用），则此参数嗅探问题在很大程度上通过自适应连接得到缓解。在SQL Server 2017上，可以使用Kendra Little 写的过滤列存储索引hack强制自适应连接。

输入Ad Hoc Caching

为ad hoc工作负载配置设置启用优化将减少第一次执行时所有查询计划使用的内存量。存根不存储整个计划，而是存储在计划缓存中。一旦该计划再次执行，那么只有完整的计划存储在内存中。这意味着在第二次执行时多次运行的所有计划的开销很小。

在官方文件有这样一段话：

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

在启用此设置的情况下，一次性使用计划会占用更少的内存，并且在绝大多数使用ORM或字符串连接的基于数据库的应用程序中生成T-SQL查询（这是我最真实的SQL Server实例）这是一个“一劳永逸”的配置选项，可以在重要的地方提供更多内存：缓冲池。

摘要

强制参数化可能很好，特别是在SQL Server 2019上，但并不是每个人都可以访问它。与此同时，为临时工作负载启用优化将减少计划缓存中一次性计划的内存占用，并使系统运行更加清洁。