SQL Server大表查询性能优化及索引相关问题咨询

问题背景 #

数据检索40万条记录耗时超4小时；150万行的父表关联12张子表，简单count查询（如下）也需数小时完成：

select count(1) 
from table
where col_filter >= 'number'

该表共40列，col_filter为varchar类型且无索引。

1. 排查表设置潜在问题需检查的内容 #

• 存储结构：检查是否为堆表（无聚集索引），堆表全表扫描效率远低于带聚集索引的表；查看表是否分区，未分区的大表扫描成本极高。
• 字段属性：确认col_filter的varchar长度是否合理，过长字符类型会增加扫描IO开销；检查字段是否存在大量NULL值或异常数据，这类数据会干扰查询优化器生成最优执行计划。
• 统计信息：查看表及col_filter字段的统计信息是否过期，SQL Server依赖统计信息选择执行计划，过期统计信息会导致计划选择错误。
• 锁与阻塞：排查是否有长期运行的事务锁表，或频繁读写冲突导致查询阻塞；可通过sys.dm_tran_locks、sys.dm_os_wait_stats等系统视图查看。
• 磁盘IO性能：检查存储磁盘的读写速度、队列长度，慢磁盘会直接拖慢大表扫描；通过sys.dm_io_virtual_file_stats获取磁盘IO指标。
• 表碎片：查看表及现有索引的碎片率，高碎片会增加扫描时的IO次数；可使用sys.dm_db_index_physical_stats查询碎片情况。

2. SSMS 18中可用的性能分析工具 #

• 执行计划：点击“显示估计的执行计划”（Ctrl+L）或“包括实际执行计划”（Ctrl+M），查看查询执行步骤，识别全表扫描、高开销操作节点。
• 查询存储（Query Store）：启用后可追踪查询的历史执行计划、性能指标，定位性能退化点，还能强制使用最优执行计划。
• 数据库引擎优化顾问：右键点击查询选择“分析查询在数据库引擎优化顾问”，工具会根据查询和表结构推荐索引、分区等优化方案。
• 活动监视器：通过“活动监视器”查看当前进程、等待统计信息、数据文件IO，快速定位阻塞或资源瓶颈。
• 动态管理视图（DMVs）：直接查询系统DMVs，比如sys.dm_exec_query_stats查看查询的CPU、IO开销，sys.dm_exec_requests查看当前运行查询的状态。

3. 索引占用空间的计算方法 #

针对col_filter创建非聚集索引时，空间占用可通过以下方式估算：

1. 单条索引记录大小：非聚集索引包含索引键（col_filter平均长度）+ 聚集索引键（表有聚集索引时）或堆表的RID（约8字节）。假设col_filter平均长度为N字节，单条索引记录大小约为N + 8字节（堆表）或N + 聚集索引键长度（聚集索引表）。
2. 总索引大小：总记录数（150万） × 单条索引记录大小 × 1.2（预留20%空间用于碎片和增长）。
3. 精确计算：可先创建测试索引，再通过sys.dm_db_index_physical_stats查看索引的total_pages，乘以8KB（SQL Server每页大小）得到实际占用空间；或使用sp_spaceused存储过程查看索引空间占用。

若创建覆盖索引（如CREATE NONCLUSTERED INDEX IX_table_col_filter ON table(col_filter) INCLUDE (其他所需列)），需将INCLUDE列的大小加入计算。

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