创建高性能 SQL Server 实例

本教程介绍如何创建一个运行 SQL Server 并针对性能进行了优化的 Compute Engine 虚拟机实例。本教程将指导您创建实例，然后配置 SQL Server 以在Google Cloud上获得最佳性能。您将了解许多用于调整系统性能的配置选项。

本教程使用的是 SQL Server Standard Edition 2022，因此本指南中提供的各个配置选项并非适用于所有人，而且并非所有配置选项都能为每种工作负载提供明显的性能优势。

创建具有磁盘的 Compute Engine 虚拟机

要创建高性能 SQL Server 实例，您必须先创建一个具有 SQL Server 和两个永久性磁盘卷的虚拟机实例。

永久性磁盘注意事项

如需为您的虚拟机选择永久性磁盘卷类型，请查看以下注意事项：

  • 本地固态硬盘可以高性能地存储 tempdb 和 Windows 页面文件。

    使用本地固态硬盘时，需要注意一些重要的注意事项。当您从 Windows 关闭实例或使用 API 重置实例时，本地固态硬盘会被移除。此操作会使实例无法启动。 如需让机器重新运行，您需要分离您的永久性磁盘，用它们创建新的实例，然后定义新的本地固态硬盘。启动后，您还需要格式化新磁盘并重新启动。因此，除非您准备重新构建本地固态硬盘，否则不应在其中永久存储关键数据，也不应关闭实例电源。

  • SSD 永久性磁盘可以为数据库文件提供高性能存储。

    永久性磁盘的性能是根据 CPU 数量和磁盘大小计算得到的。如果有 32 个 vCPU 和一块 1 TB 磁盘，则性能最高可达到每秒 40000 次读取操作和 30000 次写入操作。读取和写入的总持续吞吐量分别为 800 MB/秒和 400 MB/秒。这些数值代表连接到虚拟机的所有永久性磁盘卷的总和，包括 C:\ 驱动器。为确保性能一致，请创建一个本地固态硬盘，并分流分页文件、tempdb、暂存数据和备份所需的所有 IOPS。

如需详细了解磁盘性能，请参阅配置磁盘以满足性能要求。

创建具有磁盘的 Compute Engine 虚拟机

如需创建在 Windows Server 2022 上预安装了 SQL Server 2022 Standard 的虚拟机，请按照以下步骤操作：

  1. 在 Google Cloud 控制台中，前往创建实例页面。

    转到“创建实例”

  2. 对于名称，输入 ms-sql-server。

  3. 在机器配置部分中，选择通用，然后执行以下操作：

    1. 在系列列表中，点击 N2。
    2. 在机器类型列表中，点击 n2-highmem-16 (16vCPU, 128 GB memory)。

  4. 在启动磁盘部分，点击更改，然后执行以下操作：

    1. 在公共映像标签页上，点击操作系统列表，然后选择 Windows Server 上的 SQL Server。
    2. 在版本列表中，点击 SQL Server 2022 Standard on Windows Server 2022 Datacenter。
    3. 在启动磁盘类型列表中，点击标准永久性磁盘。
    4. 在大小 (GB) 部分中，将启动磁盘大小设置为 50 GB。
    5. 要保存启动磁盘配置，请点击选择。

  5. 展开高级选项部分，然后执行以下操作：

    1. 展开磁盘部分。

    2. 如需创建本地磁盘，请点击添加本地 SSD，然后执行以下操作：

      1. 在接口列表中，选择满足系统性能要求的协议。
      2. 在磁盘容量列表中，选择支持预期的 tempdb 文件大小的磁盘容量。
      3. 要完成此磁盘的创建，请点击保存。

    3. 如需创建其他磁盘，请点击添加新磁盘。

      1. 保持名称字段不变。
      2. 在磁盘来源类型列表中，选择空白磁盘。
      3. 在磁盘类型列表中，选择 SSD 永久性磁盘。
      4. 在大小字段中，输入可容纳数据库大小的磁盘大小。
      5. 要完成第二个磁盘的创建，请点击保存。

  6. 如需创建虚拟机，请点击创建。

配置 Windows

现在您已经有了一个正在运行 SQL Server 的实例，请连接到您的实例并配置 Windows 操作系统。之后，您将在接下来的部分中学习如何配置 SQL Server。

