企业官网建设流程全解析

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

本文基于 samples/applications/iot-smart-grid 示例展开。该示例演示了如何利用 SQL Server 2016（或更高版本）的内存优化数据库能力，支撑 IoT 智能电网（Smart Grid）场景下极高的数据输入速率：大量 IoT 电表持续向数据库发送用电量测量数据。读完本文，你将掌握内存优化表（Memory Optimized Tables）、内存优化表值参数（TVP）、原生编译存储过程（Natively Compiled Stored Procedures）、聚集列存储索引（CCI）与 Power BI 的组合应用方式，并能在本地或 Azure SQL Database 上完整复现这一数据摄取与实时分析管道。

关于本示例

本示例（官方仓库中的 README）核心信息如下：

• 适用版本：SQL Server 2016（或更高版本）Enterprise / Developer / Evaluation 版，以及 Azure SQL Database；
• 关键特性：内存优化表与表值参数（TVP）、原生编译存储过程、聚集列存储索引（CCI）、Power BI 可视化；
• 工作负载：面向 IoT 的数据摄取（Data Ingestion）；
• 编程语言：.NET C# 与 T-SQL；
• 作者：Perry Skountrianos（perrysk-msft）。

示例模拟的是一条完整的 IoT 数据链路：多个 IoT 电表不断产生用电量测量数据 → 数据生成器（Data Generator）以多异步任务方式批量写入 → 内存优化表承接高频写入 → 历史数据定期卸载到聚集列存储索引 → Power BI 完成实时运营分析可视化。

端到端架构与数据流

从控制台客户端的源码头注释（ConsoleClient/Program.cs）可以看到官方对整体场景的完整描述：数据生成器模拟的是经典的“高数据输入速率/冲击吸收器（Shock Absorber）”模式——数据洪峰先被内存优化表以极低延迟吸收，再以可控节奏卸载到面向分析的列存储结构中，从而避免高频随机写入拖垮基于磁盘的传统行存储。

数据流可以概括为如下五个环节：

1. 生成：SqlDataGenerator中每个异步任务（Async Task）生成一批随机值记录，模拟单个 IoT 电表的测量数据；
2. 传输：批次数据打包进内存优化的表值参数（TVP）udtMeterMeasurement；
3. 写入：调用原生编译存储过程InsertMeterMeasurement，把 TVP 一次性插入内存优化表MeterMeasurement；
4. 卸载：后台任务周期性调用InsertMeterMeasurementHistory，将MeterMeasurement中指定MeterID的历史数据转移到聚集列存储索引表MeterMeasurementHistory，并删除内存表中的对应记录，保持“热”内存区容量可控；
5. 可视化：Power BI 通过视图vwMeterMeasurement读取按秒聚合的实时指标，形成仪表板。

环境准备与前置条件

软件前置条件

1. SQL Server 2016（或更高版本），或一个 Azure SQL Database；
2. Visual Studio 2015（或更高版本），并安装最新版 SSDT（SQL Server Data Tools）；
3. Power BI Desktop（用于加载随示例提供的PowerDashboard.pbix报表）。

Azure 前置条件

需要在 Azure 订阅中具备创建 Azure SQL Database 的权限（示例可发布到 Azure SQL Database，详见下文“发布数据库”小节）。

快速运行（脚本 + 数据生成器方式）

这是不借助 Visual Studio 的最快路径，共 5 步：

1. 创建数据库：在 SQL Server 实例上创建一个名为PowerConsumption的数据库（这是示例默认连接的库名）；
2. 创建数据库对象：在PowerConsumption中运行 setup-or-reset-demo.sql，该脚本会先DROP再CREATE全部对象（内存优化表、CCI 历史表、TVP、两个存储过程与视图），可反复执行用于重置演示环境；
3. 启动数据生成器：运行WinFormsClient（Client.exe）或ConsoleClient（ConsoleClient.exe）；
4. 检查连接字符串：如需修改，编辑 App.config。默认配置连接本地默认 SQL Server 实例并使用集成身份验证（Server=.;Database=PowerConsumption;Integrated Security=True）；
5. 加载 Power BI 报表：WinForms 客户端点击左下角链接，控制台客户端输入REPORT命令。

注意：内存优化表依赖数据库中的内存优化数据文件组（MEMORY_OPTIMIZED_DATA）。在本地 SQL Server 上执行上述脚本前，需要先为PowerConsumption数据库配置该文件组（见下文mod.sql说明）；发布到 Azure SQL Database 时平台会自动管理，无需手动创建。

从 Visual Studio 构建运行（完整开发方式）

1. 克隆本仓库（或下载 zip），用 Git for Windows 或直接下载均可；
2. 打开仓库根目录下的IoT-Smart-Grid.sln解决方案文件；
3. 示例包含两个工作负载客户端：ConsoleClient（控制台）与WinFormsClient（Windows 窗体）。右键目标项目，选择“设为启动项目”（Set as StartUp Project）；
4. 在 Visual Studio 的“生成”菜单中执行生成解决方案（或按 F6）；
5. 修改 App.config 配置（位于 Solution Items 解决方案文件夹中）：根据自身硬件规格调整参数（详见下一节参数表）；
6. 发布数据库：
   • 右键Db数据库项目（SQL Server Database Project），选择Publish；
   • 点击Edit...配置连接字符串，推荐使用数据库名PowerConsumption（示例默认运行库名）；
   • 点击Publish完成发布；
   • 发布到 Azure SQL 时的额外要求：① 将 Db 项目的目标平台切换为Microsoft Azure SQL Database V12；② 发布前注释掉 Db/Storage/mod.sql 中的 T-SQL；
