SQL Server 实例最佳做法

您可以应用多种最佳做法优化运行 Microsoft SQL Server 的 Compute Engine 实例。如需了解如何设置高性能 SQL Server 实例，请阅读创建高性能 SQL Server 实例。

配置 Windows

本部分介绍了有关如何针对 SQL Server 在 Compute Engine 上运行时的性能优化 Microsoft Windows 操作系统的配置主题。

设置 Windows 防火墙

最佳做法：使用 Windows Server 高级防火墙，并指定客户端计算机的 IP 地址。

Windows 高级防火墙是 Windows Server 中的一个重要安全组件。如果您将 SQL Server 环境设置为可以从其他客户端机器连接到数据库，请将防火墙配置为允许传入流量：

netsh advfirewall firewall add rule name="SQL Access" ^
dir=in action=allow ^
program="%programfiles%\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Binn\sqlservr.exe" ^
remoteip=LOCAL_SUBNET

使用此防火墙规则时，建议指定您的客户端机器的 IP 地址。为 remoteip 参数指定以英文逗号分隔的 IP 地址列表（无空格），以取代 LOCAL_SUBNET。另外请注意，program 参数的路径可能会发生变化，具体取决于您使用的 SQL Server 版本。

SQL Server 应用映像包含 SQL Server Windows 防火墙规则。此规则在很大程度上不受限制，因此建议在您的系统投入生产环境之前予以停用。

调整网络连接

最佳做法：使用操作系统的默认网络设置。

在大多数操作系统上，默认的网络设置都是针对连接到普通速度网络的小型计算机上的连接而配置的。 此类设置通常足以满足各种要求。此外，保守的默认值可确保网络流量不会超出网络和已连接的计算机所能接受的上限。

在 Compute Engine 上，虚拟机 (VM) 实例会连接到由 Google 设计的网络，该网络可提供高容量和出色的性能。运行 Compute Engine 实例的物理服务器已经过充分优化，可利用此网络容量。您的实例中的虚拟网络驱动程序也经过了优化，这使得默认值能满足大多数用例的要求。

安装防病毒程序

最佳做法：遵循 Microsoft 的防病毒软件指南。

如果您运行的是 Windows，则应运行某种杀毒软件。恶意软件和软件病毒会对连接到网络的任何系统带来重大风险，而杀毒软件是可供您用来保护数据的简单风险缓解措施。不过，如果杀毒软件的配置不正确，可能会对您的数据库性能造成负面影响。Microsoft 提供了关于如何选择防病毒软件的建议。

针对性能和稳定性进行优化

本部分介绍了如何优化 Compute Engine 上的 SQL Server 性能以及有助于确保 SQL Server 平稳运行的操作活动。

将数据文件和日志文件移到新的磁盘

最佳做法：使用单独的 SSD 永久性磁盘存储日志和数据文件。

默认情况下，SQL Server 的预配置映像中的所有内容都安装在启动永久性磁盘（作为“C:”驱动器装载）上。请考虑挂接辅助 SSD 永久性磁盘，并将日志文件和数据文件移到新磁盘。

使用本地 SSD 来提高 IOPS

最佳做法：创建挂接了一个或多个本地 SSD 的新 SQL Server 实例，以存储 tempdb 和 Windows 分页文件。

本地 SSD 技术具有短暂性，因此不适合用来存储您的关键数据库和重要文件。不过，tempdb 和 Windows 分页文件都是临时文件，因此它们都非常适合迁移到本地 SSD。这样可分担 SSD 永久性磁盘中大量 I/O 操作的负载。如需详细了解如何进行此项设置，请参阅设置 TempDB。

并行查询处理

最佳做法：将 max degree of parallelism 设为 8。

建议将 max degree of parallelism 的默认值设为与服务器上的 CPU 数量保持一致。不过，如果将一个查询分成 16 或 32 个区块，在不同的 vCPU 上执行这些区块，然后再全部合并成一个结果，这种做法会比只使用一个 vCPU 运行查询花费更多的时间。实际上，将默认值设为 8 是一个非常理想的选择。

最佳做法：监控 CXPACKET 等待情况并逐步增大 cost threshold for parallelism 的值。

此设置与 max degree of parallelism 密切相关。每个单元代表（在考虑进行并行执行计划之前）采用串行执行计划进行查询所需的 CPU 和 I/O 工作的组合。默认值为 5。 虽然我们不会对默认值的更改提供具体建议，但是您需要关注该值。如有必要，可在负载测试期间将该值以 5 为增量逐步递增。可能需要增大该值的一个关键指标是出现 CXPACKET 等待情况。虽然出现 CXPACKET 等待情况并不一定表示需要更改此设置，但这会是一个很好的入手点。

