SQL Server电厂停运时序数据插入性能优化咨询

背景与现状 #

爬取电厂停运相关消息并转换为时序数据，存储至SQL Server数据库。现有数据表结构及数据量如下：

• Messages表（remit_messages）：包含publicationDate(datetime2(0))、messageSeriesID(nvarchar(36))、version(int)、自增messageId字段，主键为(messageSeriesId, version)；当前约100万条数据。
• Units表（remit_units）：包含自增tsId、fuelTypeId(int)、areaId(int)、messageId(int)、unitName(nvarchar(200))，通过messageId关联Messages表，存储单条消息对应的多台机组信息；当前约100万条数据。
• Timeseries表（remit_ts）：包含tsId(int)、delivery(datetime2(0))、available(decimal(11,3))、unavailable(decimal(11,3))，按delivery月份分区，主键为(delivery, tsId)，通过tsId关联Units表；当前约5亿条数据。

每次批量插入时，Messages表插入1行，Units表插入1-4行，Timeseries表需插入多达数十万行。

遇到的问题 #

Timeseries表插入速度过慢：插入10万行耗时可达1分钟，已将索引填充因子从100调整为80，但性能仍未达标。

• 跨3年（36个分区）插入时速度极慢；单分区插入同量数据仅需约1.5秒，空表跨分区插入速度也很快。
• 原预期分区按delivery月份划分、主键以delivery为首，数据可直接插入分区末尾，但实际并非如此。
• 查询主要基于delivery字段，因此不适合按tsId分区。

附建表语句 #

CREATE TABLE [dbo].[remit_messages]
(
    [publicationDate] [datetime2](0) NOT NULL,
    [version] [int] NOT NULL,
    [messageId] [int] IDENTITY(1,1) NOT NULL,
    [messageSeriesId] [nvarchar](36) NOT NULL,

    PRIMARY KEY CLUSTERED ([messageSeriesId] ASC, [version] ASC)
            WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                  IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                  ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE UNIQUE NONCLUSTERED INDEX [dbo_remit_messages_messageId] 
ON [dbo].[remit_messages] ([messageId] ASC)
       WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
             SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, 
             DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, 
             ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

CREATE TABLE [dbo].[remit_units]
(
    [tsId] [int] IDENTITY(1,1) NOT NULL,
    [fuelTypeId] [int] NOT NULL,
    [areaId] [int] NOT NULL,
    [messageId] [int] NOT NULL,
    [unitName] [nvarchar](200) NULL,

    PRIMARY KEY CLUSTERED ([messageId] ASC, [tsId] ASC)
            WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                  IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                  ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
    CONSTRAINT [dbo_remit_tsId] UNIQUE NONCLUSTERED ([tsId] ASC)
            WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                  IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                  ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [dbo_remit_units_tsid] 
ON [dbo].[remit_units] ([tsId] ASC)
         WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
               SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, 
               ONLINE = OFF, ALLOW_ROW_LOCKS = ON, 
               ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

ALTER TABLE [dbo].[remit_units] WITH CHECK 
    ADD FOREIGN KEY([messageId])
        REFERENCES [dbo].[remit_messages] ([messageId])
            ON UPDATE CASCADE
            ON DELETE CASCADE
GO

CREATE TABLE [dbo].[remit_ts]
(
    [tsId] [int] NOT NULL,
    [delivery] [datetime2](0) NOT NULL,
    [available] [decimal](11, 3) NULL,
    [unavailable] [decimal](11, 3) NULL,

    PRIMARY KEY CLUSTERED ([delivery] ASC, [tsId] ASC)
            WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                  IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                  ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [MonthlyPartitionScheme]([delivery])
) ON [MonthlyPartitionScheme]([delivery])
GO

CREATE NONCLUSTERED INDEX [idx_remit_ts_delivery_inc] 
ON [dbo].[remit_ts] ([delivery] ASC)
INCLUDE([tsId], [unavailable], [available]) 
         WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
               SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF,
               ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [MonthlyPartitionScheme]([delivery])
GO

ALTER TABLE [dbo].[remit_ts] WITH CHECK 
    ADD FOREIGN KEY([tsId])
        REFERENCES [dbo].[remit_units] ([tsId])
            ON UPDATE CASCADE
            ON DELETE CASCADE
GO

优化建议 #

一、插入方法优化 #

1. 分区排序后批量插入
   跨分区插入慢的核心是数据在多分区内无序，导致SQL Server频繁切换分区页、产生大量随机IO。将待插入的Timeseries数据按delivery（分区键）+tsId提前排序后再批量插入，每个分区内数据按主键顺序写入，可大幅减少页分裂和随机IO。
2. 使用批量插入工具
   避免单条或小批量插入，改用SqlBulkCopy（.NET）、bcp工具，或T-SQL的INSERT ... SELECT、BULK INSERT语句，这类方式能在简单恢复模式下启用最小日志记录，降低日志IO开销。
3. 临时表中转
   先将待插入数据写入同结构的非分区临时表（或堆表），排序后再批量导入remit_ts。临时表写入速度更快，且可在内存中完成排序。
4. 调整数据库恢复模式
   若业务允许，将数据库切换为简单恢复模式，批量插入时使用最小日志；完成插入后再改回完整恢复模式（如需要）。

二、表结构与索引优化 #

1. 删除冗余索引
   remit_ts表的非聚集索引idx_remit_ts_delivery_inc包含的字段，聚集索引(delivery, tsId)已覆盖（聚集索引包含所有列），该非聚集索引完全冗余，删除可减少插入时的索引维护开销。
2. 临时禁用外键约束
   批量插入时，外键约束会导致每条数据都去remit_units表校验，产生大量IO。若能确保待插入的tsId合法，可临时禁用约束，插入完成后再启用并校验：

   ALTER TABLE [dbo].[remit_ts] NOCHECK CONSTRAINT [dbo_remit_ts_tsId];
   -- 执行批量插入
   ALTER TABLE [dbo].[remit_ts] WITH CHECK CHECK CONSTRAINT [dbo_remit_ts_tsId];
   

3. 检查分区分布
   确认分区函数的边界是否合理，避免出现部分分区数据量过大、部分过小的情况，均衡的分区分布能提升插入和查询效率。

三、系统层面优化 #

1. 优化存储IO
   将数据库数据文件和日志文件分别部署在高速存储介质（如SSD）上，避免IO瓶颈；若有条件，可将不同分区的数据文件分散到不同磁盘。
2. 调整批量插入参数
   使用SqlBulkCopy时，设置合适的BatchSize（如10000或更大），避免过小批次导致频繁提交和日志写入。
3. 更新统计信息
   大表插入后统计信息易过时，定期更新remit_ts表的统计信息，帮助SQL Server生成最优执行计划：

   UPDATE STATISTICS [dbo].[remit_ts] WITH FULLSCAN;
   

内容的提问来源于stack exchange，提问作者Jasper