连接到您的实例

  1. 在 Google Cloud 控制台中，前往虚拟机实例页面。

    转到虚拟机实例

  2. 在名称列下，点击您的实例的名称 ms-sql-server。

  3. 在实例详情页面的顶部，点击设置 Windows 密码按钮。

  4. 指定用户名。

  5. 点击设置为此 Windows 实例生成一个新密码。

  6. 记下用户名和密码，以便您可以登录到此实例。

  7. 使用 RDP 连接到您的实例。

设置磁盘卷

创建和格式化卷：

  1. 从 Start 菜单中，搜索“Computer Managemen”，然后将其打开。
  2. 在 Storage 部分下，选择 Disk Management。
  3. 当系统提示您初始化磁盘时，请接受默认选择，然后点击 OK。

  4. 为本地固态硬盘创建分区：

    如需查找本地固态硬盘，请右键点击磁盘，然后选择 Properties。SCSI 接口的本地 SSD 磁盘属性名称为 Google EphemeralDisk，NVMe 接口的本地固态硬盘属性名称则为 nvme_card。本地固态硬盘和永久性固态硬盘均标记为具有 Unallocated 分区。

    1. 如果虚拟机仅包含 1 个本地固态硬盘，请按照以下步骤操作：

      1. 在磁盘驱动器列表下，右键点击 374.98 GB 本地固态硬盘，然后选择 New Simple Volume。
      2. 在欢迎界面上，点击 Next 以启动磁盘卷向导。
      3. 在 Specify Volume Size 步骤中，将卷大小保留为默认值，然后点击 Next 以继续操作。
      4. 在 Assign Drive Letter or Path 步骤中，选择 P: 作为盘符，然后点击 Next 以继续操作。

      5. 在 Format Volume 步骤中，将 Allocation unit size 更改为 8192，并输入“pagefile”作为 Volume label。点击 Next 以继续操作。

        “新建卷”向导

      6. 点击 Finish 以完成磁盘卷向导。

    2. 如果虚拟机包含多个本地固态硬盘驱动器，请按照以下步骤操作：

      1. 在磁盘驱动器列表下，右键点击 374.98 GB 本地固态硬盘，然后选择 New Striped Volume。
      2. 在欢迎界面上，点击 Next 以启动磁盘卷向导。

      3. 在 Select Disks 步骤中，将大小为 383,982 MB 的所有可用磁盘添加到所选部分中。点击 Next 以继续操作。

        添加带区磁盘

      4. 在 Assign Drive Letter or Path 步骤中，选择 P: 作为盘符，然后点击 Next 以继续操作。

      5. 在 Format Volume 步骤中，将 Allocation unit size 更改为 8192，并输入“pagefile”作为 Volume label。点击 Next 以继续操作。

        “新建卷”向导

      6. 点击 Finish 以完成磁盘卷向导。

  5. 重复上述步骤，为固态硬盘创建 New Simple Volume，其中包含以下三项更改：

    • 选择 D: 作为驱动器号。

    • 将分配单元大小设置为 64k。

      如需详细了解如何选择分配单元大小，请参阅 SQL Server 实例的最佳做法。

    • 在卷标签中输入 sqldata。

移动 Windows 分页文件

现在您已创建并装载了新卷，请将 Windows 分页文件移动到本地固态硬盘上，从而释放永久性磁盘的 IOPS 并缩短虚拟内存的访问时间。

  1. 从开始菜单中，搜索查看高级系统设置，然后打开对话框。
  2. 点击高级标签，然后在性能部分中，点击设置。
  3. 在虚拟内存部分中，点击更改按钮。
  4. 清除 Automatically manage paging file size for all drives 复选框。系统应该已经在 C:\ 驱动器上设置了页面文件，您需要将其移动。
  5. 点击 C:，然后点击无分页文件单选按钮。
  6. 点击设置按钮。
  7. 如需创建新的分页文件，请点击 P: 驱动器，然后点击系统管理的大小单选按钮。
  8. 点击设置按钮。

  9. 点击确定三次以退出高级系统属性。

    Microsoft 支持团队已发布有关虚拟内存设置的其他提示。

设置电源配置文件

将电源配置文件设置为 High-Performance，而不是 Balanced。

  1. 在开始菜单中，搜索“选择电源计划”，然后打开电源选项。
  2. 选择高性能单选按钮。
  3. 退出该对话框。

配置 SQL Server

使用 SQL Server Management Studio 可执行大多数管理任务。 SQL Server 的预配置映像已经安装了 Management Studio。启动 Management Studio，然后点击连接以连接到默认数据库。

移动数据和日志文件