最佳做法：对不同的等待类型进行监控，并调整全局并行处理设置，或针对个别数据库级层进行并行处理设置。

个别数据库可能有不同的并行处理需求。您可以进行全局设置，并在个别数据库级层设置 Max DOP。您应观察异常的工作负载，监控等待次数，然后相应地调整这些值。

SQLSkills 网站提供了一个实用的性能指南，其中介绍了数据库内部的等待统计信息。本指南可以帮助您了解具体等待细节以及如何缓解延迟情况。

处理事务日志

最佳做法：监控您的系统中事务日志的增长情况。请考虑停用“自动增长”，并根据您的每日平均日志累积量将日志文件设置成固定大小。

一个最容易被忽视的性能损失和间歇性减速原因是事务日志不受控制地增长。当您的数据库配置成使用 Full 恢复模式时，您可以执行恢复操作以恢复到任何时间点，但您的事务日志的填充速度会更快。默认情况下，当事务日志文件写满时，SQL Server 会增大文件大小以添加更多空白空间来写入更多事务，并暂停数据库中的所有活动，直到写入操作结束为止。SQL Server 会根据每个日志文件的文件大小上限和文件增长设置来增大这些文件。

当文件达到其文件大小上限，并且无法再增大时，系统会发出 9002 错误，并将数据库置于只读模式。如果文件可以增大，SQL Server 会扩大文件大小，并清除空白空间。文件增长设置会默认为日志文件当前大小的 10%。此默认设置会对性能产生负面影响，因为您的文件越大，创建新的空白空间所需的时间就越长。

最佳做法：安排定期进行事务日志的备份。

无论大小上限和增长设置如何，您都应安排定期进行事务日志备份，在默认情况下，这种做法会截断旧日志条目，并让系统能够重复使用现有的文件空间。这个简单的维护任务有助于避免在高峰流量期间出现性能下降的情况。

优化虚拟日志文件

最佳方法：监控虚拟日志文件增长情况，并采取措施防止日志文件碎片化。

物理事务日志文件被细分成虚拟日志文件 (VLF)。每当物理事务日志文件必须增大时，系统都会创建新的虚拟日志文件。如果您没有停用“自动增大”功能，并且增长频率过于频繁，系统就会创建过多的虚拟日志文件。此活动可能导致日志文件碎片化，这种情况与磁盘碎片化类似，并且可能会对性能产生负面影响。

SQL Server 2014 引入了一种更高效的算法来确定“自动增大”期间需要创建多少个虚拟日志文件。通常情况下，如果增大的大小小于当前日志文件大小的 1/8，那么 SQL Server 将在此新的细分部分中创建一个虚拟日志文件。此前，如果增大的大小介于 64 MB 到 1 GB 之间，则系统会创建 8 个虚拟日志文件；如果增大的大小超过 1 GB，系统会创建 16 个虚拟日志文件。您可以使用以下 TSQL 脚本来查看您的数据库当前包含多少个虚拟日志文件。如果包含数千个文件，请考虑进行手动压缩，并调整日志文件的大小。

--Check VLFs substitute your database name below
USE YOUR_DB
DECLARE @vlf_count INT
DBCC LOGINFO
SET @vlf_count = @@ROWCOUNT
SELECT VLFs = @vlf_count

如需详细了解虚拟日志文件，请访问 Brent Ozar 的网站。

避免索引碎片化

最佳做法：定期对修改幅度最大的表进行索引碎片整理。

表中的索引可能会变得碎片化，导致使用这些索引的所有查询性能不佳。定期维护计划应包含重新整理修改最为频繁的表中的索引。 您可以对数据库运行以下 Transact-SQL 脚本，以显示索引及其碎片化程度。以下示例结果显示 PK_STOCK 索引的碎片化程度已达 95%。在以下“SELECT”语句中，请将“YOUR_DB”替换为您的数据库名称：

SELECT stats.index_id as id, name, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(N'YOUR_DB'), NULL, NULL, NULL, NULL) AS stats
    JOIN sys.indexes AS indx ON stats.object_id = indx.object_id
      AND stats.index_id = indx.index_id AND name IS NOT NULL;

RESULTS
-------------------------------
Id    name          avg_fragmentation_in_percent
-------------------------------
1 ORDERS_I1 0
2 ORDERS_I2 0
1 ORDER_LINE_I1 0.01
1 PK_STOCK95.5529819557039
1 PK_WAREHOUSE0.8

