在 Amazon RDS Custom 中使用本地实例存储优化 SQL Server 的 TempDB 性能
关键要点
本文探讨如何通过使用本地 NVMe SSD 驱动器提高 SQL Server TempDB 的性能。本地存储可以显著缩短延迟和提高 I/O 性能，使 TempDB 整体性能提升高达 20。提供了通过 GUI 和 PowerShell 配置和初始化本地存储的分步指南。包括性能基准测试与 EBS 存储的比较，强调本地存储的优势。Amazon Relational Database Service (Amazon RDS) Custom for SQL Server 现在支持 X2iedn 实例类型，该类型具有高内存与 vCPU 比率，并配备非易失性内存表达 (NVMe) SSD 支持的本地存储，旨在实现低延迟和改进的随机 I/O 性能。通过这一新功能，您可以在本地附加的 NVMe 磁盘上创建并扩展 TempDB 文件，从而实现低存储延迟。
在本文中，我们将展示如何使用本地 NVMe 磁盘来优化 TempDB 性能。我们将提供配置和初始化本地存储的详细说明通过 GUI 和 PowerShell，并与 Amazon Elastic Block Store Amazon EBS存储中的 TempDB 进行性能比较，以及在使用本地实例存储时优化 TempDB 的最佳实践。
SQL Server TempDB 概述
SQL Server 中的 TempDB 系统数据库是一个全球资源，可供所有连接到该实例的用户访问。它用于存储临时用户对象和数据库引擎创建的内部对象。临时用户对象包括全局或本地临时表、临时存储过程、表变量、在表值函数中返回的表和游标。数据库引擎创建的内部对象包括存储中间结果的工作表，例如用于操作如临时大对象 LOB 存储、哈希连接或哈希聚合操作以及某些 GROUP BY、ORDER BY 或 UNION 查询的中间排序结果的工作文件等。
由于 TempDB 在许多操作中发挥着至关重要的作用，特别是当多个查询需要临时存储或严重使用快照隔离、结果集排序或聚合用户查询时，TempDB 会经历频繁的 I/O。多个会话同时尝试在 TempDB 中分配空间时，可能会导致竞争，从而引发整体性能问题。
使用本地 TempDB 的优势：
优势描述更快的读写速度本地存储的 TempDB 具有更低的延迟和更高的随机 I/O 性能。性能提升根据工作负载，TempDB 性能可提升多达 20。降低 EBS 快照成本第二个 TempDB 文件将存储在本地实例存储中，降低总体快照成本。您需要一个 RDS Custom for SQL Server 实例作为先决条件。有关设置实例的更多详细信息，请参阅 使用 AWS CloudFormation 启动 Amazon RDS Custom for SQL Server 实例。
使用 GUI 配置和初始化本地 NVMe SSD 存储
请按照以下步骤通过 RDP 在 Amazon Elastic Compute Cloud Amazon EC2实例上初始化并开始使用 SSD 磁盘：
使用远程桌面协议 (RDP) 连接到您的 RDS Custom EC2 实例。您需要实例的公有 IP 地址或 DNS 名称以及登录凭据。
在 开始 菜单搜索框中输入“磁盘管理”并选择相应的结果以打开磁盘管理窗口。在磁盘管理窗口中，您应该会看到磁盘列表。新 SSD 磁盘可能会显示为未知磁盘以下截图中的 Disk1。
右键单击 Disk1标记为“未初始化”并选择 初始化磁盘 开始设置其分区。按照提示选择适当的磁盘初始化类型在本例中为 GPT，然后选择 确定 继续初始化。初始化磁盘后，右键单击未分配空间，选择 新建简单卷。选择 下一步 以按照向导创建 SSD 上的新分区。您可以选择分区大小和驱动器字母，并将分区格式化为文件系统例如 NTFS。根据 DB 实例类型 配置您的本地存储。分配一个驱动器字母在本例中为 T；选择一个未被使用的驱动器字母。使用适当的文件系统格式化在本例中为 NTFS，并对卷进行标记我们选择的标签为 Temp。选择 下一步 继续创建分区。右键单击新创建的分区并选择 格式化。选择所需的文件系统和分配单位大小。选择 下一步 完成格式化分区。奈云加速器下载SSD 磁盘现在已初始化并准备使用。您可以使用分配的驱动器字母在本案例中为 T访问它。
使用 PowerShell 配置和初始化本地 NVMe SSD 存储
对于我们的示例用例，我们在 X2iedn 实例中有一个本地 SSD 存储。您可以通过以管理员身份运行以下 PowerShell 命令来配置和初始化本地 NVMe SSD 存储：
powershell
获取所有可用的磁盘
disks = GetDisk
找到需要配置的本地 SSD 磁盘
ssd = disks WhereObject { OperationalStatus eq offline or PartitionStyle eq RAW }
初始化磁盘
ssd InitializeDisk PartitionStyle GPT
创建新分区
partition = ssd NewPartition UseMaximumSize
分配一个未使用的驱动器字母，此示例中使用 T
partition SetPartition NewDriveLetter T
根据提供的卷名称格式化分区，在本示例中使用‘Temp’
partition FormatVolume FileSystem NTFS NewFileSystemLabel Temp
在本地存储上配置 TempDB
请完成以下步骤以在本地存储上配置 TempDB：
确定您打算用于创建附加 TempDB 文件的新本地驱动器。确保该驱动器具有足够的磁盘空间和 I/O 能力来支持 TempDB。打开 SQL Server Management Studio (SSMS)，连接到您的 SQL Server 实例，并使用 master 用户登录 SSMS。运行以下 SQL 命令检查 TempDB 的当前配置及其文件位置。
sqlUSE tempdb EXEC sphelpfile
请注意现有文件位置。
在配置附加 TempDB 文件之前，在新驱动器上创建目录以存储附加 TempDB 文件。在我们的示例中，我们在 T 盘上创建了路径 TrdsdbdataDATA 的目录以存储附加的 TempDB 文件。请确保您具有创建目录的必要权限。您也可以通过以管理员身份运行以下 PowerShell 命令来创建目录结构并复制权限：
powershell
源目录，此示例中的源目录为 DrdsdbdataDATA
sourcedirectory = DrdsdbdataDATA
显示源目录
WriteHost 源目录为 sourcedirectory
目标目录，在本例中为 TrdsdbdataDATA
destinationdirectory = TrdsdbdataDATA
显示目标目录
WriteHost 目标目录将为 destinationdirectory
获取源目录的目录结构
WriteHost 从源中获取目录结构subdirectory = GetChildItem Path sourcedirectory Recurse WhereObject { PSIsContainer }
创建目标目录
WriteHost 创建目标目录NewItem ItemType Directory Force Path destinationdirectory
获取源目录的权限
WriteHost 获取源目录的权限sourcedirectorypermission = GetAcl Path sourcedirectory
将目标目录的权限设置为与源目录相同
WriteHost 设置目标目录的权限SetAcl Path destinationdirectory AclObject sourcedirectorypermission
从源目录复制目录结构和权限到目标目录
WriteHost 复制目录结构和权限foreach (item in subdirectory) { relativepath = itemFullNameSubstring(sourcedirectoryLength) destinationpath = JoinPath Path destinationdirectory ChildPath relativepath WriteHost 创建目录 destinationpath NewItem ItemType Directory Force Path destinationpath itemAcl = GetAcl Path itemFullName SetAcl Path destinationpath AclObject itemAcl}
WriteHost 完成！
使用以下 SQL 命令作为模板，在新创建的驱动器上创建附加 TempDB 文件。根据需要调整文件路径和大小。建议将所有 TempDB 数据库文件设置为相同的初始大小。
sqlUSE masterGOALTER DATABASE tempdbADD FILE ( NAME = tempdev1 示例文件名 FILENAME = TrdsdbdataDATAtempdev1ndf 指定新的文件路径 SIZE = 300GB 设置文件大小 MAXSIZE = UNLIMITED FILEGROWTH = 64MB 设置自增增长大小)
如有必要，重复该命令以添加所需数量的附加 TempDB 文件并将它们添加到新位置。
再次运行 sphelpfile 命令以确认 TempDB 文件已成功创建在新驱动器上：
sqlUSE tempdbEXEC sphelpfile
本地与非本地 TempDB 的性能基准
我们对在 X2ieden 实例上进行的单可用区 (SingleAZ) 和多可用区 (MultiAZ) 部署的 TempDB 性能进行了评估和分析。我们使用 SQLQueryStress 工具 模拟 TempDB 工作负载。
我们使用 dbx2iedn16xlarge 实例进行基准测试，配置如下：
SQL Server 配置：版本：Enterprise Edition 2019 及 CU20TempDB 数据文件：一个主数据文件和六个附加 TempDB 数据文件，每个 352250 MBSQL Server 最大内存：20 GB 限于 20 GB 确保模拟更多disk I/O，，仅用于测试目的
测试细节
使用 SQLQueryStress 工具配置： 迭代次数：500 线程数：1 延迟：0
创建两个存储在 TempDB 中的临时表 #tempOrders 和 #tempOrderDetails。这些表分别填充了 100 万和 500 万条记录，以模拟大规模应用数据。
对于基准测试的查询，我们对这些表进行了连接、聚合和过滤结果。这个复杂的查询测试了 TempDB 处理大数据集和密集操作的效率，提供了关于其性能的洞察。
以下是代码示例：
sql 用于评估 tempdb 性能的工作负载查询
创建用于基准测试的临时表：CREATE TABLE #tempOrders ( OrderID INT PRIMARY KEY CustomerID INT OrderDate DATETIME Amount DECIMAL(102))
CREATE TABLE #tempOrderDetails ( DetailID INT PRIMARY KEY OrderID INT ProductID INT Quantity INT)
将临时表填充示例测试数据： 填充 #tempOrdersINSERT INTO #tempOrdersSELECT TOP 1000000 ROWNUMBER() OVER (ORDER BY aname) AS OrderID ABS(CHECKSUM(NEWID())) 5000 AS CustomerID DATEADD(DAY ABS(CHECKSUM(NEWID())) 365 20230101) AS OrderDate ABS(CHECKSUM(NEWID())) 1000 AS AmountFROM sysallobjects aCROSS JOIN sysallobjects b
填充 #tempOrderDetailsINSERT INTO #tempOrderDetailsSELECT TOP 5000000 ROWNUMBER() OVER (ORDER BY aname) AS DetailID ABS(CHECKSUM(NEWID())) 1000000 AS OrderID ABS(CHECKSUM(NEWID())) 10000 AS ProductID ABS(CHECKSUM(NEWID())) 100 AS QuantityFROM sysallobjects aCROSS JOIN sysallobjects b
基于复杂查询的示例WITH CTE AS ( SELECT oOrderID oCustomerID oOrderDate oAmount dProductID dQuantity (oAmount dQuantity) AS TotalAmount FROM #tempOrders o JOIN #tempOrderDetails d ON oOrderID = dOrderID)SELECT CustomerID SUM(TotalAmount) AS GrandTotalFROM CTEGROUP BY CustomerIDHAVING SUM(TotalAmount) gt 5000ORDER BY GrandTotal DESC
DBCC DROPCLEANBUFFERSDBCC FREEPROCCACHE
DROP TABLE #tempOrdersDROP TABLE #tempOrderDetails
以下截图来源于基准测试。
下表显示，配置了本地存储的 MultiAZ 和 SingleAZ 实例的 TempDB 性能在每次迭代的经过时间和平均实际时间上比其 EBS 存储对应物提升了 20。这表明，对于高 TempDB 工作负载，本地存储配置更为高效。性能改进可归因于本地存储相比 EBS 存储带来的更低延迟与更高的 IOPS每秒 I/O 操作次数。
我们使用的 TempDB 配置如下：
MultiAZ 本地存储在 D 和 E 驱动
