SQL Server CentOS资源占用高怎么办

SQL Server 在 CentOS 上资源占用高的定位与处置

一 快速定位占用来源

• 系统层面：用 top/htop 观察 sqlservr 进程的 CPU、RES、%CPU；用 pidstat -u -p $(pidof sqlservr) 1 查看线程级占用；用 iotop 观察磁盘读写；用 sar -n DEV 1 检查网络。
• SQL Server 层面：
  • 当前高消耗请求与语句：
    • SELECT TOP 20 s.session_id, r.status, r.cpu_time, r.logical_reads, r.reads, r.writes,
      SUBSTRING(st.text,(r.statement_start_offset/2)+1,
      CASE WHEN r.statement_end_offset = -1 THEN DATALENGTH(st.text) ELSE r.statement_end_offset END - r.statement_start_offset)/2+1) AS stmt,
      s.loginame, s.host_name, s.program_name, s.last_request_end_time
      FROM sys.dm_exec_requests r WITH (NOLOCK)
      JOIN sys.sysprocesses s WITH (NOLOCK) ON r.session_id = s.spid
      CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) st
      ORDER BY r.cpu_time DESC;
  • 历史高 CPU 查询与计划：
    • SELECT TOP 20 qs.total_worker_time/qs.execution_count AS avg_cpu_ms, qs.execution_count,
      qs.total_worker_time, qs.total_elapsed_time,
      SUBSTRING(qt.text,(qs.statement_start_offset/2)+1,
      CASE WHEN qs.statement_end_offset = -1 THEN DATALENGTH(qt.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2+1) AS stmt,
      qp.query_plan
      FROM sys.dm_exec_query_stats qs
      CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
      CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
      ORDER BY avg_cpu_ms DESC;
  • 等待统计识别 CPU 压力与并发特征：
    • 信号等待占比（纯 CPU 排队）：
      • SELECT CAST(100.0 * SUM(signal_wait_time_ms) / SUM(wait_time_ms) AS NUMERIC(5,2)) AS signal_wait_pct
        FROM sys.dm_os_wait_stats;
    • 常见 CPU 相关等待：SOS_SCHEDULER_YIELD、CXPACKET、CMEMTHREAD。
  • 内存压力与缓存命中：
    • SELECT total_physical_memory_kb/1024 AS Total_MB, available_physical_memory_kb/1024 AS Avail_MB,
      process_physical_memory_low, process_virtual_memory_low
      FROM sys.dm_os_sys_memory;
    • SELECT d.name, SUM(a.total_pages)*8/1024 AS Mem_MB
      FROM sys.dm_os_memory_clerks a JOIN sys.databases d ON a.database_id = d.database_id
      GROUP BY d.name ORDER BY Mem_MB DESC;
  • 计划缓存与单次 Ad-Hoc 过多：
    • SELECT usecounts, cacheobjtype, objtype,
      SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
      CASE WHEN qs.statement_end_offset = -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2+1) AS stmt
      FROM sys.dm_exec_cached_plans cp
      CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
      JOIN sys.dm_exec_query_stats qs ON cp.plan_hash_value = qs.plan_hash_value
      ORDER BY usecounts ASC;
      以上查询可快速判断是 CPU 密集（扫描、排序、哈希、编译）、内存压力（缓冲池不足、系统可用内存低）还是 I/O 瓶颈（读写放大）导致占用高。

二 常见根因与对应处置

• 执行计划不佳或统计信息过期：高逻辑读、缺失索引、参数嗅探导致次优计划。
  • 处置：更新统计信息（UPDATE STATISTICS）、按需创建/重建索引、对参数敏感场景使用 OPTIMIZE FOR 提示或计划指南、必要时重编译。
• 非参数化 Ad-Hoc 过多：计划缓存膨胀、CPU 与内存浪费。
  • 处置：改为参数化或 sp_executesql，开启 Optimize for Ad Hoc Workloads。
