加载2500万条记录后T-SQL查询性能骤降原因排查求助

这种2500万条记录后性能骤降的情况我在日常排查中碰到过不少，大概率是几个常见因素叠加导致的，咱们一步步拆解来看：

• 内存缓存耗尽：当数据量冲到2500万左右，SQL Server的缓冲池可能被占满了——之前的查询能直接从内存缓存取数据，现在不得不频繁读取磁盘（物理IO飙升），速度自然掉下来。你可以跑SELECT * FROM sys.dm_os_memory_clerks看看缓存使用情况，或者打开性能监视器观察Page Life Expectancy（PLE），如果PLE突然断崖式下跌，基本就是内存顶不住了。
• 统计信息过时：SQL Server的查询优化器全靠统计信息判断数据分布，生成最优执行计划。当数据量剧增后，旧的统计信息就不准了，优化器可能生成低效的执行计划（比如本来该走索引，结果走了全表扫描）。手动更新统计信息试试：UPDATE STATISTICS [你的目标表名] WITH FULLSCAN，更新完再跑存储过程看看速度有没有回升。
• 事务日志瓶颈：如果加载数据是放在大事务里执行的，日志文件可能出现空间不足或者增长过慢的问题——比如日志文件设置了很小的增长步长，导致频繁触发自动增长，查询时就得等日志写入完成。可以查sys.dm_os_wait_stats里的WRITELOG等待类型，如果占比很高，就得调整日志文件的初始大小和增长设置（建议设成固定大小的增量，比如1GB，避免频繁小步增长）。
• 索引碎片暴增：大量插入数据后，尤其是聚集索引，很容易产生严重的碎片。查询时遍历碎片多的索引，相当于要跳很多磁盘块，速度自然慢20倍都有可能。你可以用SELECT * FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('你的目标表名'), NULL, NULL, 'DETAILED')查看碎片率，如果超过30%，直接重建索引：ALTER INDEX ALL ON [你的目标表名] REBUILD。

• 存储过程执行计划固化（参数嗅探）：如果你的存储过程第一次执行时数据量还很小，SQL Server会生成适合小数据量的执行计划，后面数据量变大了，它还会复用这个旧计划——这就是参数嗅探的坑。解决办法很简单：要么重新编译存储过程EXEC sp_recompile 'pGetOHLCBetweenTwoDates'，要么在存储过程定义里加WITH RECOMPILE选项，让它每次都生成新的执行计划。
• 查询逻辑存在低效点：打开SSMS，按Ctrl+M开启实际执行计划，跑一遍存储过程，看看有没有高成本的操作——比如全表扫描、RID Lookup/Key Lookup（说明缺少覆盖索引）。如果是覆盖索引的问题，你可以创建包含查询所需所有列的索引，比如CREATE NONCLUSTERED INDEX IX_OHLC_Date ON [你的表名](DateColumn) INCLUDE (Open, High, Low, Close)，这样查询就能直接从索引取数据，不用回表。
• SqlDataReader的细节优化：虽然你说耗时卡在ExecuteReader()，但也可以检查下有没有开启CommandBehavior.SequentialAccess——如果存储过程返回的结果集很大，这个选项能减少内存占用，提升读取效率。不过核心问题还是在SQL端的执行时间，这个算是辅助优化。

• 数据倾斜：如果2500万条之后，你查询的时间区间刚好是数据密集区（比如某段时间的记录数是之前的10倍），那之前的执行计划就不适用了。这种情况也会触发参数嗅探的问题，同样可以用重新编译执行计划来解决。
• 锁与阻塞：加载数据的操作和查询有没有冲突？比如插入时加了表级锁，导致查询一直等待锁释放。你可以跑SELECT * FROM sys.dm_tran_locks看看锁的情况，或者查sys.dm_os_wait_stats里的LCK_M_*等待类型，如果占比高，就得调整加载数据的方式（比如改成批量插入加事务提交，减少锁持有时间）。

内容的提问来源于stack exchange，提问作者David C Fuchs