创建高性能 SQL Server 实例

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

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

目标

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

费用

本教程使用 Cloud Platform 的可计费组件,包括:

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

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

准备工作

  1. 登录您的 Google 帐号。

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

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

    转到“管理资源”页面

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

    了解如何启用结算功能

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

创建 Compute Engine 实例和磁盘

使用 SQL Server 和两个永久性磁盘创建 Compute Engine 实例。

  • 一块本地 SSDtempdb 和 Windows 分页文件提供了一个高性能存储位置。

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

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

    SSD 永久性磁盘的性能是根据 CPU 数量和磁盘大小计算得到的。如果有 32 个 vCPU 和一块 1 TB 磁盘,则性能最高可达到每秒 40000 次读取操作和 30000 次写入操作。读取和写入操作的总持续吞吐量分别为 800 MB/秒和 400 MB/秒。这些测量结果表示的是附加到虚拟机的所有永久性磁盘的总和,包括 C:\` drive. 驱动器。 This is why you should create a local SSD to offload all the IOPS needed for the paging file,这就解释了为什么您应该创建一块本地 SSD 以减少分页文件、tempdb`、暂存数据和备份所需的所有 IOPS。

如需了解有关磁盘性能的更多信息,请参阅优化永久性磁盘和本地 SSD 的性能

创建 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 时能更好地对齐。此更改还会减少计入永久性磁盘 IO 限额的磁盘操作次数。

  • 卷标签中输入“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,然后点击连接以连接到默认数据库。

移动数据和日志文件

在 SQL Server 的预配置映像中,所有内容都安装在 C:\` drive, including the system databases. 驱动器上,包括系统数据库。In order to optimize your setup, move those files to the new为了优化您的设置,请将这些文件移到您之前创建的新的 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. 如果您计划使用报告服务器功能,请将 ReportServerReportServerTempDB 文件也移动过去。

移动主文件并重新启动后,您需要将系统配置为指向模型和 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. C:\` drive where thedatabase was located to the使用 Windows 文件资源管理器将物理文件从主数据库之前所在的 C:\ 驱动器移到 D:\SQLData 目录中。
  3. 启动 SQL Server 数据库服务。

设置系统权限

移动系统数据库后,修改一些额外设置,首先从为了运行 SQL Server 进程而创建的 Windows 用户帐号的权限开始,该用户帐号名为 NT Service\MSSQLSERVER

授予 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 创建三个附加数据文件。

    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 分区。如前所述,这样做会带来一些风险,但如果这些文件因任何原因丢失,SQL Server 将重新构建 TempDB 文件。移动 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 教程后,您可以清理在 Google Cloud Platform 上创建的资源,避免这些资源占用配额,以后您就不必为这些资源付费了。以下部分介绍如何删除或关闭这些资源。

删除项目

最简单的避免支付费用的方法是删除您为本教程创建的项目。

要删除项目,请运行以下命令:

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

    转到“项目”页面

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

删除实例

要删除 Compute Engine 实例,请运行以下命令:

  1. 在 GCP Console 中,转到“虚拟机实例”页面。

    转到“虚拟机实例”页面

  2. 点击 要删除的实例旁边的复选框。
  3. 点击页面顶部的删除按钮以删除实例。

删除永久性磁盘

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

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

    转到“磁盘”页面

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

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

后续步骤

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

发送以下问题的反馈:

此网页
Compute Engine