SQL存储迁移期间连接中断与性能验证脚本合理性咨询

问题背景 #

架构团队计划迁移某SQL服务器的存储，需验证迁移期间是否存在连接中断或性能影响（合作方称无业务影响，本次为首次尝试）。我编写了如下临时脚本，通过每秒写入带时间戳的记录来观察中断情况，原本考虑过读写内存的逻辑但不确定是否可行，现咨询：

1. 该脚本能否满足需求？
2. 若出现中断是否会因故障严重无法生成可报告结果？
3. 是否有更简洁或更优的脚本实现相同功能？

原脚本：

CREATE TABLE [dbo].[TomAndRickysSpecialTable]
( [Counter] INT IDENTITY(1,1) NOT NULL
 ,[Result]  VARCHAR(10)     NOT NULL
 ,[TimeStamp] DATETIME2(0) NOT NULL DEFAULT(GETDATE())
,CONSTRAINT PK_TikkiTavi PRIMARY KEY CLUSTERED ([counter] ASC)
)
exec sp_configure 'remote query timeout', 0 
go 
reconfigure with override 
go

CREATE PROC RICKYRICKYRICKYRICKYrickyTom
AS
    TRUNCATE TABLE TomAndRickysSpecialTable;
    INSERT TomAndRickysSpecialTable(Result) VALUES ('Start');
    SET NOCOUNT ON;
    TryAgain:
    WHILE 1 = 1
    BEGIN
    BEGIN TRY
        WAITFOR DELAY '00:00:01';
        IF EXISTS (SELECT TOP 1 [Counter] FROM TomAndRickysSpecialTable)
            INSERT TomAndRickysSpecialTable(Result) VALUES ('Yup');
            RAISERROR('Yup',10,1) WITH NOWAIT;  
    END TRY
    BEGIN CATCH
        RAISERROR('Nop',10,1) WITH NOWAIT;
        Goto TryAgain;
    END CATCH
    END

附：我虽反感While循环与Goto，但认为该场景下可实现需求。

问题解答 #

1. 原脚本能否满足需求？ #

能部分满足连接中断检测，但在性能影响验证上存在明显不足：

• 有效点：脚本通过每秒插入记录+实时输出状态，能通过TimeStamp列的时间间隔判断是否有中断；CATCH块的重试逻辑能在连接恢复后继续运行，不会因单次中断直接终止。
• 缺陷：
  • 负载太轻：仅做简单单条插入，无法模拟真实业务的读写压力，没法验证迁移对高并发场景下的性能影响（比如延迟升高、吞吐量下降）。
  • 无性能指标：没有记录操作耗时，无法量化迁移前后的性能变化。
  • 逻辑冗余：IF EXISTS (SELECT TOP 1 [Counter] FROM TomAndRickysSpecialTable)完全多余，脚本初始化时已插入Start记录，该判断永远为真。

2. 中断时能否生成可报告结果？ #

大概率能保留有效记录，但极端故障下会有局限：

• 普通连接闪断：CATCH块会输出'Nop'并重试，之前的TimeStamp记录会保留在表中，通过时间戳的跳变可以定位中断时段。
• 严重故障（如服务器宕机、连接彻底断开）：脚本进程会直接终止，此时最后一条成功记录的时间戳到故障发生的间隔即为中断起始点，但故障期间的状态无法记录——不过这种级别的故障本身会直接触发业务告警，无需脚本也能感知。
• 注意：存储迁移导致的短暂连接中断，脚本的重试逻辑会让测试继续，表中的时间戳间隔会明显变大，这是可靠的中断证据。

3. 更简洁/更优的实现方案 #

优化版脚本（增强监控+去除冗余）

去掉无用逻辑，增加耗时记录，同时保留实时输出：

CREATE TABLE [dbo].[MigrationTestLog]
(
    [LogId] INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
    [Status] VARCHAR(10) NOT NULL,
    [ExecutionTimeMs] INT NOT NULL, -- 记录单次操作耗时
    [TimeStamp] DATETIME2(0) NOT NULL DEFAULT(GETDATE())
)
GO

CREATE PROCEDURE [dbo].[RunMigrationConnectionTest]
AS
BEGIN
    SET NOCOUNT ON;
    TRUNCATE TABLE [dbo].[MigrationTestLog];
    INSERT INTO [dbo].[MigrationTestLog] ([Status], [ExecutionTimeMs]) VALUES ('Start', 0);

    WHILE 1 = 1
    BEGIN
        DECLARE @StartTime DATETIME2 = GETDATE();
        BEGIN TRY
            WAITFOR DELAY '00:00:01';
            -- 可替换为贴近业务的读写逻辑（如查询业务表+批量插入）
            INSERT INTO [dbo].[MigrationTestLog] ([Status], [ExecutionTimeMs])
            VALUES ('Success', DATEDIFF(MILLISECOND, @StartTime, GETDATE()));
            
            RAISERROR('Success at %s', 10, 1, CONVERT(VARCHAR, GETDATE(), 120)) WITH NOWAIT;
        END TRY
        BEGIN CATCH
            INSERT INTO [dbo].[MigrationTestLog] ([Status], [ExecutionTimeMs])
            VALUES ('Failed', DATEDIFF(MILLISECOND, @StartTime, GETDATE()));
            
            RAISERROR('Failed at %s: %s', 10, 1, CONVERT(VARCHAR, GETDATE(), 120), ERROR_MESSAGE()) WITH NOWAIT;
            -- 增加短暂延迟，避免频繁重试占用资源
            WAITFOR DELAY '00:00:00.500';
        END CATCH
    END
END
GO

内存型方案（轻量低干扰）

如果担心磁盘IO影响迁移测试结果，可使用SQL Server内存优化表（2014+支持），减少磁盘写入的干扰，同时性能更优：

-- 先创建内存优化文件组（若数据库未配置）
ALTER DATABASE [YourDatabaseName] ADD FILEGROUP [MemoryOptimizedFG] CONTAINS MEMORY_OPTIMIZED_DATA;
ALTER DATABASE [YourDatabaseName] ADD FILE (NAME = 'MemoryOptDataFile', FILENAME = 'D:\SQLData\MemoryOptDataFile') TO FILEGROUP [MemoryOptimizedFG];
GO

-- 创建内存优化日志表
CREATE TABLE [dbo].[MigrationTestLog_Memory]
(
    [LogId] INT IDENTITY(1,1) PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 1000000),
    [Status] VARCHAR(10) NOT NULL,
    [ExecutionTimeMs] INT NOT NULL,
    [TimeStamp] DATETIME2(0) NOT NULL DEFAULT(GETDATE())
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA); -- 保证数据持久化
GO

-- 存储过程可复用上面优化版的逻辑，仅替换表名即可

额外建议

• 模拟真实负载：若要验证性能影响，不要只用简单插入，应模拟业务的真实读写（如查询大表、批量更新），可使用SQL Server分布式重播工具ostress生成并发负载。
• 多客户端测试：在不同客户端同时运行测试脚本，模拟多连接场景，更贴近真实业务环境。
• 系统指标监控：除脚本记录外，需同步监控SQL Server的CPU、内存、磁盘IO、等待事件（如PAGEIOLATCH_*），迁移期间的指标变化能更全面反映性能影响。

内容的提问来源于stack exchange，提问作者High Plains Grifter