SQL Server 性能调优概述

简介

SQL Server 是一个功能强大且功能丰富的数据库管理平台，支持广泛的应用程序。但是如果查询性能不佳或工作负载遇到死锁、延迟问题和其他服务中断，没有人会关心如何平台不错。他们唯一关心的是应用程序性能。

确保 SQL Server 能够满足其性能要求的最佳方法是，实施一个强大的性能调优策略，该策略将 SQL Server 环境的所有方面都考虑在内。这不仅包括索引和查询，还包括硬件基础设施、服务器、数据库设置、日志、数据文件以及支持环境的任何其他组件。

数据库性能优化的应用场景相当广泛，但SQL语句与业务联系紧密，代码层面的优化可能需要花费相当多的时间与精力。除了代码层面，语句执行层面的优化、更佳的SQL语句使用执行计划、运行在一个稳定高效的环境，同样是高效也更符合运维的一种优化手段。下面我分享一些SQL Server在配置方面的性能优化思路，从CPU、内存、I/O、执行计划等层面，内容包含了最大并行度、资源调控器、查询提示几个功能的介绍与配置方法。

1. 根据性能需求规划硬件

性能问题通常归因于支持 SQL Server 环境的硬件基础结构。对基础架构的规划越好，就越有可能防止与硬件相关的性能问题，这些问题可能会发生在以下任何资源中：

• 计算。SQL Server 是一种资源密集型应用程序，需要足够的处理能力和内存能力来管理数据并驱动其工作负载，无论是事务性、分析性、商业智能、批量负载还是不稳定的混合负载。当处理器或内存跟不上时，应用程序的响应速度就会变慢，甚至可能停止工作。

• 存储。SQL Server 需要能够处理预期数据量的存储解决方案，同时支持访问数据的不同方式。在当今市场上，我们可以从广泛的存储选项中进行选择，但对于 SQL Server 只有一些是实用的。无论我们选择什么，存储解决方案都必须能够提供必要的每秒输入/输出操作 (IOPS)，同时将延迟率保持在最低水平。

• 网络。如果网络无法满足工作负载需求，也会出现性能问题。SQL Server 可能运行良好，查询已针对性能进行了优化，但网络瓶颈可能会导致响应时间过长，从而降低应用程序的速度，甚至导致超时。

在规划 SQL Server 实施时，您必须确保您的基础结构能够提供支持所有工作负载所需的性能。如果您已经实施了 SQL Server 并且遇到了性能问题，您应该评估您现有的硬件以确定它是否可能是您的问题的根源。

2. 设置 SQL Server 实例时考虑性能

与硬件一样，您应该从一开始就考虑性能，这包括您第一次设置 SQL Server 实例时。例如，您可能希望为不同环境（例如开发和生产）部署实例，或者在专用于该实例的计算机上安装 SQL Server。

SQL Server 还包括可配置的设置，您可以修改这些设置以满足特定的工作负载要求。一个很好的例子是服务器内存选项，它允许您配置最小和最大服务器内存量，以及索引创建内存和每个查询的最小内存量。例如，您可能在服务器或群集上运行多个 SQL Server 实例，并希望防止它们相互竞争，在这种情况下，您可能会降低每个实例的最大内存。SQL Server 还提供了许多其他选项，例如与并行性和死锁优先级相关的选项。

更改配置时必须小心。例如，您可能指定大量的最大工作线程数，认为这会提高性能，结果却发现它使应用程序陷入困境。或者，您可能会实施备份压缩，然后意识到该进程正在使用宝贵的 CPU 资源并影响并发操作。在设置跟踪标志时，您还应该谨慎行事。尽管它们对于故障排除性能很有用，但它们也会中断操作，这就是为什么您应该在将它们实施到生产中之前彻底测试所做的任何更改。

3. 在配置数据库时考虑性能

SQL Server 还提供了多种方法来解决数据库级别的性能问题。例如，您可以将日志和数据文件定位在不同的物理驱动器上，这可以消除文件之间的争用，同时适应不同的访问模式。此外，您可能会发现它很有用调整自动增长和MAXSIZE这些文件的文件中的设置，根据您的工作负载。

