仅700行的表执行SELECT *耗时过长，如何优化查询性能？

问题背景 #

我有一张仅含700行数据的表，执行select *查询耗时极久：获取前40行需1分钟以上，获取80行则需2分钟左右。已尝试更新统计信息、添加主键列，且无并行阻塞查询，但性能未得到任何改善。

仅针对该表的SELECT查询速度缓慢，是否可能是因为存储查询计划的XML类型列与存储执行SQL命令的sql_command列导致？还需检查哪些内容？如何优化该查询的性能？

一、XML列与sql_command列是否会导致性能问题？ #

是，这类列大概率是性能瓶颈的核心原因：

• XML类型列：SQL Server读取XML列时需对存储的XML数据进行解析、反序列化，若单条XML内容体积较大（比如包含复杂的查询计划结构），即便只有700行，每行的XML解析开销累加后也会大幅拖慢查询速度。
• sql_command列：若该列存储长文本（比如完整的复杂SQL语句），select *时需读取大量文本数据，会增加IO传输与CPU处理开销，直接影响查询效率。

二、还需检查的内容 #

• 表的存储结构：确认表是否为堆表（即使添加主键，若主键是非聚集索引，表本身仍为堆），堆表的读取效率通常低于聚集索引表；同时排查索引碎片情况（700行碎片影响有限，但仍可确认）。
• XML列细节：查看XML列的平均数据大小（通过DATALENGTH(xml列名)查询每行字节数）；确认是否创建了XML索引，无索引的大XML列读取开销会显著增高。
• sql_command列属性：检查该列是否使用TEXT/NTEXT等过时数据类型，这类类型的读取效率远低于VARCHAR(MAX)/NVARCHAR(MAX)；同时统计该列的平均文本长度，判断大文本是否是主要开销来源。
• 执行计划：通过SSMS的“包括实际执行计划”或SET SHOWPLAN_XML ON查看查询的资源消耗，确认瓶颈是否在XML解析、文本读取而非磁盘IO。
• 磁盘IO性能：检查数据文件所在磁盘的IO状态，若磁盘队列高、读写延迟大，即便是小表的大字段读取也会变慢，可通过Windows性能监视器查看PhysicalDisk相关计数器。

三、性能优化方案 #

1. 避免全列查询：放弃select *，仅查询业务必需的列，跳过XML列与sql_command列，这是最直接有效的优化手段。
2. 优化XML列处理：
   • 若需频繁查询XML内的特定节点，创建主XML索引与对应辅助索引（路径/值索引），让SQL Server直接索引XML内容，避免全量解析。
   • 若无需XML结构查询，可将XML转为VARCHAR(MAX)存储，减少解析开销（仅适用于不需要XML操作的场景）。
3. 优化sql_command列：
   • 对该列启用行级或页级压缩，降低存储体积与读取开销。
   • 若无需完整SQL语句，仅存储SQL哈希值、语句摘要等关键信息，大幅减少数据量。
4. 调整表结构：
   • 将主键设置为聚集索引，让表按主键顺序存储，提升顺序读取效率。
   • 若XML列与sql_command列不常用，可将其设为非聚集索引的包含列，或使用列存储索引优化批量读取。
5. 查询层面优化：
   • 若必须读取XML列，可提前用CAST(xml列名 AS VARCHAR(MAX))转换，减少客户端解析压力。
   • 采用OFFSET...FETCH分批次读取数据，降低单次查询的资源消耗。

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