创建高性能 SQL Server 实例

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

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

目标

  • 设置 Compute Engine 实例和磁盘。
  • 配置 Windows 操作系统。
  • 配置 SQL Server。

费用

本教程使用 Google Cloud Platform 的收费组件,包括:

  • Compute Engine 高内存实例
  • Compute Engine SSD 永久性磁盘存储
  • Compute Engine 本地 SSD 磁盘存储
  • SQL Server Standard 预配置映像

您可以使用价格计算器根据您的预计使用情况来估算费用。提供的链接中显示了本教程中使用的产品的估算费用,每小时可能超过 4 美元(美国),每月可能超过 3000 美元。Google Cloud Platform 新用户可能有资格申请免费试用

准备工作

  1. 登录您的 Google 帐号。

    如果您还没有 Google 帐号,请注册新帐号

  2. 选择或创建 Google Cloud Platform 项目。

    转到“管理资源”页面

  3. 确保您的 Google Cloud Platform 项目已启用结算功能。

    了解如何启用结算功能

  4. 如果您在本地机器上使用的不是 Windows,请安装一个第三方 RDP 客户端,例如 FusionLabs 的 Chrome RDP

创建 Compute Engine 实例和磁盘

创建包含 SQL Server 和两个永久性磁盘的 Compute Engine 实例。

  • 本地 SSD 可以高性能地存储 tempdb 和 Windows 页面文件。

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

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

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

如需详细了解磁盘性能,请参阅块存储性能

创建 Compute Engine 实例

创建一个在 Windows Server 2012 上预安装了 SQL Server 2014 Standard 的虚拟机。

  1. 在 Google Cloud Platform Console 中,转到虚拟机实例页面。

    转到虚拟机实例页面

  2. 点击创建实例按钮。

  3. 将您的实例命名为“ms-sql-server”。

  4. 机器配置设置为 16个 vCPU 104 GB,n1-highmem-16。

  5. 启动磁盘部分,点击更改以开始配置您的启动磁盘。

  6. 应用映像标签中,选择 Windows Server 2012 R2 上的 SQL Server 2014 Standard。

  7. 启动磁盘类型部分中,选择标准永久性磁盘。

  8. 大小 (GB) 部分中,将启动磁盘大小设置为 50 GB。

  9. 点击选择

  10. 展开管理、安全、磁盘、网络、单独租用

  11. 点击磁盘

  12. 额外磁盘下,点击添加新磁盘以创建新的额外磁盘。

  13. 保持名称字段不变。

  14. 类型下,选择本地 SSD 暂存磁盘(最多 8 个)。

  15. 点击完成,即可完成此磁盘的创建。

  16. 额外磁盘下,再次点击添加新磁盘以创建第二个额外磁盘。

  17. 保持名称字段不变。

  18. 类型下,选择 SSD 永久性磁盘。

  19. 源类型下,选择空白磁盘。

  20. 点击完成,即可完成第二个磁盘的创建。

  21. 点击创建以创建实例。

配置 Windows

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

连接到您的实例

  1. 转到 GCP Console 中的虚拟机实例页面。

    转到虚拟机实例页面

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

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

  4. 指定用户名。

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

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

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

    • 如果您安装了 FusionLabs 的 Chrome RDP,请点击实例详细信息页面顶部的 RDP 按钮。
    • 如果您使用的是其他 RDP 客户端,例如 Windows 远程桌面连接,请点击 RDP 按钮的溢出菜单并下载 RDP 文件。使用您的客户端打开此 RDP 文件。

设置磁盘卷

