sql-server-samples 中的 SmoSamples:基于单元测试的 SMO 性能优化与网络度量实战

• 示例工程
• 数据库
• 教程
• 后端

本文围绕 sql-server-samples 仓库中samples/features/sql-management-objects下的 SmoSamples 样例展开,该样例是一个用 C# 编写的单元测试工程,专门演示 SQL Server Management Objects(SMO)框架的核心特性,并帮助开发者优化其 SMO 应用的性能。读完后,你将理解如何用SetDefaultInitFields减少集合枚举时的往返查询、如何用本地 TCP 代理精确度量 SMO 应用的查询次数与网络流量,以及 URN 转义、事件订阅等 SMO 开发中的关键实践。

样例定位:面向 SMO 应用性能优化的单元测试工程

SmoSamples 的定位在 README.md 中开宗明义:

This unit test project is meant to demonstrate features of the Sql Management Objects framework and to help developers optimize performance of their SMO-based applications.

也就是说,它不是一个功能演示型应用,而是一个"可运行的最佳实践库":每个单元测试都演示 SMO 应用开发的某个具体方面,并针对一个真实可连接的 SQL Server 实例执行,同时通过度量框架量化优化前后的差异。样例的关键信息如下(与原文档保持一致):

• 适用对象:SQL Server 2016(或更高版本)、Azure SQL Database、Azure SQL Data Warehouse
• 关键特性:针对可运行 SQL Server 实例的 SMO 特性演示,附带单元测试与 Docker 文件
• 编程语言:C#

整个目录的组织方式清晰体现了"测试 + 环境准备"两条主线:

samples/features/sql-management-objects/ ├── README.md # 本文的主体文档 ├── runtests.sh # Linux/macOS 一键运行脚本(Docker) ├── runtests.cmd # Windows 一键运行脚本(Docker) ├── prep/ # Docker 环境准备 │ ├── dockerfile # 基于 mssql 2017 镜像,下载并恢复 WideWorldImporters │ ├── entrypoint.sh # 启动 sqlservr 并执行备份恢复 │ ├── restore.sh # 等待 SQL Server 就绪后执行 sqlcmd 恢复 │ └── restore.sql # RESTORE DATABASE WideWorldImporters ... └── src/ # 被测工程(单元测试 + 度量基础设施) ├── SmoSamples.csproj ├── SmoSamples.sln ├── localhost.runsettings ├── CollectionSamples.cs # 集合高效遍历测试 ├── Urn.cs # URN 转义测试 ├── ConnectionHelpers.cs # 连接构造与测试上下文辅助 ├── ConnectionMetrics.cs # 网络/查询度量 └── GenericSqlProxy.cs # 本地内存 TCP 代理

前提条件

按 README.md 的 "Before you begin" 一节,运行该样例需要:

1. 装有完整 WideWorldImporters 样例数据库的 SQL Server 2016(或更高版本),或 Azure SQL Database;或
2. Docker
3. 至少 dotnet 2.2 SDK,或 Visual Studio 2017

从 SmoSamples.csproj 可以确认实际的依赖版本,这也是理解运行环境约束的关键依据:

• 目标框架为netcoreapp2.1;
• SMO 包引用为Microsoft.SqlServer.SqlManagementObjects150.18118.0(150.x 对应 SQL Server 2016/2017 时代的管理对象版本);
• System.Data.SqlClient4.8.6;
• 测试框架采用 MSTest(MSTest.TestAdapter/MSTest.TestFramework1.4.0)提供[TestClass]、[TestMethod]与TestContext,同时引入NUnit3.11.0 的Assert(源码中以using Assert = NUnit.Framework.Assert;的方式混用)。

运行方式一:Docker 一键脚本

如果本地已有独立的 SQL Server 2016+ 或 Azure SQL Database,可以跳过本节;否则最简单的方式是使用 runtests.sh(Linux/macOS)或 runtests.cmd(Windows),两个脚本的完整流程一致,以 sh 版为例:

pwd=Pwd$RANDOM # 生成随机 SA 密码 echo Building the SQL Linux Docker container docker pull mcr.microsoft.com/mssql/server:2017-latest docker build -t sqllinux prep # 基于 prep/dockerfile 构建 echo Running the SQL linux docker image docker run -e ACCEPT_EULA=Y -e SA_PASSWORD=$pwd -e MSSQL_SA_PASSWORD=$pwd \ -h sqlserver --name sqlserver -p:1433:1433 -d --rm sqllinux echo Waiting 2 minutes for SQL server to restore WideWorldImporters sleep 120 echo running tests against SQL 2017 database WideWorldImporters export TEST_PASSWORD=$pwd # 传给 runsettings 中的 [password] 占位符 dotnet publish src dotnet vstest src/bin/Debug/netcoreapp2.1/SmoSamples.dll \ --logger:console --Settings:src/localhost.runsettings echo Terminating docker container docker kill sqlserver

几个值得注意的细节:

• prep/目录下的 dockerfile 基于mcr.microsoft.com/mssql/server:2017-latest镜像,构建阶段就用wget下载 WideWorldImporters 的完整备份WideWorldImporters-Full.bak并放入镜像,因此镜像本身就是"带库"的;
• entrypoint.sh 采用"后台启动 + 恢复"的方式:先以&后台启动/opt/mssql/bin/sqlservr,再执行 restore.sh(sleep 35s等待实例就绪后用sqlcmd -S . -U sa -P $SA_PASSWORD执行恢复脚本),最后tail -f /dev/null保持容器存活;
• restore.sql 中的RESTORE DATABASE WideWorldImporters FROM DISK = "/tmp/backup/WideWorldImporters-Full.bak"用WITH MOVE将WWI_Primary、WWI_Userdata、WWI_Log、WWI_InMemory_Data_1四个文件组分别落盘到/var/opt/mssql/data/下,这也是 WideWorldImporters 包含内存优化数据库文件这一特点所要求的;
• 脚本把随机 SA 密码导出为TEST_PASSWORD环境变量,这正是后面.runsettings连接串中占位符的取值来源(Windows 版 runtests.cmd 用setlocal/endlocal将密码限制在当前脚本作用域内,避免污染用户环境)。

运行方式二:连接已有的 SQL Server 实例

如果目标是独立的 SQL Server 实例或 Azure SQL Database,README 给出的做法是:创建一份自己的.runsettings文件,然后使用 Visual Studio 或dotnet vstest运行单元测试。

仓库内置的 localhost.runsettings 就是模板:

<?xml version="1.0" encoding="utf-8"?> <RunSettings> <TestRunParameters> <Parameter name="connectionString" value="server=localhost;User Id=sa;Password=[password];Timeout=60" /> <Parameter name="testDatabase" value="WideWorldImporters" /> <Parameter name="proxyPort" value="0" /> </TestRunParameters> </RunSettings>

这三个参数的含义,需要结合 ConnectionHelpers.cs 与 ConnectionMetrics.cs 才能说清:

GetTestConnection()还会根据ConnectionType枚举(Default/Integrated/SqlAuth/SqlConnection)构造不同认证方式的ServerConnection,SQL 认证缺用户名或密码时会抛出ArgumentException,避免静默降级为错误连接。

特性一:集合的高效遍历(SetDefaultInitFields)

README 的 "Sample details" 列出的第一个特性领域是Efficient use of collections(高效使用集合),对应测试类 CollectionSamples.cs。它演示的正是 SMO 应用最常见、也最容易被忽视的性能陷阱:SMO 对象属性是延迟初始化的,首次访问才会触发一次元数据查询。

测试方法Collection_iteration_is_faster_with_SetDefaultInitFields的做法:

1. 建立带度量的连接后,先做一次"朴素"遍历:

