优化数据库文件组

项目
10/14/2023

文件输入/输出 (I/O) 争用通常是生产BizTalk Server环境中的限制因素或瓶颈。 BizTalk Server是一个数据库密集型应用程序，而BizTalk Server使用的SQL Server数据库则非常占用文件 I/O。

本主题介绍如何充分利用 SQL Server 的文件和文件组功能，以最大程度地减少文件 I/O 争用的发生并提高BizTalk Server解决方案的整体性能。

概述

随着吞吐量的增加，每个BizTalk Server解决方案最终都会遇到文件 I/O 争用。 I/O 子系统或存储引擎是任何关系数据库的关键组件。成功的数据库实现通常要求在项目的初期阶段进行仔细的规划。此规划应考虑下列事项：

使用哪种类型的磁盘硬件，如 RAID（独立磁盘冗余阵列）设备。
如何使用文件和文件组在磁盘上分配数据。有关在 SQL Server 中使用文件和文件组的详细信息，请参阅数据库文件和文件组。
实现最佳索引设计以提高访问数据时的性能。有关设计索引的详细信息，请参阅设计索引。
如何设置SQL Server配置参数以获得最佳性能。有关为SQL Server设置最佳配置参数的详细信息，请参阅服务器配置选项。

BizTalk Server的主要设计目标之一是确保消息永不丢失。为了降低消息丢失的可能性，在处理消息时，通常会将消息写入 MessageBox 数据库。当业务流程处理消息时，消息将写入业务流程中每个持久性点的 MessageBox 数据库。这些持久性点会导致 MessageBox 将消息和相关状态写入物理磁盘。在更高的吞吐量下，这种持久性可能会导致大量磁盘争用，并可能成为瓶颈。

已证明充分利用 SQL Server 中的文件和文件组功能可以有效地解决文件 IO 瓶颈并提高BizTalk Server解决方案的整体性能。此优化只能由经验丰富的SQL Server数据库管理员完成，并且只有在正确备份所有BizTalk Server数据库之后才能完成。应在BizTalk Server环境中的所有SQL Server计算机上执行此优化。

可以使用SQL Server文件和文件组来提高数据库性能，因为此功能允许跨多个磁盘、多个磁盘控制器或 RAID (独立磁盘) 系统的冗余阵列创建数据库。例如，如果计算机上有四个磁盘，那么可以创建一个由三个数据文件和一个日志文件组成的数据库，每个磁盘上放置一个文件。访问数据时，四个读/写头可以并行访问数据。这大大加快了数据库操作的速度。有关为SQL Server磁盘实现硬件解决方案的详细信息，请参阅 SQL Server 联机https://go.microsoft.com/fwlink/?LinkID=71419丛书中的“数据库性能”。

此外，文件和文件组支持数据放置，因为可以在特定的文件组中创建表。这可以提高性能，因为给定表的所有文件 I/O 都可以定向到特定磁盘。例如，一个经常使用的表可以放置在位于一个磁盘的文件组中的某个文件上，而数据库中访问量较低的其他表可以位于另一个文件组（位于另一个磁盘上的）中的不同文件上。

数据库层的瓶颈中详细介绍了文件 IO 瓶颈。文件 I/O (磁盘 I/O) 瓶颈的最常见指标是“物理磁盘：平均磁盘队列长度”计数器的值。如果任何 SQL Server 上任何给定磁盘的“物理磁盘：平均磁盘队列长度”计数器的值大于大约 3，则文件 I/O 可能是瓶颈。

如果应用文件或文件组优化无法解决文件 I/O 瓶颈问题，则可能需要通过添加额外的物理或 SAN 驱动器来提高磁盘子系统的吞吐量。

本主题介绍如何手动应用文件和文件组优化，但也可以编写这些优化脚本。本主题末尾提供了一个示例 SQL 脚本。请务必注意，需要修改此脚本，以适应SQL Server数据库 (任何给定BizTalk Server解决方案) 使用的文件、文件组和磁盘配置。

使用默认BizTalk Server配置创建的数据库

根据配置BizTalk Server时启用的功能，最多可以在 SQL Server 中创建 13 个不同的数据库，所有这些数据库都在默认文件组中创建。 SQL Server的默认文件组是 PRIMARY 文件组，除非使用 ALTER DATABASE 命令更改默认文件组。下表列出了在配置BizTalk Server时启用所有功能的情况下，在 SQL Server 中创建的数据库。