7. 构建并运行应用：注意不要使用调试器（Debugger）运行，否则会拖慢应用速度、影响负载真实性；
8. 启动工作负载：ConsoleClient 中输入START；WinFormsClient 中点击Start按钮；
9. 启动 Power BI 报表：ConsoleClient 中在命令行输入REPORT；WinFormsClient 中点击Power BI Report链接；
10. 在 Power BI Desktop 中点击Edit Queries → Source，确认 Server 与 Database Name 与第 6 步发布的连接信息一致，点击 OK 应用。

App.config 参数详解

App.config 是工作负载调优的核心。下表完整列出全部配置项及其仓库中携带的默认值：

这些参数会直接映射到数据生成器内部。例如 DataGenerator/SqlDataGenerator.cs 中，BatchSize、Delay均带有参数校验逻辑（Validate(...)），而Rps/Drps属性则实时计算每秒插入 / 删除行数，供客户端界面与rpsFrequency轮询逻辑使用。

数据库对象源码级解析

下面逐一对 setup-or-reset-demo.sql 与 Db 数据库项目（Db.sqlproj）中的对象进行剖析。SSDT 项目中的对象定义与重置脚本略有差异（如哈希桶数量不同，见下文），但语义一致。

1. 内存优化热表 MeterMeasurement

Db/dbo/Tables/MeterMeasurement.sql 定义了承接高频写入的“热”表：

• MeasurementID BIGINT IDENTITY主键使用非聚集哈希索引（PRIMARY KEY NONCLUSTERED HASH），SSDT 版本BUCKET_COUNT = 16777216；
• MeterID列上另建一个非聚集哈希索引（SSDT 版本BUCKET_COUNT = 1048576），用于支撑按电表 ID 的快速查找与卸载；
• 表级选项MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY：仅持久化架构、不持久化数据行，这是追求极致写入性能的关键——重启后数据会清空，属于典型的缓存/缓冲型内存表用法。

setup-or-reset-demo.sql中对应定义（L18-L30）使用了BUCKET_COUNT = 10000000与1000000。哈希索引桶数应设置为预期唯一键数量的幂次级别，过小会导致桶链过长、性能退化。

2. 内存优化表值参数 udtMeterMeasurement

Db/dbo/User Defined Types/udtMeterMeasurement.sql 定义了一个内存优化的表值参数类型（MEMORY_OPTIMIZED = ON），结构上比目标表多一列RowID（带哈希索引IX_RowID，SSDT 版本BUCKET_COUNT = 131072），用于在批内唯一标识行。TVP 是批量数据进入原生编译存储过程的载体——相比逐行INSERT，TVP 把整批数据作为一个参数传入，大幅减少网络往返与编译开销。

3. 原生编译存储过程 InsertMeterMeasurement

Db/dbo/Stored Procedures/InsertMeterMeasurement.sql 是写入路径的性能核心：

CREATE PROCEDURE [dbo].[InsertMeterMeasurement] @Batch AS dbo.udtMeterMeasurement READONLY, @BatchSize INT WITH NATIVE_COMPILATION, SCHEMABINDING AS BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL=SNAPSHOT, LANGUAGE=N'English') INSERT INTO dbo.MeterMeasurement (MeterID, MeasurementInkWh, PostalCode, MeasurementDate) SELECT MeterID, MeasurementInkWh, PostalCode, MeasurementDate FROM @Batch END;

要点：

• WITH NATIVE_COMPILATION：过程被编译为机器码，执行时不再逐次解释，是内存 OLTP 低延迟写入的关键；
• SCHEMABINDING：与底层表/类型强绑定，保证编译产物稳定；
• BEGIN ATOMIC要求原生编译过程必须使用原子块，这里显式指定SNAPSHOT隔离级别，避免锁开销；
• @Batch AS ... READONLY：TVP 以只读方式传入，随后一条INSERT ... SELECT完成整批落表。

4. 卸载存储过程 InsertMeterMeasurementHistory

Db/dbo/Stored Procedures/InsertMeterMeasurementHistory.sql 是普通（非原生编译）存储过程，在单个事务内完成“搬移”：

BEGIN TRAN INSERT INTO dbo.MeterMeasurementHistory (...) SELECT ... FROM dbo.MeterMeasurement WITH (SNAPSHOT) WHERE MeterID = @MeterID DELETE FROM dbo.MeterMeasurement WITH (SNAPSHOT) WHERE MeterID = @MeterID COMMIT

