通过

将 tempdb 放在 Azure VM 上的 SQL Server 的临时存储上

  • 项目

适用于：Azure VM 上的 SQL Server

本文介绍如何通过使用某些 Azure VM 可用的本地 SSD 临时存储（例如，将系统数据库移动到 tempdb 本地 SSD 驱动器，或使用本地 SSD 驱动器扩展缓冲池）来提高 Azure 虚拟机上的 SQL Server 工作负荷的性能。

概述

附加到某些 Azure 虚拟机（VM）的本地 SSD 驱动器提供优化的临时存储 - 一个以物理方式连接到主机的高性能磁盘。 每当解除分配或移动 VM（例如在维护或调整大小期间），将重新创建此临时存储。 不管怎样，将 SQL Server tempdb 系统数据库放在临时存储上不会带来任何风险，因为每次 SQL Server 重启时都会重新创建数据库。

建议将tempdb放在临时驱动器上，因为临时驱动器经过优化的低延迟和高 IOPS 可以显著提升严重依赖临时对象的工作负载性能，包括：

  • 处理大型记录集的查询
  • 索引创建和维护
  • 行版本控制隔离级别
  • 临时表
  • 触发器

但是，由于本地 SSD 驱动器是非永久性的，因此每当 VM 停止、解除分配或重新定位到新主机时，其内容和权限都将丢失。 这需要仔细规划，并考虑以下事项：

  • 重启时重新配置： tempdb 每次 VM 重启时，都必须重新配置以使用临时磁盘（通常为 D：）。 对于 Azure 市场中 Azure VM 映像上的 SQL Server，此过程是使用 SQL IaaS 代理扩展自动执行的，通过在 VM 启动时自动创建文件夹和处理权限，从而简化管理。 但是，如果手动安装了 SQL Server，则需要配置为 tempdb 在每次 VM 重启时手动使用临时磁盘 - 此过程可以 自动 执行，例如 PowerShell 和任务计划程序。
  • 独占使用： tempdb 应是唯一存储在本地 SSD 驱动器上的数据。 永久性数据（如数据文件、日志文件或备份）不得放置在临时存储上，因为每次 VM 重启或解除分配时，它们都会丢失。

先决条件

在配置 tempdb 以使用临时存储之前，需要满足以下先决条件：

注释

本文假定您已手动安装 SQL Server，因为当您从 Azure 市场部署一个 SQL Server 虚拟机映像时，tempdb 会被自动配置为使用临时存储。

将 tempdb 配置为使用临时存储

在维护时段内，可以使用 Transact-SQL 将 SQL Server tempdb 配置为使用临时磁盘。 请考虑以下事项：

  • 数据库tempb可以有多个数据文件，例如tempdb.mdftempdb2.md，以及tempdb3.md，具体取决于 SQL Server 配置。 必须为每个数据文件运行MODIFY FILE命令，以重新配置tempdb以使用临时磁盘，例如D:\SQLTemp。
  • 可以查询 sys.master_files （其中database_id = 2）来标识所有 tempdb 数据文件。

  1. 在本地 SSD 驱动器上创建文件夹，例如 D:\SQLTemp。

  2. 打开 SQL Server Management Studio (SSMS)，连接到你的 SQL Server 实例。

  3. 运行以下 T-SQL 命令以配置 tempdb 使用临时磁盘：

    USE MASTER
GO

ALTER DATABASE tempdb MODIFY FILE (NAME= tempdev, FILENAME= 'D:\SQLTemp\tempdb.mdf') -- to move data files
GO

ALTER DATABASE tempdb MODIFY FILE (name = templog, filename = 'D:\SQLTemp\templog.ldf') -- to move log files
GO

  4. 重启 SQL Server 实例以应用更改。

  5. tempdb通过运行以下 T-SQL 命令验证是否使用临时磁盘：

    USE tempdb
GO

EXEC sp_helpfile
GO

  6. 检查临时磁盘上的文件夹，验证 tempdb 是否在 D:\SQLTemp 文件夹中创建了文件。

启动时自动执行 tempdb 配置

由于重启 VM 时，临时驱动器上的内容会丢失，因此如果在 tempdb SQL Server 启动之前未创建文件夹，SQL Server 将无法启动。 可以使用 PowerShell 在 SQL Server 服务启动之前自动创建文件夹。

若要在启动时自动执行 tempdb 配置，请执行以下步骤：

  1. 配置服务：将 SQL Server 和 SQL Server 代理服务都设置为手动启动。 这样可以防止他们在创建文件夹之前自动启动。
  2. 创建一个 PowerShell 脚本 ，该脚本在临时磁盘上创建文件夹并启动 SQL Server 和 SQL 代理服务。
  3. 使用 Windows 计划任务安排脚本在系统启动时运行。 使用帐户配置任务，以确保无论用户是否已登录都能运行任务。

