SQL Server 实例最佳做法

您可以应用多种最佳做法优化运行 Microsoft SQL Server 的 Google 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 地址。指定以逗号分隔的 IP 地址列表,其中用于替换 [LOCAL_SUBNET]remoteip 参数中不能有空格。另外,请注意,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

最佳做法:创建具有一个或多个本地 SSD 的新 SQL Server 实例,以存储 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)。每当物理事务日志文件必须增大时,系统都会创建新的虚拟日志文件。如果您没有停用“自动增大”功能,并且增长频率过于频繁,系统就会创建过多的虚拟日志文件。此活动可能导致日志文件碎片化,这种情况与磁盘碎片化类似,并且可能会对性能产生负面影响。

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

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

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

避免索引碎片化

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

表中的索引可能会碎片化,而这又可能导致使用这些索引的所有查询性能不佳。定期维护时间表应该包括对经过最重大修改的表中的索引进行重新整理。您可以对数据库运行以下 T-SQL 脚本,以显示索引及其碎片化百分比。在该示例结果上,您可以看到 PK_STOCK 索引碎片化百分比为 95%。在以下“选择”语句中,将“[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'

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

备份

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

请访问 Ola Hallengren 的网站,了解如何实施可靠的备份和维护计划。

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

监控

最佳做法:使用 Stackdriver Monitoring。

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

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

批量加载数据

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

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

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

验证您的设置

最佳做法:测试您的配置,以验证其性能是否与预期相同。

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

优化 SQL Server Enterprise Edition

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

使用压缩表

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

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

启用缓冲池扩展程序

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

缓冲池是系统存储“干净页”的位置。简而言之,缓冲池可存储您数据的副本(即为数据在磁盘上的显示方式建立镜像)。当内存中的数据发生更改时,该数据页会被称为“脏页”,脏页必须被清空并发送到磁盘,以保存更改。当您的数据库大于您的可用内存时,会给缓冲池带来压力,而干净页可能会被删除。干净页被删除后,当系统下次必须访问被删除的数据时,将不得不从磁盘读取这些数据。

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

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

后续步骤

此页内容是否有用?请给出您的反馈和评价:

发送以下问题的反馈:

此网页
Compute Engine 文档