• 并发与并行度过高：CXPACKET 偏高、过度并行导致调度开销。
  • 处置：合理设置 MAXDOP（不超过物理核数，OLTP 通常更小）、适度提高 cost threshold for parallelism，避免小查询并行。
• 压缩/加密等特性：数据压缩、备份压缩会提升 CPU 占用以换取 I/O 下降。
  • 处置：在 CPU 紧张时评估降低压缩级别或错峰执行备份/压缩任务。
• 内存过度占用导致系统换页：缓冲池无限增长挤占系统与其他服务。
  • 处置：设置 max server memory (MB)，为操作系统与其他进程预留充足内存（见下一节）。
• I/O 瓶颈：频繁扫描、写入放大、日志/数据盘性能不足。
  • 处置：使用 SSD、优化索引减少扫描、分离日志与数据盘、合理设置文件增长与批量提交。
    以上为 SQL Server 在 Linux 上高占用的高频根因与对策。

三 内存占用过高时的稳妥配置

• 设置最大服务器内存（缓冲池上限）：
  • EXEC sp_configure ‘show advanced options’, 1; RECONFIGURE;
  • EXEC sp_configure ‘max server memory (MB)’, 16384; RECONFIGURE; – 示例：限制为 16GB
• 设置最小服务器内存（避免运行时过度收缩）：
  • EXEC sp_configure ‘min server memory (MB)’, 4096; RECONFIGURE; – 示例：保留 4GB
• 重要说明：
  • max server memory 主要限制缓冲池，不等于 SQL Server 所有组件的上限；
  • 在 CentOS 上，不要把 SQL Server 的 max memory 配置为接近物理内存，需为 操作系统、监控代理、备份工具 等预留充足内存，避免系统换页与 OOM。
• 观察与验证：
  • 用 sys.dm_os_sys_memory 与 free -h 观察可用内存变化，确认设置后系统与应用均稳定。
    以上步骤可有效避免 SQL Server 在 Linux 上因内存过度占用引发的系统级问题。

四 临时缓解与应急操作

• 快速“止血”：
  • 终止异常会话：KILL < session_id> ；
  • 将问题查询改为只读副本/报表库执行；
  • 暂停大批量导入/重建索引等重负载任务。
• 降低即时 CPU 压力：
  • 临时降低 MAXDOP、提高 cost threshold for parallelism；
  • 对大查询加 OPTION (MAXDOP 1) 或拆分批处理；
  • 错峰执行压缩/备份。
• 仅用于紧急排查的 Linux 手段（不建议生产频繁使用）：
  • 清理 PageCache/Dentry/Inode 缓存：echo 1 > /proc/sys/vm/drop_caches（或 2/3 分别清理不同类型缓存）。这会释放缓存、可能影响文件系统性能，务必谨慎评估。
    以上为应急与临时缓解建议，治本仍需回到查询与配置优化。

五 建议的优化顺序与落地清单

• 第一步 观测与定位：用系统工具与 DMV 找出是 CPU/内存/I/O 哪个为主，并定位到具体 SQL/会话/等待类型。
• 第二步 快速止损：终止异常会话、限流重任务、临时调整并行度与 MAXDOP。
• 第三步 稳定配置：设置合理的 max/min server memory，为系统预留内存，避免换页。
• 第四步 查询与索引：更新统计信息、优化执行计划、参数化 Ad-Hoc、必要时重建/重组索引。
• 第五步 架构与 I/O：引入读写分离/缓存层、分离日志与数据盘、使用 SSD、优化批量提交与事务设计。
• 第六步 持续监控：固化 DMV 与系统监控基线，设置告警，定期复盘 Top SQL 与等待统计。
  以上流程能在多数场景下快速降低占用并提升稳定性。

声明：本文内容由网友自发贡献，本站不承担相应法律责任。对本内容有异议或投诉，请联系2913721942#qq.com核实处理，我们将尽快回复您，谢谢合作！