用于增强 Amazon RDS Custom for SQL 服务器性能的最佳实践和参数配置

适用于 SQL Server 的亚马逊关系数据库服务 (Amazon RDS) 定制 版 是一项托管数据库服务，适用于需要访问底层操作系统和数据库 (DB) 环境的传统、自定义和打包应用程序。它有助于自动设置、操作和扩展云中的数据库，同时授予对数据库和底层操作系统的访问权限，以配置设置、安装驱动程序和启用原生功能以满足依赖应用程序的要求。

通常，适用于 SQL Server 的 Amazon RDS 自定义的 默认配置和参数设置 是运行大多数工作负载的理想和最佳配置。但是，您可能需要考虑根据工作负载实现某些实例和数据库级别的参数以及底层 SQL 服务器的最佳实践。

在这篇文章中，我们将讨论微软 SQL Server 参数和最佳实践，以提高在 Amazon RDS Custom for SQL Server 上运行的关键任务工作负载的性能。

数据库级 参数是适用于特定数据库的设置。我们审查了以下数据库级别的参数：

  1. 汽车增长
  2. 自动收缩
  3. 自动关闭
  4. 页面验证
  5. 虚拟日志文件

实例级 参数是适用于整个 SQL Server 实例的设置。我们审查了以下实例级别参数：

  1. 针对临时工作负载进行优化
  2. 备份压缩
  3. 数据库完整性
  4. 索引碎片整理 和 更新统计信息

在实施这些建议之前，您应始终根据自己的工作负载为数据库性能设置基准，以获得准确的性能结果。

数据库级参数和最佳实践

以下参数和最佳实践是在数据库级别配置的。它们有助于提高单个数据库的性能。

1。汽车增长

数据库级别参数 启用自动增长 允许 SQL Server 引擎在空间不足时扩展数据库文件的大小。自动增长设置是为与数据库关联的每个数据库文件配置的。

数据库中过多的自动增长事件会导致磁盘级碎片，从而降低数据库的整体性能。此外，对于每个自动增长事件，数据库文件上的所有活动都将暂停，直到增长操作完成。始终建议在创建数据库文件时将其初始化为适当的大小。

启用自动增长 参数并不能取代适当的预调整和主动维护。尽管如此， 启用自动增长 是一个仅应在紧急情况下使用的选项，以避免由于数据库的巨大增长而导致的空间不足错误导致意外中断。

此外，应适当设置汽车增长值。较小的增长值往往会使数据库变得碎片化，并且会导致大型插入花费更长的时间，因为数据库必须增长几次。例如，将数据库设置为以 64 MB 为增量自动增长不适用于每天增长 1 GB 的数据库。

以下是一些典型数据库工作负载的数据库文件增长率建议。

  • 对于小于 1 GB 的数据库，则为 64 MB
  • 对于介于 1—5 GB 之间的数据库，存储空间为 256 MB
  • 对于大于 5 GB 的数据库，则为 512 MB

在为数据库实现这些值之前，我们建议您测试特定数据库工作负载的性能以实现最佳性能。

您可以使用 T-SQL 或 SSMS 修改此参数。

以下 T-SQL 生成 SQL 语句，使用先前推荐的自动增长增量根据实例中数据库的大小调整其文件增长设置。它首先创建一个临时表，使用 sp_helpd b 存储过程向其中插入数据。然后，它从系统视图中选择数据库信息并生成 SQL 语句。最后，它删除了临时表。

CREATE TABLE #DBSize(DBName sysname, DBSizeMB varchar(50),DBOwner sysname,[dbid] smallint, 
DBCreated varchar(50), DBStatus varchar(5000), DBCompat smallint)

INSERT INTO #DBSize
EXEC sp_helpdb

UPDATE #DBSize
SET DBSizeMB=Ltrim(Rtrim((substring(dbsizemb,1,charindex('MB',dbsizemb)-2))))

DECLARE @db sysname, @fname sysname, @DBSize Decimal(38,2)
DECLARE cur cursor
for
SELECT DB_NAME(a.database_id),a.name , convert(decimal(38,2),b.DBSizeMB)
FROM sys.master_files a
JOIN #DBSIze b
ON DB_NAME(a.database_id)=b.DBName
WHERE a.type=0 AND b.DBName NOT IN ('master','msdb','model','tempdb')

OPEN cur
FETCH NEXT FROM cur
INTO @db, @fname, @DBSize
WHILE @@FETCH_STATUS = 0
BEGIN