SQL Server 还允许您在数据库级别配置设置，就像在服务器级别一样。例如，您可以启用或禁用自动创建统计和自动更新统计选项，以及设置异步统计更新。您还可以更改数据库兼容性级别，这在您将 SQL Server 更新到更新版本后会很有用。

在解决数据库级别的性能问题时，您不仅限于用户数据库及其属性。您还可以通过优化tempdb系统数据库来提高性能，这可能会以意想不到的方式影响性能，例如导致元数据争用或对象分配争用。优化数据库的一种方法是将其文件定位在与用户数据库或底层操作系统分开的磁盘上。其他方法包括调整tempdb数据文件的数量或其大小。

4. 设计数据库时要考虑到性能

正确的数据库设计对于确保数据完整性至关重要，但它也可以在性能方面发挥重要作用。例如，规范化数据库可以通过减少冗余数据量来提高性能，这可以简化写操作，在某些情况下还可以简化读操作，尤其是在只涉及一个表的情况下。但是，在某些情况下，如果使用得当，您的查询可能会从一些非规范化中受益。

在设计数据库时，还要确保为您的数据选择最合适的数据类型，例如对于全为两个字符的字符串值，使用char而不是varchar。您还应该考虑实际存储在 SQL Server 中的数据类型。例如，将图像路径存储在数据库中通常比图像本身更好。适当使用外键和其他约束也可以提高性能。

除了表之外，您还应该考虑其他数据库对象如何影响性能。例如，您通常可以通过将这些查询放在视图中然后索引视图（记住最佳索引实践）来避免复杂的查询。另一个例子是存储过程，它通常可以提供比类似查询更好的性能。但是，请确保在创建存储过程时遵循最佳实践，例如在命名过程时包括 SET NOCOUNT ON 子句或不使用sp_前缀。

5. 建立索引来提高性能，而不是让它变得更糟

一些组织，尤其是那些没有经验的 DBA 的组织，经常试图不断向他们的表中添加索引以希望提高性能，结果却发现性能一直在不断恶化。正确的索引需要一种更微妙的方法，一种考虑数据本身和正在执行的查询类型的方法。

正确编制索引的指南非常广泛，太多无法在此涵盖，但请记住以下几点注意事项：

• 在被查询的列上建立索引，尤其是在连接和查询谓词中的那些。
• 在多列上创建索引时，列顺序基于列的查询方式。
• 不要在小表上创建索引，除非你能清楚地展示它们的好处。
• 不要索引唯一值相对较少的列。
• 定期检查索引是否有碎片，然后根据需要重建或重新组织它们。
• 识别未使用或未充分利用的索引并删除它们以避免不必要的开销。
• 根据查询数据的方式对索引进行排序。
• 验证您的查询是否按预期使用索引。

在适当的情况下使用覆盖索引或过滤索引，记住您支持的查询类型。

这些只是在表和视图上创建索引时要考虑的一些注意事项。必须非常小心地使用正确的索引，否则它会严重破坏性能，而不是帮助它前进。

6. 创建查询以最大化性能

您可以采取的提高 SQL Server 性能的最重要步骤之一是优化您的 T-SQL 查询，这是一项如此复杂和细致入微的任务，以至于已经写了整本关于该主题的书籍。勤奋的数据库开发人员需要考虑许多因素，包括以下几点：

• 仅检索您需要的字段并避免在查询中使用 SELECT *。
• 调用数据库对象时包括模式名称。
• 在查询中包含 SET NOCOUNT ON 子句，除非您需要知道受查询影响的行数。
• 避免导致查询引擎不必要地转换数据的隐式转换。
• 在可能的情况下减少每个会话的查询数量，并保持事务简短。
• 了解临时表、表变量和公用表表达式之间的区别，并知道何时使用一种而不是另一种。
• 使用连接而不是相关的子查询。

这些只是优化 T-SQL 查询时要记住的许多准则的一个示例。此外，您的脚本应经过仔细的代码审查并在投入生产之前进行全面测试。开发人员还应该以小的增量修改他们的查询，而不是一次进行彻底的更改，以便他们清楚地了解哪些有效，哪些无效。

7. 使您的 SQL Server 环境保持最新

这种最佳实践似乎不言而喻，但保持硬件、软件和固件最新的重要性怎么强调都不为过。在可能的情况下，您应该考虑更新到更新版本的 SQL Server 以利用与性能相关的新功能。例如，微软在 SQL Server 2019 中引入了内存优化的tempdb元数据，这可以提高严重依赖tempdb数据库的工作负载的性能。

