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

安装防病毒程序

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

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

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

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

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

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

默认情况下,SQL Server 预配置映像的所有随附组件都安装在永久性启动磁盘(作为“C:”盘装载)上。请考虑挂接辅助 SSD 永久性磁盘,并将日志文件和数据文件迁移到新的磁盘。

使用本地 SSD 来提高 IOPS

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

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

并行查询处理

最佳做法:将 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 和 IO 工作的组合。默认值为 5。虽然我们不会对默认值的更改提供具体建议,但是您需要关注该值。如有必要,可在负载测试期间将该值以 5 为增量逐步递增。可能需要增大该值的一个关键指标是出现 CXPACKET 等待情况。虽然出现 CXPACKET 等待情况并不一定表示需要更改此设置,但这会是一个很好的入手点。

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

各个数据库可能有不同的并行处理需求。您可以进行全局设置,并在各个数据库级别设置 Max DOP。建议您观察独特的工作负载,监控等待情况,然后相应地调整这些值。

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

处理事务日志

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

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

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

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

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

优化虚拟日志文件

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

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

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

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

如需详细了解 VLF,请访问 Brent Ozar 的网站

避免索引碎片化

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

表中的索引可能会变得碎片化,导致使用这些索引的所有查询性能不佳。定期维护计划应包含重新整理修改幅度最大的表中的索引。您可以对数据库运行以下 T-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
    

备份

最佳做法:制定备份计划并定期执行备份。

如需了解如何实施健全可靠的备份和维护计划,请访问 Ola Hallengren 的网站,其中提供了很好的入门指南。

在定期进行数据库备份时,请注意不要使用过多的永久性磁盘 IOPS。可使用本地 SSD 暂存您的备份,然后将这些备份推送到 Cloud Storage 存储分区。

监控

最佳做法:使用 Stackdriver Monitoring。

您可以安装适用于 Microsoft Windows 的 Stackdriver Monitoring 代理,以将若干监控数据点发送到 Stackdriver Monitoring 系统。

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

批量加载数据

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

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

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

验证设置

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

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

优化 SQL Server Enterprise Edition

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

使用压缩表

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

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

启用缓冲池扩展程序

最佳做法:使用缓冲池扩展程序来加快数据访问速度。

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

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

这项技术远远不如拥有足够内存的时候那样快,但它可以在可用内存不足时适度地增长吞吐量。如需详细了解缓冲池扩展程序并查看一些基准化分析结果,请访问 Brent Ozar 网站

后续步骤