当您索引的碎片化程度过高时，您可以使用一个基本的 ALTER 脚本重新整理这些索引。以下示例脚本将输出您可对各个表索引运行的 ALTER 语句：

SELECT
'ALTER INDEX ALL ON ' + table_name + ' REORGANIZE;
GO'
FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_CATALOG = 'YOUR_DB'

请从碎片化程度最高的结果集中选择表，然后逐步执行这些语句。可考虑安排此脚本或一个类似的脚本作为您的一个定期维护作业。

格式化辅助磁盘

最佳做法：以 64 KB 的分配单元大小格式化辅助磁盘。

SQL Server 以称作区间 (extent) 的存储单元存储数据。区间大小为 64 KB，由八个连续的内存页组成，每个内存页的大小为 8 KB。通过以 64 KB 的分配单元大小格式化磁盘，SQL Server 可以更高效地对区间进行读写操作，从而提高磁盘的 I/O 性能。

如需以 64 KB 的分配单元大小格式化辅助磁盘，请运行以下 PowerShell 命令，该命令会搜索系统中所有新磁盘和未初始化的磁盘，并以 64 KB 的分配单元大小格式化这些磁盘：

Get-Disk | Where-Object {$_.PartitionStyle -eq 'RAW'} | Initialize-Disk -PartitionStyle GPT -PassThru | New-Partition -AssignDriveLetter -UseMaximumSize | Format-Volume -FileSystem NTFS -AllocationUnitSize 65536 -Confirm:$FALSE

备份

最佳实践：使用 Google 的备份和灾难恢复解决方案定期备份数据，以获得最佳保护。我们建议您至少每天备份一次数据。

Google 的备份和灾难恢复解决方案可为 Microsoft SQL Server 提供以下优势：

  • 高效的增量永久备份，具有真正的时间点恢复功能，可帮助您以比传统备份更短的时间执行备份，同时减少对生产服务器的影响。该优势还可降低带宽和存储空间消耗，从而实现低恢复点目标 (RPO) 和总拥有成本 (TCO)。
  • 装载和迁移恢复 (M&M)，用于 Cloud Storage 中存储的备份，以实现低 RTO。
  • 与 SQL Server 功能的全面集成，包括对 SQL Server 可用性组集群的支持以及用于各种场景的多个恢复选项。
  • 集中式管理平台，包括针对所有备份的专用监控、提醒和报告功能。

了解详情：

监控

最佳做法：使用 Cloud Monitoring。

您可以安装适用于 Microsoft Windows 的 Cloud Monitoring 代理，以将多个监控数据点发送到 Cloud Monitoring 系统。

借助数据收集功能，您可以微调要监控的信息，并将相应信息发送到内置管理数据仓库。您可以在所监控的同一服务器上运行管理数据仓库，也可以将数据流式传输到另一个运行仓库的 SQL Server 实例。

批量加载数据

最佳做法：使用单独的数据库暂存和转换批量数据，然后再将这些数据迁移到生产服务器。

如果您不是定期加载数据，可能需要至少将大量数据加载到自己的系统中一次。这项操作需要占用大量资源，因此您在进行批量加载时，可能会达到永久性磁盘 IOPS 上限。

有一种简便的方法可以减少批量加载操作的磁盘 I/O 和 CPU 耗用量，并会加快批量作业的执行速度。此处的解决方案是创建一个完全独立且采用 Simple 恢复模式的数据库，使用该数据库暂存和转换批量数据集，然后将该数据集插入生产数据库中。如果您有足够的空间，还可以将这个新的数据库放入本地 SSD 驱动器中。对恢复数据库使用本地 SSD 将减少您执行批量操作时的资源消耗量，并缩短完成作业所需的时间。最终带来的好处是，针对生产数据的备份作业不必对事务日志中的所有批量操作进行备份，因此备份作业的规模会更小且运行速度也会更快。

验证设置

最佳做法：测试您的配置，以验证该配置是否发挥预期的作用。

每当设置新系统时，您都应当制定计划来验证配置和运行一些性能测试。此存储过程是评估 SQL Server 配置的绝佳资源。稍后请花些时间了解配置标志，并运行该过程。

优化 SQL Server Enterprise Edition

SQL Server Enterprise Edition 在 Standard Edition 的基础上添加了很多功能。如果您要将一个现有的许可迁移到 Google Cloud，可以考虑实现某些性能方案。

使用压缩表

最佳做法：启用表和索引压缩。