即使您决定不升级到更新的 SQL Server 版本，您仍应定期更新您的 SQL Server 实例和基础 Windows 操作系统，以从最近的任何性能增强中受益。此外，您应该考虑更新任何可能开始降低性能的硬件。即使您不更换硬件，您也一定要保持支持的软件和固件是最新的。

8. 利用 SQL Server 工具优化性能

SQL Server 提供了多种工具来帮助提高性能。任何试图优化 SQL Server 环境的人都应该在有意义的地方利用这些工具，特别是因为它们包含在许可费用中。以下是 SQL Server 提供的一些工具：

• Microsoft 数据库引擎优化顾问 (DTA) 将分析您的数据库并提供有关如何优化查询性能的建议。您可以使用该工具对特定查询进行故障排除、跨多个数据库调整一组查询、管理存储空间以及对设计更改执行假设分析。

• SQL Server 查询存储允许您捕获查询、执行计划和运行时统计信息的历史记录，然后您可以查看这些历史记录以深入了解数据库使用模式和查询计划更改。查询存储可以帮助您识别和修复由计划更改引起的查询性能回归，以及识别和调整消耗资源的查询。

• SQL Server 扩展事件是一个轻量级的性能监视系统，可让您收集识别和解决性能问题所需的数据。创建扩展事件会话时，您可以指定要跟踪的事件以及如何存储事件数据。通过这种方式，您可以准确地收集您需要的有关 SQL Server 环境的信息，然后以最适合您需要的格式查看这些信息。

• SQL Server Management Studio (SSMS) 包括多个用于查看有关查询执行的详细信息的选项。您可以显示查询的实际执行计划，其中包含资源使用指标或运行时警告等运行时信息，或者您可以使用实时查询统计功能查看有关查询执行过程的实时数据。SSMS 还允许您查看有关查询及其相关网络数据包的客户端统计信息。

• SQL Server 提供了种类丰富的动态管理视图 (DMV)，其中许多可以帮助识别性能问题。例如，您可以使用sys.dm_os_wait_stats DMV 查看已执行线程遇到的等待，这可以帮助您诊断 SQL Server 和特定查询的性能问题。SQL Server 提供服务器范围和数据库范围的 DMV。

您越了解如何使用 SQL Server 提供的工具，就越能有效地识别和解决性能问题。但是请注意，某些工具或功能可能不适用于较旧的 SQL Server 或 SSMS 版本。此外，这里提到的工具并不是唯一可用的工具。例如，您可以下载 Microsoft 的数据库实验助手 (DEA)，这是一种用于 SQL Server 升级的 A/B 测试解决方案。

绝对值得深入研究哪些其他工具和功能可以帮助您解决性能问题。甚至错误日志有时也可用于识别潜在的性能问题。

9. 监视，监视，监视

持续监控对于在所有 SQL Server 数据库和实例中提供一致的性能至关重要。但仅仅从您的系统收集遥测数据是不够的。您还必须能够使用收集到的数据来诊断性能问题并找出其根本原因。监控解决方案可以提醒您注意潜在问题，帮助您识别瓶颈，并揭示可能指向特定时间段内发生的性能问题的趋势。

没有硬性规定明确规定您应该监控什么，当然您应该设计监控策略以满足您的特定情况。也就是说，有些领域通常值得您关注。例如，您可能希望监视索引填充因子、碎片和使用情况。您还可能受益于监控指标，例如 CPU、内存、I/O 和缓冲区缓存使用情况，以及与日志和数据文件相关的指标。您还应该考虑跟踪特定于您的查询的指标。

要正确监控您的 SQL Server 环境，您需要一个监控解决方案，该解决方案可以跟踪您需要的指标，而不会影响应用程序性能或数据库操作。该解决方案应该能够跟踪所有相关指标，生成及时的通知，并为您提供快速有效地了解和解决性能问题所需的洞察力。您可能会考虑 Microsoft 的 System Monitor（又名 Performance Monitor），它是免费提供的。

性能调优是一项持续的工作