BizTalk Server 数据库

数据库	默认数据库名称	说明
配置数据库	BizTalkMgmtDb	BizTalk Server组中所有BizTalk Server实例的中心元信息存储。
BizTalk MessageBox 数据库	BizTalkMsgBoxDb	存储订阅谓词。它是一个主机平台，并保留每个BizTalk Server主机的队列和状态表。 MessageBox 数据库还存储消息和消息属性。
BizTalk 跟踪数据库	BizTalkDTADb	存储BizTalk Server跟踪引擎跟踪的业务和运行状况监视数据。
BAM 分析数据库	BAMAnalysis	SQL Server Analysis Services数据库，用于保留业务活动的聚合历史数据。
BAM 星型架构数据库	BAMStarSchema	转换从用于 OLAP 处理的业务活动监视收集的数据。使用 BAM 分析数据库时需要此数据库。
BAM 主导入数据库	BAMPrimaryImport	存储来自“业务活动”的事件，然后在活动实例之后查询进度和数据。此数据库还执行实时聚合。
BAM 存档数据库	BAMArchive	存储订阅谓词。 BAM 存档数据库可最大程度地减少 BAM 主导入数据库中业务活动数据的累积。
SSO 数据库	SSODB	安全存储接收位置的配置信息。存储 SSO 关联应用程序的信息，以及所有关联应用程序的加密用户凭据。
规则引擎数据库	BizTalkRuleEngineDb	存储库： - 策略，这些策略是相关规则集。 - 词汇，是规则中数据引用的用户友好、特定于域的名称的集合。
跟踪 Analysis Server 管理数据库	BizTalkAnalysisDb	存储业务和运行状况监视 OLAP 多维数据集。

数据文件和日志文件的分离

如上所述，默认BizTalk Server配置会将 MessageBox 数据库放入默认文件组中的单个文件中。默认情况下，MessageBox 数据库的数据和事务日志放置在相同的驱动器和路径上。这样做是为了适应具有单个磁盘的系统。在生产环境中，单个文件/文件组/磁盘配置 不是最佳 配置。为了获得最佳性能，数据文件和日志文件应放置在单独的磁盘上。

注意

日志文件不包括在文件组内。日志空间与数据空间分开管理。

分发BizTalk Server数据库的 80/20 规则

由于磁盘 I/O 争用或数据库争用，大多数BizTalk Server解决方案中争用main源是 messageBox 数据库BizTalk Server。这在单个和多 MessageBox 方案中都是如此。合理地假设分发 BizTalk 数据库的值的 80% 将派生自优化 MessageBox 数据文件和日志文件。下面详细介绍的示例方案侧重于优化 MessageBox 数据库的数据文件。然后，可以根据需要对其他数据库执行这些步骤，例如，如果解决方案需要广泛的跟踪，则跟踪数据库也可以进行优化。

手动将文件添加到 MessageBox 数据库，分步操作

本部分介绍手动将文件添加到 MessageBox 数据库时可以遵循的步骤。在此示例中，将添加三个文件组，然后向每个文件组添加一个文件，以便跨多个磁盘分发 MessageBox 的文件。

注意

为了完成本指南的性能测试，文件组通过使用脚本进行了优化，该脚本将作为BizTalk Server性能优化指南的一部分发布。以下步骤仅供参考。

在 SQL Server 上手动将文件添加到 MessageBox 数据库

打开SQL Server Management Studio以显示“连接到服务器”对话框。
在“连接到服务器”对话框的“服务器名称”字段中，输入包含BizTalk Server MessageBox 数据库的SQL Server实例的名称，然后单击“连接”按钮以显示“Microsoft SQL Server Management Studio”对话框。

在SQL Server Management Studio的“对象资源管理器”窗格中，展开“数据库”以查看此SQL Server实例的数据库。
右键单击要为其添加文件的数据库，然后单击“ 属性” 以显示数据库的“ 数据库属性 ”对话框。
在“ 数据库属性 ”对话框中，选择“ 文件组 ”页。单击“ 添加 ”按钮为 BizTalkMsgBoxDb 数据库创建其他文件组。在下面的示例中，添加了另外三个文件组。
在 “数据库属性” 对话框中，选择 “文件” 页。

