CentOS上 SQL Server 存储过程优化方法
一 环境与诊断工具
- 在 CentOS 上连接 SQL Server 可用 sqlcmd 执行诊断与优化命令，便于在 Linux 终端直接收集信息。
- 收集执行时间与 I/O：
- 执行前开启统计：
SET STATISTICS TIME ON; SET STATISTICS IO ON;
- 执行存储过程：
EXEC [YourStoredProcedure];
- 关闭统计：
SET STATISTICS TIME OFF; SET STATISTICS IO OFF;
- 查看执行计划（XML）：
SET SHOWPLAN_XML ON; EXEC [YourStoredProcedure]; SET SHOWPLAN_XML OFF;
- 线上热点查询定位（示例）：
SELECT TOP 10 qs.execution_count, qs.total_elapsed_time/1000 AS [ms], qs.total_worker_time/1000, qs.total_physical_reads, qs.total_logical_reads FROM sys.dm_exec_query_stats AS qs ORDER BY qs.total_elapsed_time DESC;
- 索引核查：
EXEC sp_helpindex 'YourTable';
- 存储过程元数据：
sp_help 'YourStoredProcedure';
- 建议将统计与计划输出重定向到文件，便于对比优化前后差异：
sqlcmd -S <host,port> -U <user> -P <pwd> -d <db> -Q "SET STATISTICS IO,TIME ON; EXEC dbo.YourProc;" -o sp_opt.txt。
二 编写与重构规范
- 使用 SET NOCOUNT ON：减少网络往返的“影响行数”消息，降低开销。
- 始终指定 Schema（如
dbo.Table）：避免名称解析与计划缓存搜索，减少编译锁竞争。
- 自定义存储过程避免以 sp_ 开头：防止不必要的 master 库查找路径。
- 动态 SQL 使用 sp_executesql 并参数化：可重用执行计划，避免
EXEC 拼接导致的计划无法复用与注入风险。
- 尽量避免 游标：SQL 为集合语言，优先用集合操作替代逐行处理；若确需游标，控制每次抓取量并尽快释放。
- 事务尽量 短小精悍：只包裹必要操作，避免高隔离级别与长时间持有锁，降低阻塞与死锁概率。
- 错误处理使用 TRY…CATCH：与事务结合，确保异常时回滚并给出可诊断信息。
三 查询与索引策略
- 只选择必要列：避免
SELECT *，减少 I/O 与网络传输。
- 先过滤后连接：优先在 WHERE 中减少参与 JOIN 的数据集，降低后续操作成本。
- 合理使用索引：为高频过滤/连接/排序字段建立合适索引，避免全表扫描；对多列条件设计联合索引并遵循最左前缀原则。
- 避免在 WHERE 中对列做函数或表达式运算（如
WHERE YEAR(Col)=2024）：会导致索引失效，改写为可索引条件（如范围条件）。
- 存在性判断优先用 EXISTS 而非
COUNT(1)：语义更清晰、通常更高效。
- 复杂多表聚合或中间结果集较大时，可先物化到 临时表（必要时建立索引），再参与后续 JOIN，常能显著降低整体成本。
- 跨库访问尽量通过同库 VIEW 封装，便于依赖管理与后续优化（如统计信息、索引协同）。
四 临时表与 tempdb 使用
- 大数据量一次性装载优先用 SELECT INTO 创建临时表（最小化日志），小数据量可先
CREATE TABLE 再 INSERT。
- 避免在临时表上频繁
CREATE/DROP：减少系统表争用；过程结束前可 TRUNCATE TABLE 再 DROP TABLE 以加速清理。
- 临时表数据量较大时，为其创建合适的 索引 以加速后续 JOIN/聚合；必要时将“建表+建索引”封装为子过程，便于复用与优化器选择。
- 减少不必要的 DISTINCT/ORDER BY/GROUP BY/JOIN 等会放大 tempdb 压力的操作；合理分批处理，避免单次操作数据量过大。
五 执行计划与参数嗅探治理
- 使用
SET SHOWPLAN_XML ON 或图形化执行计划，定位高成本算子（如表扫描、键查找、排序、哈希匹配）并优先从索引与语句改写入手优化。
- 针对“参数嗅探”导致的次优计划：
- 在关键语句上使用 OPTION (RECOMPILE) 获取基于当前参数值的专用计划（适合数据分布极不均匀的参数）。
- 使用 OPTION (OPTIMIZE FOR UNKNOWN) 或计划指南，缓解极端参数导致的计划偏差。
- 对稳定且通用的查询片段，考虑使用 计划指南 或局部重构以稳定计划形态。
- 优化后务必在相同数据量与并发场景下复测，并用
sys.dm_exec_query_stats 等视图持续监控执行时间与 I/O 的变化，确保收益稳定落地。