性能转变不是一次性操作，您可以在早期执行并忘记直到一切崩溃。这是一项持续的工作，需要在 SQL Server 环境的整个生命周期内仔细关注它的所有方面。性能调优包括托管环境的基础架构、访问数据的查询、支持查询的索引、影响性能的服务器和数据库设置，以及介于两者之间的所有内容。

但是，在调整环境时，您应该谨慎行事，在可管理的步骤中进行更改，并在将这些更改实施到生产中之前彻底测试这些更改。最重要的是，不应将性能调优视为事后的想法，而应将其视为管理和优化工作的一个组成部分，从第一次设置 SQL Server 环境开始，一直持续到环境停止使用。

最大并行度（MAXDOP）

介绍

最大并行度是指会话可以使用的最大线程数，对于大批量查询，例如大表的扫描，使用多个线程同时扫描能成倍地提高效率；但是对于小型查询，例如只修改小表里一行的内容，则没必要使用多个线程。

一般情况下，会话最终使用多少个线程是由查询优化器决定的（可以通过option查询子句进行干预）。查询语句提交到SQL Server后会先进行解析，然后进行优化和简化（例如子查询转为对应连接、优先应用筛选条件），生成一系列执行计划，最后根据统计信息计算开销，选择合适的执行计划。最终预估的开销决定了会话使用多少并行度。

在服务器配置选项中，我们能通过“最大并行度”、“并行的开销阈值”两个配置进行调整，最大并行度的默认值是0，即不限制并行度，最大能使用到与CPU核数相等的并行度。但是对于明显有性能问题的系统，则需要考虑调整这个高级选项进行优化：

配置方法

• 检查/配置“最大并行度”设置

• 检查/配置“最大线程数”设置

注意事项

以下列举了一些场景作为参考：

• OLTP系统

单纯的OLTP系统由高并发的小事务组成，不适合使用太高的并行度，可以将最大并行度设置为1，即不开启并行查询；如果调整后明显感觉到执行时间太长，应用反应变慢，则可以逐步提高到2、4、8再进行观察。（对于这类语句执行频繁的小事务，执行计划的选择也是非常重要的优化方向，需要结合语句单独分析）

• OLAP系统

单纯的OLAP系统由只读长事务组成，事务执行时间都较长，例如报表统计、历史数据导出。这类事务的特点是会连接大量表、读取大量数据、进行大量计算，对于语句执行效率来说并行度越高越好。尽管官方文档推荐8核以上的服务器也使用并行度8，但在没达到CPU瓶颈的情况下可以尽可能提高OLAP系统的最大并行度，或者不限制最大并行度。

• 混合系统

实际中更常见的是读写混合的系统，在承载应用写操作的同时也承载一些小型报表的查询，这类系统则需要进行反复的调整以达到最佳的并行度设置：写操作通常开销较小，只会用1个并行度；普通的检索开销也一般不大，使用较低并行度；报表通常开销较大，会使用较高并行度。

在CPU资源有限的情况下，配置最大并行度为1可以保证最关键的写操作能获得足够的资源；但如果读操作需要使用并行来提高效率（maxdop=1时语句执行太慢），可以适当调到2并逐步增加；如果只需要提高那些执行时间很长的查询，可以提高“并行的开销阈值”，只让高开销的查询使用并行。

• “并行的开销阈值”是一个相对值，没有单位，默认是5，只能通过一步步测试调整来选用最佳的设置。
• 最大连接数默认值为0，但不是没上限，而是根据CPU核数递增，官方给出的计算公式为Default Max Workers + ((logical CPUs - 4) * Workers per CPU)。

例如：一个64核的SQL2016最大线程数默认为1472，默认最大并行度为64，如果一个会话引发了阻塞，被阻塞的会话并行度都很高，那么积累了几十个会话之后线程数就满了，这在繁忙的系统上可能只会花几分钟的时间。线程数满了以后新的连接无法建立，应用开始报错，直到阻塞源消失才会恢复。

这种时候普通用户无法连接数据库，我们可以通过管理员专用通道（DAC）进行连接，在连接实例的名称前加上admin:即可，例如admin:127.0.0.1，DAC连接只能同时存在1个。线程占满的根本原因还是阻塞源的处理，提高最大线程数只是一种无奈之举。

注：日常运维不建议使用DAC连接，因为DAC连接有更高的CPU优先级，服务器压力较大时有可能会抢占普通线程，引发阻塞。