压缩表会使您的系统运行更快，这似乎不合常理，但在大多数情况下确实是这样。这种折衷方案只需占用少量 CPU 周期即可压缩数据，并消除读写较大数据块所需的额外磁盘 I/O。通常，您的系统使用的磁盘 I/O 越少，系统性能就会越好。有关预估和启用表及索引压缩的说明，请访问 MSDN 网站。

启用缓冲池扩展程序

最佳实践：使用缓冲区池扩展程序来加快数据访问速度。

缓冲池是系统存储“干净页”的位置。简而言之，缓冲池可存储您数据的副本（即为数据在磁盘上的显示方式建立镜像）。当内存中的数据发生更改时，该数据页就称为“脏页”。脏页必须刷新到磁盘以保存更改。如果您的数据库大小超过您的可用内存，这会对缓冲池造成压力，且可能导致干净页被舍弃。干净页被舍弃后，系统下次要访问已舍弃的数据时，将不得不从磁盘读取这些数据。

借助缓冲区池扩展程序功能，您可以将干净页推送到本地 SSD，而不用将其舍弃。这项功能的运作方式与虚拟内存相同，也就是说，通过“交换”方式让您访问本地 SSD 上的干净页，这种方式比您通过访问普通磁盘来获取数据的速度更快。

这种技术不会立刻提供足够的内存，但可以在您的内存很小时，使吞吐量获得适度增长。如需详细了解缓冲池扩展程序并查看一些基准化分析结果，请访问 Brent Ozar 网站。

优化 SQL Server 许可

并发多线程 (SMT)

最佳做法：对于大多数 SQL Server 工作负载，请将每个核心的线程数设置为 1

并发多线程 (SMT) 在 Intel 处理器上通常称为超线程技术 (HTT)，该功能允许单个 CPU 核心在逻辑上作为两个线程共享。在 Compute Engine 上，默认在大多数虚拟机上启用 SMT，这意味着虚拟机中的每个 vCPU 都在单线程上运行，并且每个物理 CPU 核心由两个 vCPU 共享。

在 Compute Engine 上，您可以配置每个核心的线程数，从而有效地关闭 SMT。当每个核心的线程数设置为 1 时，vCPU 不会共享物理 CPU 核心。此配置对 Windows Server 和 SQL Server 的许可费用会产生重大影响。当每个核心的线程数设置为 1 时，虚拟机中的 vCPU 数量会减半，这也会将所需的 Windows Server 和 SQL Server 许可数量的减半。这样可以显著降低总工作负载费用。

但是，配置每个核心的线程数也会影响工作负载性能。编写为多线程的应用可以通过将计算工作分解为跨多个逻辑核心调度的较小的可并行块来利用此功能。像这样将工作并行化通常能够更好地利用可用核心资源，从而提高整体系统吞吐量。例如，当一个线程停滞时，另一个线程可以利用该核心。

SMT 对 SQL Server 性能的确切影响取决于工作负载特征和使用的硬件平台，因为 SMT 实现因硬件世代而异。具有大量小事务的工作负载（例如 OLTP 工作负载）通常可以利用 SMT，并受益于更高的性能提升。相比之下，并行度较低的工作负载（例如 OLAP 工作负载）从 SMT 受益较少。虽然这些模式通常已经被注意到，但请考虑评估 SMT 对每个工作负载性能的影响，以确定将每个核心的线程数设置为 1 的影响。

大多数 SQL Server 工作负载的最具成本效益的配置都需要将每个核心的线程数设置为 1。使用更大的虚拟机可以解决任何性能下降问题。在大多数情况下，许可费用降低 50% 的金额要大于使用更大型虚拟机所增加的费用。

示例：假设 SQL Server 部署在 n2-standard-16 配置中

默认情况下，操作系统中可见的核心数为 16，这意味着运行服务器需要 16 个 Windows Server vCPU 和 16 个 SQL Server vCPU 许可。

PS C:\> Get-WmiObject -Class Win32_processor | Select-Object NumberOfCores, @{Name="Thread(s) per core";Expression={$_.NumberOfLogicalProcessors/$_.NumberOfCores}}

NumberOfCores Thread(s) per core
------------- ------------------
            8                  2

在 SQL Server 上执行停用 SMT 步骤后，新配置如下：

PS C:\> Get-WmiObject -Class Win32_processor | Select-Object NumberOfCores, @{Name="Thread(s) per core";Expression={$_.NumberOfLogicalProcessors/$_.NumberOfCores}}

NumberOfCores Thread(s) per core
------------- ------------------
            8                  1

现在，只有 8 个核心在操作系统中可见，因此该服务器只需要 8 个 Windows Server 和 SQL Server vCPU 即可运行。

后续步骤