单击“ 添加 ”按钮创建要添加到文件组的其他文件，然后单击“ 确定”。 MessageBox 数据库现在分布在多个磁盘上，这将提供与单个磁盘配置相较的显著性能优势。

在下面的示例中，为之前创建的每个文件组创建一个文件，并且每个文件都放置在单独的磁盘上。

用于将文件组和文件添加到 BizTalk MessageBox 数据库的示例 SQL 脚本

下面的示例 SQL 脚本执行在上一部分中手动完成的任务相同。此示例脚本假定存在不同的逻辑驱动器 G 到 J。该脚本为每个文件组创建文件组和文件，并将日志文件放在 J 驱动器上。

注意

由于SQL Server按顺序写入其日志文件，因此为SQL Server数据库创建多个日志文件无法实现性能优势。

-- Filegroup changes are made using the master database
USE [master]
GO

-- Script-wide declarations
DECLARE @CommandBuffer nvarchar(2048)
DECLARE @FG1_Path nvarchar(1024)
DECLARE @FG2_Path nvarchar(1024)
DECLARE @FG3_Path nvarchar(1024)
DECLARE @Log_Path nvarchar(1024)

-- Set the default path for all filegroups
SET @FG1_Path = N'G:\BizTalkMsgBoxDATA\'
SET @FG2_Path = N'H:\BizTalkMsgBoxDATA\'
SET @FG3_Path = N'I:\BizTalkMsgBoxDATA\'
SET @Log_Path = N'J:\BizTalkMsgBoxLog\'

ALTER DATABASE [BizTalkMsgBoxDb] ADD FILEGROUP [BTS_MsgBox_FG1]
SET @CommandBuffer = N'ALTER DATABASE [BizTalkMsgBoxDb] ADD FILE ( NAME = N''BizTalkMsgBoxDb_FG1'', FILENAME = N''' + @FG1_Path +
N'BizTalkMsgBoxDb_FG1.ndf'' , SIZE = 102400KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10240KB ) TO FILEGROUP [BTS_MsgBox_FG1]'
EXECUTE (@CommandBuffer)

ALTER DATABASE [BizTalkMsgBoxDb] ADD FILEGROUP [BTS_MsgBox_FG2]
SET @CommandBuffer = N'ALTER DATABASE [BizTalkMsgBoxDb] ADD FILE ( NAME = N''BizTalkMsgBoxDb_FG1'', FILENAME = N''' + @FG2_Path +
N'BizTalkMsgBoxDb_FG2.ndf'' , SIZE = 102400KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10240KB ) TO FILEGROUP [BTS_MsgBox_FG2]'
EXECUTE (@CommandBuffer)

ALTER DATABASE [BizTalkMsgBoxDb] ADD FILEGROUP [BTS_MsgBox_FG3]
SET @CommandBuffer = N'ALTER DATABASE [BizTalkMsgBoxDb] ADD FILE ( NAME = N''BizTalkMsgBoxDb_FG1'', FILENAME = N''' + @FG3_Path +
N'BizTalkMsgBoxDb_FG3.ndf'' , SIZE = 102400KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10240KB ) TO FILEGROUP [BTS_MsgBox_FG3]'
EXECUTE (@CommandBuffer)

ALTER DATABASE [BizTalkMsgBoxDb] MODIFY FILE ( NAME = N'BizTalkMsgBoxDb_log', SIZE = 10240KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10240KB )

GO -- Completes the previous batch, as necessary

以下示例 SQL 脚本可用于将特定文件组设置为默认文件组：

USE [BizTalkMsgBoxDb]
GO
declare @isdefault bit
SELECT @isdefault=convert(bit, (status & 0x10)) FROM sysfilegroups WHERE groupname=N'BTS_MsgBox_FG1'
if(@isdefault=0)
ALTER DATABASE [BizTalkMsgBoxDb] MODIFY FILEGROUP [BTS_MsgBox_FG1] DEFAULT
GO

脚本的优点是脚本可以快速执行多个任务，可以精确重现，并减少人为错误的可能性。脚本的缺点是执行错误编写的脚本可能会导致严重的问题，这些问题可能需要从头开始重新配置BizTalk Server数据库。因此，在生产环境中执行 SQL 脚本（如本主题中列出的示例脚本）之前，必须经过全面测试，这一点至关重要。

通过