DECLARE @SQL VARCHAR(5000)
IF (@DBSize<1025)
SET @SQL='ALTER DATABASE ['+@db+'] modify file (name='''+@fname+''',FILEGROWTH =64MB) ;'
ELSE
IF (@DBSize<5121)
SET @SQL='ALTER DATABASE ['+@db+'] modify file (name='''+@fname+''',FILEGROWTH =256MB) ;'
ELSE
SET @SQL='ALTER DATABASE ['+@db+'] modify file (name='''+@fname+''',FILEGROWTH =512MB) ;'

PRINT @SQL
--Exec(@sql)

FETCH NEXT FROM cur
INTO @db, @fname, @DBSize

END
CLOSE cur
DEALLOCATE cur
DROP TABLE #DBSize

使用 SSMS 更改设置

  1. 连接到 SQL 服务器实例
  2. 选择 数据库
  3. 打开快捷 菜单 （右键单击），然后选择 “ 属性”
  4. 选择 “ 文件” ，然后选择省略号 (...) 打开 “ 更改自动 增长” 对话框
  5. 在 “ 文件增长” 下 ，选择 “ 以兆字节为单位 ” 并输入相应的值
    Enable Autogrowth

2。自动收缩

数据库级别参数 auto_shr ink 有助于自动缩小数据库文件并用于节省磁盘空间。

默认情况下， auto_shrink 参数未启用。启用此参数后，数据库引擎会对数据库文件执行文件收缩操作，将总可用空间减少到 25%。数据库缩减操作会导致大量等待和阻塞；消耗大量 CPU、内存和 I/O 资源；并增加碎片化。此外，你无法控制它何时运行；它是自动完成的。数据库很可能会再次需要释放的磁盘空间。

我们建议保持默认设置，切勿打开 auto_shr ink 参数。如果您在数据库上 启用了 auto_shr ink 以节省磁盘空间，请考虑适当扩展存储以适应数据库的增长。

您可以使用 T-SQL 或 SSMS 修改此参数。

以下 T-SQL 生成 SQL 语句来更改数据库的自动收缩设置。

--Check the current setting
SELECT name, is_auto_shrink_on FROM sys.databases

--Set Auto Shrink OFF 
ALTER DATABASE <DatabaseName> SET AUTO_SHRINK OFF WITH NO_WAIT

--Generates the T-SQL for your change
SELECT 'ALTER DATABASE ['+[NAME]+ '] SET AUTO_SHRINK OFF WITH NO_WAIT' + char(13) + char(10) + 'GO'
FROM sys.databases
WHERE is_auto_shrink_on = 1

使用 SS MS 更改默认设置

  1. 连接到 SQL 服务器实例
  2. 选择 数据库
  3. 打开快捷 菜单 （右键单击），然后选择 “ 属性”
  4. 选择 选项
  5. 在 “自动” 下 ，为 “ 自动缩小” 选择相应的值 Auto Shrink

3。自动关闭

数据库级别参数 auto_clos e 允许 SQL Server 引擎在首次访问数据库时打开和锁定与数据库关联的所有文件。

默认情况下， auto_clos e 参数未启用。当启用此参数并且最后一个连接到该参数的用户关闭连接时，数据库将关闭并释放文件锁。打开和关闭数据库的行为会造成不必要的开销和性能下降。此外， auto_clos e 还会刷新该数据库的缓冲区和过程缓存。

我们建议保持默认设置，切勿打开 auto_clos e 参数。

您可以使用 T-SQL 或 SSMS 修改此参数。

以下 T-SQL 生成 SQL 语句来更改数据库的自动关闭设置。

--Check the current setting
SELECT name, is_auto_close_on FROM sys.databases

--Set Auto close OFF 
ALTER DATABASE <DatabaseName> SET AUTO_CLOSE OFF WITH NO_WAIT

--Generates the T-SQL for your change
SELECT 'ALTER DATABASE ['+[NAME]+ '] SET AUTO_CLOSE OFF WITH NO_WAIT' + char(13) + char(10) + 'GO'
FROM sys.databases
WHERE is_auto_close_on = 1

使用 SS MS 更改默认设置

  1. 连接到 SQL 服务器实例
  2. 选择 数据库
  3. 打开快捷 菜单 （右键单击），然后选择 “ 属性”
  4. 选择 选项
  5. 在 “自动” 下 ，为 “ 自动关闭” 选择相应的值
    Auto-Close

4。页面验证

数据库级别参数 p age_ verify 定义了当页面写入磁盘以及再次从磁盘读取页面时验证页面一致性的 SQL Server 机制。根据选择的页面验证方法，SQL Server 会发现写入到磁盘上的数据是好还是损坏。页面验证选项具有以下三个可配置值：

  • 校验和
  • 翻页检测
  • 没有

默认情况下， page_ verify 参数设置为 CHECKSUM 。 但是，如果数据库将 page_ verify 参数设置为 “ 无” 或 TORN_PAGE_D ETECTION ，则从存储损坏中恢复可能不是一件容易的事。

我们建议将 page_verify 参数的默认设置保留为 CHECKS UM。 启用此选项可能会为典型的数据库工作负载产生 1-2% 的 CPU 开销，但它可以更轻松地从损坏中恢复。

您可以使用 T-SQL 或 SSMS 修改此参数。

以下 T-SQL 生成 SQL 语句来更改数据库的页面验证设置。

--Check the current setting
SELECT name, page_verify_option_desc FROM sys.databases

--Set page_verify to checksum 
ALTER DATABASE <DatabaseName> SET PAGE_VERIFY CHECKSUM WITH NO_WAIT

--Generates the T-SQL for your change
SELECT 'ALTER DATABASE ' + '[' + [name] + ']'+ ' SET PAGE_VERIFY CHECKSUM WITH NO_WAIT'+ char (13) + char(10) + 'GO'
FROM sys.databases
WHERE page_verify_option_desc != 'checksum'

使用 SS MS 更改默认设置

  1. 连接到 SQL 服务器实例
  2. 选择 数据库
  3. 打开快捷 菜单 （右键单击），然后选择 “ 属性”
  4. 选择 选项
  5. 在 “ 恢复” 下 ，为 “ 页面验证” 选择相应的值
    Page Verify

5。VLF 计数

SQL Server 在内部将每个物理事务日志文件划分为较小的部分，称为虚拟日志文件 (VLF)。SQL Server 使用虚拟日志文件作为管理单元，它们可以是活动的，也可以是非活动的。VLF 在存储事务日志的活动部分时处于活动状态，事务日志中包含事务回滚或 SQL Server 意外关闭时保持数据库一致所需的日志记录流。非活动 VLF 包含事务日志中截断（非活动）和未使用的部分。

日志文件中的 VLF 数量增加了事务日志吞吐量、数据库启动恢复和恢复时间。VLF 的适当数量取决于数据库的大小，需要在考虑事务日志文件的可接受增长和缩减后进行评估。应避免事务日志变得过于分散且包含大量小 VLF 的情况。同样，您应避免出现日志的 VLF 太少但非常大的情况。

您可以使用 sys.dm_db_log_ info 动态管理视图检查虚拟日志文件。

以下查询列出了数据库及其 VLF 计数：

SELECT [name], COUNT(l.database_id) AS 'vlf_count' 
FROM sys.databases AS s
CROSS APPLY sys.dm_db_log_info(s.database_id) AS l
GROUP BY [name]

VLF 数量过多会对事务日志活动（例如事务日志备份、数据库崩溃恢复和 DML 性能）产生负面影响。我们建议通过将事务日志缩小到尽可能小并将其增回适当的大小来修复它：

--Shrink the transaction log file to as small as possible
--You may have to repeat the shrink multiple times
DBCC SHRINKFILE(transactionloglogicalfilename, TRUNCATEONLY)

--Modify the transaction log to the appropriate size
ALTER DATABASE databasename MODIFY FILE( NAME = transactionloglogicalfilename, SIZE = newtotalsize)

实例级参数和最佳实践

以下参数和最佳实践是在实例级别配置的。它们有助于提高实例级别的性能。

适用于 SQL Server 的 Amazon RDS Custom 为您提供对主机的完全访问权限，使您能够直接修改实例级参数，就像在自管理环境中一样。

1。针对临时工作负载进行优化

SQL Server 数据库引擎在处理期间生成查询执行计划并将其存储在计划缓存中以供重复使用。但是，默认情况下缓存所有计划可能会导致未使用的计划低效地占用内存。这些一次性计划将存在于计划缓存中，从而低效地消耗服务器的一部分内存。

针对临时工作负载 优化的实例级参数 有助于提高计划缓存效率。默认情况下，不在实例级别启用针对临时工作负载 的 优化。

启用后，此设置指示 SQL Server 在每次首次运行查询时仅存储一个小的已编译计划存根。SQL Server 仅在后续运行中再次引用该小型编译计划存根时存储完整的已编译计划。当存在大量即席查询时，建议启用此参数以缓解内存压力并提高整体性能。

您可以使用 T-SQL 或 SSMS 修改此参数。

要使用 T-SQL 查找一次性缓存计划的数量，请运行以下查询：

SELECT objtype,
cacheobjtype,
SUM(refcounts),
AVG(usecounts),
SUM(CAST(size_in_bytes AS bigint))/1024/1024 AS Size_MB
FROM sys.dm_exec_cached_plans
WHERE usecounts = 1 AND objtype = 'Adhoc'
GROUP BY cacheobjtype, objtype

要查看或更改实例的 “针对临时工作负载进行 优化” 参数值，您可以运行以下查询：

--Check the current setting
SELECT * FROM sys.configurations WHERE name LIKE 'optimize for ad hoc workloads';

--Enable the parameter
EXEC SP_CONFIGURE 'Show Advanced Options', 1
GO
RECONFIGURE
GO
EXEC SP_CONFIGURE 'optimize for ad hoc workloads', 1
GO

使用 SS MS 更改默认设置

  1. 连接到 SQL 服务器实例
  2. 选择实 例
  3. 打开快捷 菜单 （右键单击），然后选择 “ 属性”
  4. 选择 高级
  5. 在 “ 其他” 下 ，为 “针对临时工作负载进行 优化” 选择相应的值
    Optimize for ad hoc workload

2。备份压缩

实例级参数 备份压缩默认值 有助于减少数据库备份所需的时间和空间，从而提高数据库还原的速度。默认情况下，不启用备份压缩。

启用此参数时应考虑以下因素：

  • 备份压缩涉及一些 CPU 开销。它因多个因素而异，例如备份过程中的数据库大小、备份类型、实例上的数据库工作量。如果出现 CPU 争用，可以在受 资源调 控器限制 CPU 使用率的会话中创建优先级较低的压缩备份 。
  • 实现的实际压缩率在很大程度上取决于数据库中数据的构成。

您可以使用 T-SQL 或 SSMS 修改此参数。

您可以使用以下 T-SQL 来更改、查看或更改实例的 备份压缩默认 参数值：

--Check the current setting
SELECT * FROM sys.configurations WHERE name LIKE 'backup compression default'

--Enable the parameter
EXEC SP_CONFIGURE 'backup compression default', 1
RECONFIGURE WITH OVERRIDE
GO

或者，您可以在 T-SQL BACKUP 语句中使用 WITH COMPRESSION 或 WITH NO_CO MPR ES SIO N 来覆盖默认的实例级备份压缩 默认参数。参见以下代码：

--Backup database using compression
BACKUP DATABASE <DatabaseName> TO DISK = '<BackupFileLocation>' WITH INIT, COMPRESSION

使用 SS MS 更改默认设置

  1. 连接到 SQL 服务器实例
  2. 选择实 例
  3. 打开快捷 菜单 （右键单击），然后选择 “ 属性”
  4. 选择 数据库 设置
  5. 配置 “ 压缩备份 ” 选项
    Backup Compression

3。数据库完整性

DBCC CHECKDB 是一个用于检查数据库中对象的逻辑和物理完整性的实用程序，应定期运行。该命令检查数据库一致性，确保对象正确存储且不包含无效值。我们建议每周至少检查一次数据库的完整性，最好是每天检查一次，然后再开始数据库备份。这有助于确保数据库中没有损坏并且可以进行有效的恢复。

您可以利用 SQL Server 本机 维护计划 来创建定期检查数据库完整性的 SQL Server 代理作业。

4。索引碎片整理和更新统计信息

数据库中的索引是与表或视图相关的特殊数据结构，有助于加快数据库查询。与任何其他存储对象一样，索引会随着时间的推移在正常的插入、更新和删除活动中变得碎片化。确定分散程度是有针对性的维护计划的关键组成部分。

常见的数据库维护策略涉及自动脚本，该脚本使用 sys.dm_db_index_physical_stat s 函数来分析索引碎片级别并执行相应的操作。例如，如果分段介于 5-30% 之间，则重新组织，如果大于 30%，则重建。

SQL Server 统计信息是包含有关数据分布信息的系统对象，例如存储在列和索引中的数据的唯一性或选择性。查询优化器使用统计信息来创建提高查询性能的查询计划。SQL Server 严重依赖基于成本的优化，因此准确的数据分布统计数据对于有效使用索引极为重要。优化器对统计数据的依赖意味着这些统计数据必须尽可能准确，否则优化器可能会对运行计划做出错误的选择。

我们建议采用有针对性的方法，使用本机 维护计划 来创建 SQL Server 代理作业，以便对索引进行碎片整理并定期更新统计信息。这种方法逐个索引查看索引碎片，并根据碎片的严重程度重建或重组索引。它还仅更新需要更新的统计信息。从长远来看，与为数据库重建所有索引和统计数据的方法相比，这将消耗更少的资源。

摘要

在这篇文章中，我们讨论了如何在 Amazon RDS Custom for SQL Server 中配置实例级和数据库级参数以及最佳实践，以增强任务关键型数据库工作负载的性能。

如果您有任何意见或问题，请在评论部分分享。

作者简介

Poulami Maity 是亚马逊网络服务的数据库专家解决方案架构师。她与 亚马逊云科技 客户合作，帮助他们将现有数据库迁移到 亚马逊云科技 云并对其进行现代化改造。

Sid Vantair 是 亚马逊云科技 的一名解决方案架构师，负责战略账户。他在关系数据库方面拥有十多年的经验，擅长解决复杂的技术问题以克服客户障碍。工作之余，他珍惜与家人共度时光，培养孩子的好奇心。