var server = new Management.Smo.Server(connectionMetrics.ServerConnection); var database = server.Databases[TestContext.GetTestDatabaseName()]; connectionMetrics.Reset(); foreach (Table table in database.Tables) { // 访问 FileGroup 会触发一次额外的查询 Trace.TraceInformation( $"Unoptimized table Name: {table.Name}\tSchema:{table.Schema}\tFileGroup:{table.FileGroup}"); }

SMO 默认只初始化集合枚举所需的最小字段集,循环体内每次访问table.FileGroup都会对单张表发起一次查询——即俗称的 N+1 问题。

2. 调用SetDefaultInitFields预取所需属性后,再做同样的遍历:

server.SetDefaultInitFields(typeof(Table), "Name", "Schema", "FileGroup"); database.Tables.Refresh(); // 让集合按新的初始化字段重新加载 foreach (Table table in database.Tables) { Trace.TraceInformation( $"Optimized table Name: {table.Name}\tSchema:{table.Schema}\tFileGroup:{table.FileGroup}"); }

3. 最后用 NUnit 断言优化后的度量严格更优:

Assert.That(optimizedMetrics.BytesRead, Is.LessThan(unoptimizedMetrics.BytesRead)); Assert.That(optimizedMetrics.BytesSent, Is.LessThan(unoptimizedMetrics.BytesSent)); Assert.That(optimizedMetrics.ConnectionCount, Is.AtMost(unoptimizedMetrics.ConnectionCount)); Assert.That(optimizedMetrics.QueryCount, Is.LessThan(unoptimizedMetrics.QueryCount));

这里有两个源码级细节值得学习:

• SetDefaultInitFields设置的是该 Server 对象上 Table 类型的默认初始化字段集,所以调用之后必须database.Tables.Refresh()让已加载的集合按新规则重新初始化,否则旧集合仍持有旧的加载状态;
• 测试没有硬编码任何行数或字节数阈值,而是对同一环境做前后对照断言,这让测试在任意规模的 WideWorldImporters 恢复结果上都成立——这正是"用单元测试固化性能优化"的思路。

特性二:SQL 查询捕获与网络度量(StatementExecuted + 本地 TCP 代理)

README 列出的Sql query capture与Events两个特性领域,在源码中由 ConnectionMetrics.cs 和 GenericSqlProxy.cs 共同实现,它们构成了一套可复用的"网络层探针"。

ConnectionMetrics维护四个计数器,全部来自事件的回调:

SetupMeasuredConnection的组装过程(见 ConnectionMetrics.cs 第 68–84 行)是理解"事件 + 代理"协作的关键:

public static ConnectionMetrics SetupMeasuredConnection(TestContext testContext, int latencyPaddingMs = 0) { var connectionString = testContext.GetConnectionString(); var proxy = new GenericSqlProxy(connectionString); if (latencyPaddingMs > 0) { // 在每次向客户端写数据前注入固定延迟,模拟高延迟网络 proxy.OnWriteClient += (o,e) => DelayWrite(latencyPaddingMs, e); } var port = testContext.Properties.ContainsKey("proxyPort") ? Convert.ToInt32(testContext.Properties["proxyPort"]) : 0; var sqlConnection = new SqlConnection(proxy.Initialize(port)); var serverConnection = new ServerConnection(sqlConnection); return new ConnectionMetrics(serverConnection, proxy); }

注意ServerConnection是直接建立在SqlConnection之上的——即让 SMO复用一条已经指向代理的 ADO.NET 连接,因此 SMO 发出的所有 TDS 流量都会经过代理,字节数统计才能闭环。latencyPaddingMs参数(默认 0)则通过OnWriteClient事件在每次响应回传前Thread.Sleep,用于人为放大网络延迟,观察优化策略在高延迟环境下的收益。

支撑这一切的 GenericSqlProxy 是一个纯内存实现的 TCP 转发代理,源码结构上可以归纳为:

• Initialize(int localPort = 0)在回环地址上启动TcpListener(port=0时随机分配),把原始连接串的DataSource解析出目标主机/端口(默认 1433),然后返回一条指向tcp:127.0.0.1,<代理端口>的新连接串;
• 每接受一个客户端连接,就同时建立到真实 SQL Server 的上游连接,并分别启动ForwardToSql(客户端→服务端)与ForwardToClient(服务端→客户端)两个转发循环;
• 两个转发循环在每次写缓冲区之前触发OnWriteHost/OnWriteClient事件,事件参数StreamWriteEventArgs携带本次写入的BytesWritten,这正是BytesSent/BytesRead的统计口径;
• 缓冲大小固定为 128 KB,源码注释说明原因是"足够容纳大多数单条响应,避免过度注入延迟";
• Dispose()通过CancellationTokenSource取消接受循环并停止监听器,测试侧的ConnectionMetrics.Dispose()同时解绑四个事件、释放底层SqlConnection与代理,保证测试之间互不串扰。

这套"事件订阅 + 透明代理"的组合拳,展示了 SMO 开发者在不修改被测代码的前提下,如何精确量化一个 SMO 操作序列的真实网络开销——这比只看执行时间更能定位问题,因为本地环境的时间差异可能完全掩盖查询次数差异,而在高延迟的 Azure 场景中则会被成倍放大。

特性三:URN 与转义

README 列出的URNs领域对应 Urn.cs 中的UrnSamples测试类,它演示了 SMO 的 URN(URN,用于定位对象的路径式标识)三个要点:

1. URN 值包含引号时必须转义。测试创建一个名为Name'With'Quotes的表,验证table.Urn.GetNameForType(Table.UrnSuffix)会原样返回带引号的名称;随后证明:直接用未转义的名称拼 URN 调用server.GetSmoObject会抛出FailedOperationException,而用Urn.EscapeString("Name'With'Quotes")转义后则能正确取回对象:

table = (Table)server.GetSmoObject( $"Server/Database[@Name='{database.Name}']/Table[@Name='{Urn.EscapeString("Name'With'Quotes")}']"); Assert.That(table.Name, Is.EqualTo("Name'With'Quotes"), "Table with escaped name");

这对"把用户输入或对象名拼进 URN"的管理类工具是必备的安全与正确性实践。

2. Server 对象的 URN Name 与实例真名一致:server.Urn.Value应等于Server@Name='<connection.TrueName>',测试借此校验 URN 语义与ServerConnection.TrueName(解析后的真实实例名,而非连接串里写的别名)的对应关系。

3. URN 的类型是路径的最后一节:new Urn("Server[@Name='server']/Database[@Name='database']/Table[@Name='table']").Type应等于Table.UrnSuffix。

另外,UrnSamples依赖的ExecuteWithDbDrop辅助方法(位于 ConnectionHelpers.cs 第 117–140 行)也值得一提:它用"TestName + Random.Next()"生成唯一库名,Create()后执行被测逻辑,finally中尽力Drop()(失败只记 Trace 不抛出),保证每个 URN 测试都在干净的临时库上进行,互不污染。

其余特性领域与工程结构说明

README 的 "Sample details" 共列出五个特性领域:1) 集合高效使用;2) SQL 查询捕获;3) 事件;4) URN;5) 脚本生成(Script generation)。从当前提交到仓库的源码结构看,前四个领域在src/下都有明确的对应实现(集合与 URN 为独立测试类,查询捕获与事件体现为ConnectionMetrics/GenericSqlProxy度量基础设施,且其本身即被各测试类复用);第五个领域"脚本生成"目前未见独立的测试类,属于 README 声明的特性范围,阅读时建议以 README 为纲、以现有源码为实证。