• 调整最大并行度的优点是快速，修改配置后无需停机即时生效，但无法进行颗粒度更细的资源分配，而下面的资源调控器则可以做到。

资源调控器（RESOURCE GOVERNOR）

介绍

这是一个SQL Server 2008开始的功能，可以通过登录名（函数user_name()）、当前时间（函数getdate()）等会话属性进行筛选，对会话使用的CPU、物理 I/O 和内存进行人为限制，保证关键功能有充足的资源可用。

开启资源调控器后（默认关闭），会话发出请求会先通过“分类器函数”进行分类，路由到相应的“工作负荷组”，每个工作负荷组都映射到一个“资源池”，再根据资源池中设置的CPU、I/O、内存阈值来决定会话的资源分配。

• 资源池

可以看作是一个虚拟的SQL Server实例，默认有两个资源池（内部资源池和默认资源池），支持用户自行创建；

注：外部资源池定义的是外部进程的资源，如R 服务的rterm.exe、BxlServer.exe，与本次讨论的内部资源无关。

• 工作负荷组

相当于具有分类标准的会话容器，我们可以根据工作负荷组对会话进行聚合监控。每个工作负荷组都只处于一个资源池中，默认有两个工作负荷组（内部工作负荷组和默认工作负荷组），支持用户自行创建；

• 分类

对传入会话进行分类，分配到工作负荷组。

注：资源调控器不向专用管理员连接 (DAC) 施加任何控制。无需对在内部工作负荷组和资源池中运行的 DAC 查询进行分类。

创建与配置资源调控器

• 通过图形界面创建较为直观，如需指定I/O相关的限制，则必须脚本创建，可以在图形界面生成脚本再进行修改。

这里新建了一个资源池vip_pool、工作负荷组vip_group，最小CPU预留了5%，最大不超过20%，内存无限制，资源池中还创建了工作负荷组vip_group；（注意下方脚本指定了cap_cpu_percent=20，就是说即使系统空闲也不会使用超过20%的CPU）

• 新建分类器函数（此处指定了登录名vip的会话，将路由到工作负荷组vip_group，其余会话都将在默认的default组）

• 下面是效果演示，这里使用了一个1亿行表与100万行表连接，手动指定cpu消耗较高的hash join，对比普通登录名与vip登录名的执行时长（图1），并通过性能计数器查看对应的CPU使用情况（图2，红线default组，绿线vip_group组）：

注意事项

• 在所有资源池中，CPU、内存的最小值相加不能超过100，最大值的设置在系统空闲的时候是可以超出的，但是当其他资源池设置了最小值时，则一定会预留出来。如果需要限制资源池不利用空闲资源，要指定cap_cpu_percent；

• 资源池中I/O的值不能用图形界面设置，在脚本中配置的值为min_iops_per_volume、max_iops_per_volume，注意这里的单位不是%，而是IOPS，设置为0代表不限制。存储设备差异较大，难以用数值衡量百分比，如SSD的4K随机IOPS能达到10万级别，但机械磁盘通常只有100级别。如果需要对I/O进行设置，请先做好充分测试；

• 启/停/修改资源调控器不需要重启服务器，但用脚本设置后记得运行ALTER RESOURCE GOVERNOR RECONFIGURE使配置生效；

• 更多设置说明与最佳实践参考官方文档：https://docs.microsoft.com/zh-cn/sql/relational-databases/resource-governor/resource-governor-resource-pool?view=sql-server-ver15

查询提示（Query Hints）

查询提示可以对当前语句的执行计划进行干涉，但在通常情况下，查询优化器选择的执行计划已经足够高效，只推荐利用查询提示进行性能分析，或者用在一些特殊的语句上。

表提示（WITH子句）

这里只介绍一些常用项，详细使用方法参考官方文档：https://docs.microsoft.com/zh-cn/sql/t-sql/queries/hints-transact-sql-table?view=sql-server-ver15

后续精彩内容已被小麦苗无情隐藏，请输入验证码解锁本站所有文章

验证码：

请关注本站微信公众号，回复“小麦苗博客”，获取验证码。在微信里搜索“DB宝”或者“www_xmmup_com”或者微信扫描右侧二维码都可以关注本站微信公众号。