SQL Server 预配置的映像包含安装在 C:\ 驱动器上的所有内容，包括系统数据库。优如需化您的设置，请将这些文件移到您新创建的 D:\ 驱动器中。 另外请记得在 D:\ 驱动器上创建所有新数据库。 由于您使用的是固态硬盘，因此无需将数据文件和日志文件存储在不同的磁盘分区上。

有两种方法可以将安装目录移动到辅助磁盘：使用安装程序或手动移动文件。

使用安装程序

如需使用安装程序，请运行 c:\setup.exe 并在辅助磁盘上选择新的安装路径。

手动移动文件

移动系统数据库并配置 SQL Server 以将数据和日志文件保存在同一个卷上：

  1. 创建名为 D:\SQLData 的新文件夹。
  2. 打开一个命令窗口。

  3. 输入以下命令以授予对 NT Service\MSSQLSERVER 的完全访问权限：

    icacls D:\SQLData /Grant "NT Service\MSSQLServer:(OI)(CI)F"

  4. 使用 Management Studio 按照以下指南移动您的系统数据库并为新数据库更改默认文件位置。

  5. 如果您计划使用报告服务器功能，请一并移动 ReportServer 和 ReportServerTempDB 文件。

移动主要配置数据库文件并重启后，您需要将系统配置为指向模型和 MSDB 数据库的新位置。以下是要在 Management Studio 中运行的一个帮助程序脚本：

ALTER DATABASE model MODIFY FILE ( NAME = modeldev , FILENAME = 'D:\SQLData\model.mdf' )
ALTER DATABASE model MODIFY FILE ( NAME = modellog , FILENAME = 'D:\SQLData\modellog.ldf' )
ALTER DATABASE msdb MODIFY FILE ( NAME = MSDBData , FILENAME = 'D:\SQLData\MSDBData.mdf' )
ALTER DATABASE msdb MODIFY FILE ( NAME = MSDBlog , FILENAME = 'D:\SQLData\MSDBLog.ldf' )

在您执行这些命令后：

  1. 使用 services.msc 管理单元停止 SQL Server 数据库服务。
  2. 使用 Windows 文件浏览器将物理文件从 master 数据库原先所在的 C:\ 驱动器移动到 D:\SQLData 目录。
  3. 启动 SQL Server 数据库服务。

设置系统权限

移动系统数据库后，修改其他设置，首先修改为运行 SQL Server 进程（名为 NT Service\MSSQLSERVER）而创建的 Windows 用户账号的权限。

授予 Lock Pages in Memory 权限

群组政策 Lock Pages in Memory 权限可防止 Windows 将物理内存中的页移至虚拟内存。为了保持物理内存不被占用且组织有序，Windows 会尝试将很少被修改的旧页面替换为磁盘上的虚拟内存分页文件。

SQL Server 将重要信息存储在内存中，例如表结构、执行计划和缓存查询。其中一些信息很少发生更改，因此这类信息会成为分页文件的目标。如果这些信息被移动到分页文件中，SQL Server 的性能可能会降低。为 SQL Server 的服务账号授予群组政策 Lock Pages in Memory 权限会阻止这种交换。

请按照以下步骤操作：

  1. 点击开始，然后搜索编辑组策略以打开控制台。
  2. 展开本地计算机策略 > 计算机配置 > Windows 设置 > 安全设置 > 本地政策 > 用户权限分配。
  3. 搜索然后双击锁定内存页。
  4. 点击添加用户或组。
  5. 搜索“NT Service\MSSQLSERVER”。
  6. 如果您看到多个名称，请双击 MSSQLSERVER 名称。
  7. 点击确定两次。
  8. 使组策略编辑器控制台保持打开状态。

锁定页面

授予 Perform volume maintenance tasks 权限

默认情况下，当应用从 Windows 请求一片磁盘空间时，操作系统会找到一个适当大小的磁盘空间块，然后将整个磁盘块清空，再将其交给应用。由于 SQL Server 经常会增加文件大小和填充磁盘空间，因此这种做法并非最佳选择。

有一个单独的 API 用于为应用分配磁盘空间，通常称为“即时文件初始化”。很可惜，此设置仅适用于数据文件，但您将在后面的部分中了解日志文件增长。即时文件初始化要求运行 SQL Server 进程的服务账号必须拥有另一个名为 Perform volume maintenance tasks 的组策略权限。

  1. 在组策略编辑器中，搜索“执行卷维护任务”。
  2. 按照上一部分中相同的方式，添加“NT Service\MSSQLSERVER”账号。
  3. 重新启动 SQL Server 进程以激活这两项设置。

设置 tempdb