它按@MeterID把内存热表中的记录插入列存储历史表，再删除内存表中的对应行。WITH (SNAPSHOT)提示配合内存表的乐观并发模型，保证搬移过程的一致性。这正是“冲击吸收器”模式的收尾环节：高频写入数据在内存表短暂驻留后，以批量方式沉淀到面向分析的列存储。

5. 聚集列存储索引历史表 MeterMeasurementHistory

Db/dbo/Tables/MeterMeasurementHistory.sql 定义的MeterMeasurementHistory表本身是普通堆表，但叠加了CREATE CLUSTERED COLUMNSTORE INDEX [ix_MeterMeasurementHistory]。列存储索引以列式压缩存储海量历史数据，支持对“全部历史 + 实时内存数据”的混合查询，这正是实时运营分析（Real-Time Operational Analytics）的核心能力。setup-or-reset-demo.sql版本（L32-L40）还显式指定了COMPRESSION_DELAY = 0。

6. 实时聚合视图 vwMeterMeasurement

Db/dbo/Views/vwMeterMeasurement.sql 面向 Power BI 提供秒级聚合结果：按PostalCode与“精确到秒”的MeasurementDate（通过DATETIMEFROMPARTS重组）分组，输出MeterCount（电表数）与AvgMeasurementInkWh（平均用电量）。视图使用WITH (NOLOCK)读取内存表，避免快照开销，保证实时性。

7. mod.sql 与内存优化文件组

Db/Storage/mod.sql 是 Db 项目的预部署脚本，目前内容整体处于注释状态：

--ALTER DATABASE [$(DatabaseName)] ADD FILEGROUP [mod] CONTAINS MEMORY_OPTIMIZED_DATA;

• 在本地 SQL Server上，如果目标数据库尚未创建MEMORY_OPTIMIZED_DATA文件组，需要先取消注释并执行该语句（或等效操作），否则无法创建内存优化表；
• 在Azure SQL Database上，内存优化文件组由平台自动管理，无需（也不允许）手动创建，这正是 README 要求发布 Azure SQL 前注释掉此脚本的原因。

8. 数据生成器实现原理

DataGenerator/SqlDataGenerator.cs 是负载引擎：

• 每个异步任务维护自己的 SQL 连接，循环执行“生成一批样本数据 → 调用插入存储过程”，直至被用户停止；任务以CancellableTask封装并登记在ConcurrentDictionary<int, CancellableTask>中（可参见 DataGenerator/CancellableTask.cs）；
• 随机数使用ThreadLocal<Random>，避免多线程共享Random实例带来的竞争与序列退化；
• 邮编数据来自内置的postalCodes数组（西雅图地区邮编），配合随机电表 ID、时间戳与用电量生成真实感数据；
• Rps/Drps属性基于Stopwatch与累计行数实时计算每秒插入/删除行数，供界面与日志展示吞吐；
• 任务还受numberOfRowsOfloadLimit约束触发卸载逻辑，保持内存表规模在预期水位。

控制台客户端 ConsoleClient/Program.cs 提供交互命令循环：START（启动负载）、STOP（停止）、HELP（帮助）、REPORT（打开 Power BI 报表）、EXIT（退出）。WinForms 客户端（WinFormsClient/FrmMain.cs）提供等价的图形界面操作。

运行状态观测与验证

setup-or-reset-demo.sql顶部注释区提供了一组可直接使用的验证查询，用于在演示过程中观测数据库内部状态：

SELECT * FROM sys.dm_db_resource_stats SELECT * FROM sys.dm_exec_requests SELECT * FROM sys.dm_db_column_store_row_group_physical_stats SELECT COUNT(*) FROM [dbo].[MeterMeasurementHistory] with (nolock) SELECT COUNT(*) FROM [dbo].[MeterMeasurement]

• sys.dm_db_resource_stats：数据库资源使用统计（Azure SQL 上尤为有用）；
• sys.dm_exec_requests：当前执行的请求，观察写入与卸载任务的并发情况；
• sys.dm_db_column_store_row_group_physical_stats：列存储行组物理统计，检查历史数据落盘与压缩状态；
• 两个COUNT(*)：对比内存热表与列存储历史表的行数变化，验证“吸收 → 卸载”节奏是否符合预期。

适用性与注意事项

仓库 README 明确声明：示例代码并非“如何构建可扩展企业级应用”的最佳实践集合，其定位是快速上手演示。使用时应结合自身硬件规格调整App.config中的任务数、批次大小与延迟参数（例如追求最大吞吐时可将dataLoadCommandDelay、offLoadCommandDelay设为0）。此外，SCHEMA_ONLY持久化意味着内存表数据在实例重启后丢失，生产环境中是否需要落盘需根据业务权衡。

相关资源（仓库内）

• 内存优化 OLTP 相关的更多官方示例：samples/features/in-memory-database（含 in-memory-oltp 工程与内存优化 tempdb 元数据示例）；
• 列存储索引深入示例：samples/features/columnstore；
• 同仓库的姊妹示例 IoT Connected Car：samples/applications/iot-connected-car，同样基于内存优化表与 TVP 实现高吞吐数据摄取；
• 示例的 Power BI 报表文件：PowerDashboard.pbix；
• 数据库项目定义：Db.sqlproj。

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