以下部分提供了每个步骤的详细说明。

配置启动模式

由于希望脚本在 SQL Server 启动之前创建要运行的文件夹，需要设置 SQL Server 和 SQL 代理服务以手动启动。 为此，请执行以下步骤：

  1. 打开“SQL Server 配置管理器”。

  2. 在左窗格中选择 SQL Server 服务 。

  3. 右键单击 SQL Server 服务，然后选择“ 属性 ”以打开 “属性” 窗口。

  4. 在 “属性 ”窗口中，选择“ 服务 ”选项卡。

  5. 在 “服务 ”选项卡上，使用下拉列表将 “开始模式 ”更改为 “手动”：

    SQL Server Configuration Manager、SQL Server 服务属性的屏幕截图，其中显示了更改启动模式的位置。

  6. 使用 “应用” 保存更改，然后 确定 关闭窗口。

  7. 对 SQL Server 代理 服务重复这些步骤。

创建 PowerShell 脚本

创建一个 PowerShell 脚本，该脚本：

  1. 在临时磁盘上创建文件夹。
  2. 启动 SQL Server 服务。
  3. 启动 SQL 代理服务。

复制并粘贴以下脚本，根据需要对其进行修改，并将其另存为 OS 驱动器上的 PowerShell 文件，例如 C:\Scripts\SQLStartup.ps1：

$SQLService="SQL Server (MSSQLSERVER)"
$SQLAgentService="SQL Server Agent (MSSQLSERVER)"
$tempfolder="D:\SQLTEMP"
if (!(test-path -path $tempfolder)) {
    New-Item -ItemType directory -Path $tempfolder
}
Start-Service $SQLService
Start-Service $SQLAgentService

注释

该脚本假定 SQL Server 实例是默认实例。 如果使用命名实例，请将 MSSQLSERVER 替换为您的 SQL Server 实例名称。

创建运行脚本的计划任务

创建计划任务以在启动时运行 PowerShell 脚本。 为此，请执行以下步骤：

  1. 从“开始”菜单打开 任务计划程序 。

  2. 在 “作”下，选择“ 创建基本任务 ”以打开 “创建任务” 窗口。

  3. 在“ 创建基本任务 ”选项卡上，输入任务的名称，例如 SQL-startup，并提供说明。 选择“下一步”。

  4. 在“ 触发器 ”选项卡上，选中 计算机启动时 并选择“ 下一步”。

  5. 在“ 作 ”选项卡上，选择“ 启动程序 ”，然后选择“ 下一步”。

  6. 在“启动程序”选项卡上，n“程序/脚本”框，输入powershell.exe并在“添加参数”（可选）框中输入脚本的路径，例如： -ExecutionPolicy Bypass -File C:\Scripts\SQLStartup.ps1

  7. 查看“ 完成 ”选项卡上的摘要，然后选择“ 完成 ”以创建任务：

    任务计划程序“创建基本任务”窗口的屏幕截图，其中显示了输入脚本路径的位置。

测试脚本

重启 VM 以测试脚本。 VM 重启后，请检查 tempdb 数据文件是否位于临时磁盘上，以及 SQL Server 和 SQL 代理服务是否正在运行。

配置缓冲池扩展

可以通过配置 缓冲池扩展 以在 Azure VM 上使用本地 SSD 驱动器来进一步增强 SQL Server 性能。 此功能通过使用磁盘上的文件来提升超过可用 RAM 的内存密集型工作负荷的 I/O 吞吐量，从而扩展内存中缓冲池。 由于本地 SSD（临时存储）提供低延迟和高性能，因此它是此扩展的理想位置。

配置缓冲池扩展时，请指定文件大小（KB）、兆字节（MB）或千兆字节（GB）。 建议的大小通常是为 SQL Server 配置的 [max_server_memory] 设置的 4 到 8 倍，但对于标准版，此值上限为 4 倍（企业版最多允许 32 次）。 例如，如果将 max_server_memory 设置为 16 GB，那么缓冲池扩展的大小应为 64-128 GB，具体大小应根据您的 SQL Server 版本和工作负荷需求进行调整。

假设指定路径存在于临时驱动器上（例如 D:\SQLTEMP\），要启用缓冲池扩展，请在连接到实例后，在 SQL Server Management Studio（SSMS）中执行以下 T-SQL 命令：

ALTER SERVER CONFIGURATION
SET BUFFER POOL EXTENSION ON
( FILENAME = 'D:\SQLTEMP\ExtensionFile.BPE' , SIZE = <size> [ KB | MB | GB ] )

相关内容