依赖与延伸阅读

• SMO 的官方 NuGet 包为Microsoft.SqlServer.SqlManagementObjects,本仓库锁定的版本是 150.18118.0(见 SmoSamples.csproj 第 23 行);如需升级,注意大版本号与 SQL Server 版本代的对应关系;
• WideWorldImporters 完整备份是测试数据基础,Docker 路线由 prep/dockerfile 在构建期下载,自行部署时可使用 WideWorldImporters 官方发布渠道获取WideWorldImporters-Full.bak并执行与 restore.sql 等价的RESTORE ... WITH MOVE语句(四个文件组缺一不可,否则会因找不到WWI_InMemory_Data_1报错);
• 若要在 CI 中复用本样例的度量手法,可参考ConnectionMetrics.SetupMeasuredConnection的组装模式:先建代理、再建SqlConnection、最后用该SqlConnection构造ServerConnection,三步顺序不可颠倒。

小结

SmoSamples 样例的价值在于"可度量":它把 SMO 开发中三类高频问题——集合延迟初始化导致的 N+1 查询、对服务端语句数量不可见、URN 拼接不转义——各自转化为一个可在真实实例上运行、带断言的单元测试,并提供了SetDefaultInitFields、本地 TCP 代理 +StatementExecuted事件、Urn.EscapeString三个可直接迁移到自己 SMO 应用中的解法。配合 runtests.sh/runtests.cmd 与 prep 目录下的 Docker 文件,整套环境可以在几分钟内从零搭起,是学习 SMO 性能工程化的现成参照。

• 示例工程
• 数据库
• 教程
• 后端