为每个 CPU 创建一个 tempdb 文件曾经是优化 SQL Server CPU 使用率的最佳做法。不过，由于 CPU 数量随时间不断增加，遵循此指南可能会降低性能。您不妨从使用 4 个 tempdb 文件开始。您在衡量系统性能时会发现，在极少数情况下，可能需要逐步增加 tempdb 文件的数量，最多到 8 个。

您可以在 SQL Server Management Studio 中运行 Transact-SQL (T-SQL) 脚本，以将 tempdb 文件移动到“p:”驱动器的文件夹中。

  1. 创建目录 p:\tempdb

  2. 授予对“NT Service\MSSQLSERVER”用户账号的完全安全访问权限：

    icacls p:\tempdb /Grant "NT Service\MSSQLServer:(OI)(CI)F"

  3. 在 SQL Server Management Studio 中运行以下脚本，以移动 tempdb 数据文件和日志文件：

    USE master
GO
ALTER DATABASE [tempdb] MODIFY FILE (NAME = tempdev, FILENAME = 'p:\tempdb\tempdb.mdf')
GO
ALTER DATABASE [tempdb] MODIFY FILE (NAME = templog, FILENAME = 'p:\tempdb\templog.ldf')
GO

  4. 重新启动 SQL Server。

  5. 运行以下脚本来修改文件大小，并为新的 tempdb 创建另外 3 个数据文件。

    ALTER DATABASE [tempdb] MODIFY FILE (NAME = tempdev, FILENAME = 'p:\tempdb\tempdb.mdf', SIZE=8GB)
ALTER DATABASE [tempdb] MODIFY FILE (NAME = templog, FILENAME = 'p:\tempdb\templog.ldf' , SIZE = 2GB)
ALTER DATABASE [tempdb] ADD FILE (NAME = 'tempdev1', FILENAME = 'p:\tempdb\tempdev1.ndf' , SIZE = 8GB, FILEGROWTH = 0);
ALTER DATABASE [tempdb] ADD FILE (NAME = 'tempdev2', FILENAME = 'p:\tempdb\tempdev2.ndf' , SIZE = 8GB, FILEGROWTH = 0);
ALTER DATABASE [tempdb] ADD FILE (NAME = 'tempdev3', FILENAME = 'p:\tempdb\tempdev3.ndf' , SIZE = 8GB, FILEGROWTH = 0);
GO

    如果您使用的是 SQL Server 2016，在执行上述步骤后，还需要额外删除 3 个 tempdb 个文件：

    ALTER DATABASE [tempdb] REMOVE FILE temp2;
ALTER DATABASE [tempdb] REMOVE FILE temp3;
ALTER DATABASE [tempdb] REMOVE FILE temp4;

  6. 再次重新启动 SQL Server。

  7. 从 C:\ 驱动器上的原始位置删除 model、MSDB、master 和 tempdb 文件。

您已成功将 tempdb 文件移到本地固态硬盘分区上。如前所述，此操作存在一些风险，但如果 tempdb 文件因任何原因丢失，SQL Server 将重新生成它们。移动 tempdb 可提高本地固态硬盘的性能，并降低永久性磁盘卷的 IOPS。

设置 max degree of parallelism

max degree of parallelism 的默认设置是与服务器上的 CPU 数量相匹配。但是，当它到达某个值时，在 16 或 32 个并行块中执行查询及合并结果要比在单个进程中运行该查询要慢得多。 如果您使用的是 16 核心或 32 核心实例，则可以使用以下 T-SQL 将 max degree of parallelism 值设置为 8：

USE master
GO
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure 'max degree of parallelism', 8
GO
RECONFIGURE WITH OVERRIDE
GO

设置 max server memory

此设置的默认值是一个非常大的数字，但您应该将其设置为可用物理 RAM 的兆字节 (MB) 数，并减去操作系统和开销所占用的几千兆字节 (GB)。以下 T-SQL 示例将 max server memory 调整为 100 GB。请对其进行修改以将值调整为与您的实例匹配的数值。如需了解详情，请参阅服务器内存服务器配置选项文档。

EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE WITH OVERRIDE
GO
exec sp_configure 'max server memory', 100000
GO
RECONFIGURE WITH OVERRIDE
GO

即将完成

再次重新启动实例以确保所有新设置生效。您的 SQL Server 系统已配置好，您已准备好创建自己的数据库并开始测试特定的工作负载。 如需详细了解操作活动、其他性能注意事项和 Enterprise Edition 功能，请查看 SQL Server 最佳做法指南。