创建和格式化卷:

  1. 开始菜单中,搜索“服务器管理器”,然后将其打开。
  2. 选择文件和存储服务,然后选择磁盘。

    本地 SSD 名为 Google EphemeralDisk。本地 SSD 和永久性 SSD 均标记为有 Unknown 分区:

    查找 Google EphemeralDisk 项

  3. 右键点击名为“Google Ephemeral Disk”的 375 GB 本地 SSD 磁盘,然后选择新建卷

  4. 使用默认值,并选择 P: 作为磁盘标签,因为这将是分页文件磁盘。

  5. 在文件系统设置步骤中,将分配单元大小更改为 8192,并为卷标签输入“pagefile”。

    “新建卷”向导

  6. 对第二块 SSD 永久性磁盘重复上述相同步骤,但注意以下三处改动:

  • 选择 D: 作为驱动器号。
  • 分配单元大小设置为“32k”。

    Microsoft 建议将 SQL Server 数据和日志磁盘格式化为 64k,但 GCP 中的永久性磁盘技术更适合使用 32k。此更改还会减少计入永久性磁盘 I/O 限额的磁盘操作次数。

  • 卷标签中输入“sqldata”。

解决 Failed to mount path - Invalid Parameter 错误

如果遇到此错误,请按照下列步骤操作:

  1. 点击关闭
  2. 点击右上角的刷新磁盘图标。
  3. 点击列表中的 500 GB 永久性磁盘。
  4. 面板中,右键点击该卷,然后选择管理驱动器号和访问路径。

    管理驱动器号和访问路径

  5. 选择 D: 作为驱动器号。

  6. 点击确定

移动 Windows 分页文件

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

  1. 开始菜单中,搜索查看高级系统设置,然后打开对话框。
  2. 点击高级标签,然后在性能部分中,点击设置。
  3. 虚拟内存部分中,点击更改按钮。
  4. 取消选中自动管理所有驱动器的分页文件大小复选框。系统应该已经在 C:\ 驱动器上设置了页面文件,您需要将其移动。
  5. 点击 C:,然后点击无分页文件单选按钮。
  6. 点击设置按钮。
  7. 如需创建新的分页文件,请点击 P: 驱动器,然后点击系统管理的大小单选按钮。
  8. 点击设置按钮。
  9. 点击确定三次以退出高级系统属性。

    Microsoft 支持部门已发布了有关虚拟内存设置的其他提示

设置电源配置文件

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

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

配置 SQL Server

使用 SQL Server Management Studio 可执行大多数管理任务。SQL Server 2014 的预配置映像已经安装了 Management Studio,但如果您使用的是 SQL Server 2016 映像,则需要手动下载并安装 Management Studio。安装完毕后,启动 Management Studio,然后点击连接以连接到默认数据库。

移动数据和日志文件

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

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

使用安装程序

如需使用安装程序,请运行 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 中运行 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:\ 驱动器上的原始位置删除 modelMSDBmastertempdb 文件。

您已成功将 TempDB 文件移到本地 SSD 分区上。如前所述,此操作存在一些风险,但如果 TempDB 文件因任何原因丢失,SQL Server 将重新生成它们。移动 TempDB 可提高本地 SSD 的性能,并降低永久性磁盘的 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 最佳做法指南

清理

学完 SQL Server 教程后,您可以清理在 GCP 上创建的资源,以避免这些资源占用配额,日后产生费用。以下部分介绍如何删除或关闭这些资源。

删除项目

为了避免产生费用,最简单的方法是删除您为本教程创建的项目。

如需删除项目,请执行以下操作:

  1. 在 GCP Console 中,转到项目页面。

    转到“项目”页面

  2. 在项目列表中,选择您要删除的项目,然后点击删除 delete
  3. 在对话框中输入项目 ID,然后点击关闭以删除项目。

删除实例

如需删除 Compute Engine 实例,请执行以下操作:

删除永久性磁盘

如需删除永久性磁盘,请执行以下操作:

  1. 在 GCP Console 中,转到“磁盘”页面。

    转到“磁盘”页面

  2. 选中要删除的磁盘名称旁的复选框。

  3. 点击页面顶部的删除按钮。

后续步骤

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

发送以下问题的反馈:

此网页
Compute Engine 文档