Hermes Agent 实战 07｜数据库实战：可直接抄走的 SQL Server 巡检脚本原创

# 数据库实战：可直接抄走的 SQL Server 巡检脚本

系列第 07 篇。这篇基本是一份 SQL Server 巡检 SOP——慢 SQL、索引碎片、缺失/僵尸索引、空间回收、wait stats，全部是 agent 真实跑过、可以直接复制的 DMV 查询和命令。下面所有 SQL 假设库名 AppDB，表名我换成了通用名，换成你自己的即可。

# 1. 连接与防截断（两个能毁掉整次巡检的细节）

# 1.1 缺 sqlcmd 就用 pymssql 兜底

最小环境的容器常常没有 sqlcmd，别因此卡死：

import pymssql, os
conn = pymssql.connect(server=os.environ["DB_HOST"], user=os.environ["DB_USER"],
                       password=<PASSWORD>DB_PASS"], database="AppDB")
cur = conn.cursor()
cur.execute("SELECT @@VERSION")
print(cur.fetchone()[0])

1
2
3
4
5
6

# 1.2 `sqlcmd` 必加 `-y 0`，否则 SQL 文本和 XML 计划会被截断

sqlcmd -S "$DB_HOST" -U "$DB_USER" -P "$DB_PASS" -d AppDB \
       -y 0 -W -s"|" -i collect_slow_sql.sql -o slow_sql.txt
#   -y 0 : 可变列不限宽（防 SQL/XML 计划截断，最关键）
#   -W   : 去掉行尾空格
#   -s"|": 用 | 作列分隔，方便后续解析

1
2
3
4
5

# 2. 慢 SQL Top-N（按累计消耗排序）

最直接的产出——揪出消耗最大的语句。用 sys.dm_exec_query_stats 关联出 SQL 文本与执行计划：

SELECT TOP 20
    qs.execution_count                                   AS exec_count,
    qs.total_worker_time/1000                            AS total_cpu_ms,
    qs.total_worker_time/qs.execution_count/1000         AS avg_cpu_ms,
    qs.total_elapsed_time/1000                           AS total_elapsed_ms,
    qs.total_logical_reads                               AS total_reads,
    qs.total_logical_reads/qs.execution_count            AS avg_reads,
    SUBSTRING(qt.text, (qs.statement_start_offset/2)+1,
        ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(qt.text)
          ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)+1) AS stmt
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
ORDER BY qs.total_worker_time DESC;   -- 想看 IO 杀手就换 total_logical_reads

1
2
3
4
5
6
7
8
9
10
11
12
13

⚠️ 专家级坑（我真栽过）：dm_exec_query_stats、dm_db_missing_index_group_stats、dm_db_index_usage_stats 里的计数全是实例启动以来的累计值。直接把它当「每天多少次」是错的——那可能是几个月的总量。验证方法很简单：隔 30 分钟查两次，看 execution_count/seeks 涨没涨（我那次 seeks 从 37,049 → 37,131，证明在递增）。要算「每天」必须自己做 Delta（两次快照相减），或在监控系统里取差值。报告里老老实实写「累计 N 次 + 平均每次开销」，别编造速率。

# 3. 索引碎片：先量化，再按阈值处理

# 3.1 查碎片（先 SAMPLED 扫全库，再对可疑表 DETAILED）

SELECT OBJECT_NAME(s.object_id)      AS table_name,
       i.name                        AS index_name,
       s.index_type_desc,
       s.avg_fragmentation_in_percent AS frag_pct,
       s.page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') s
JOIN sys.indexes i ON s.object_id = i.object_id AND s.index_id = i.index_id
WHERE s.page_count > 1000                 -- 小索引碎片无所谓，过滤掉
  AND s.avg_fragmentation_in_percent > 10
ORDER BY s.avg_fragmentation_in_percent DESC;

1
2
3
4
5
6
7
8
9
10

# 3.2 按碎片率分档处理（业界阈值 5% / 30%）

-- 5%~30%：REORGANIZE（在线、轻量、不阻塞）
ALTER INDEX IX_orders_site_paid_at ON dbo.orders REORGANIZE;

-- >30%：REBUILD（彻底，企业版可 ONLINE=ON 不阻塞）
ALTER INDEX IX_orders_site_paid_at ON dbo.orders
      REBUILD WITH (ONLINE = OFF);

-- 顺序写为主、又频繁更新的表，REBUILD 时给个 FILLFACTOR 留出更新空间，
-- 减少后续页分裂（写多的表常用 70~80）
ALTER INDEX IX_users_site_active_at ON dbo.users
      REBUILD WITH (FILLFACTOR = 70, SORT_IN_TEMPDB = ON);

1
2
3
4
5
6
7
8
9
10
11

经验值：碎片率卡在 45% 左右的大热表，一次 REBUILD 常能直接砍掉 ~45% 的无谓 IO。但重建不是治本——如果是 FILLFACTOR 不当导致的持续页分裂，过几周又会碎回去（这正是第 11 篇「配置病 vs 卫生病」的数据库版）。

# 4. 缺失索引（建之前先想清代价）

SELECT TOP 20
    ROUND(s.avg_total_user_cost * s.avg_user_impact * (s.user_seeks + s.user_scans), 0) AS impact_score,
    s.user_seeks, s.user_scans,
    d.statement                       AS table_name,
    d.equality_columns, d.inequality_columns, d.included_columns
FROM sys.dm_db_missing_index_group_stats s
JOIN sys.dm_db_missing_index_groups   g ON s.group_handle = g.index_group_handle
JOIN sys.dm_db_missing_index_details  d ON g.index_handle = d.index_handle
ORDER BY impact_score DESC;

1
2
3
4
5
6
7
8
9

同样的累计值警告适用于这里的 user_seeks/user_scans。而且别照单全收：每个新索引都会拖慢写入、占用空间。先看 impact_score 排序，只挑「高收益 + 该表写入不密集」的建，建完回到第 2 步验证慢 SQL 真的降了。

# 5. 僵尸索引（存在但从不被读，纯负担）

SELECT OBJECT_NAME(i.object_id) AS table_name, i.name AS index_name,
       u.user_seeks, u.user_scans, u.user_lookups, u.user_updates
FROM sys.indexes i
LEFT JOIN sys.dm_db_index_usage_stats u
       ON i.object_id = u.object_id AND i.index_id = u.index_id AND u.database_id = DB_ID()
WHERE i.type_desc = 'NONCLUSTERED'
  AND OBJECTPROPERTY(i.object_id, 'IsUserTable') = 1
  AND COALESCE(u.user_seeks + u.user_scans + u.user_lookups, 0) = 0  -- 从没被读
  AND COALESCE(u.user_updates, 0) > 0                                -- 却在被维护
ORDER BY u.user_updates DESC;

1
2
3
4
5
6
7
8
9
10

reads = 0 而 user_updates 很高的索引，是纯粹的写入税，删了能直接给写入提速 + 省空间。

# 6. 空间回收：先定位，再 SHRINK / CLEANTABLE

# 6.1 看文件占用与剩余

SELECT name AS logical_name, type_desc,
       size/128.0                                   AS size_mb,
       FILEPROPERTY(name,'SpaceUsed')/128.0         AS used_mb,
       (size - FILEPROPERTY(name,'SpaceUsed'))/128.0 AS free_mb
FROM sys.database_files;

1
2
3
4
5

# 6.2 真实案例的两类回收

-- 日志文件涨到 13GB 但实际只用 678MB → 收缩立竿见影，瞬间回收 11GB
DBCC SHRINKFILE (N'AppDB_log', 2048);   -- 收到 2GB

-- 230GB 空间黑洞的真相：往往不是数据量，而是 LOB（大对象）碎片。
-- 删过大量 varchar(max)/text 列后空间不还，用 CLEANTABLE 回收 LOB 空间
DBCC CLEANTABLE ('AppDB', 'dbo.message', 0);

1
2
3
4
5
6

排查「谁吃了空间」的顺序：sys.database_files 看文件级 → 按表聚合 allocation_units 区分数据/索引/LOB 三块 → 锁定后再 SHRINK/CLEANTABLE。空间膨胀十有八九不是数据多，是 LOB 碎片 + 日志没收 + 僵尸索引。

# 7. Wait Stats：过滤掉 benign 等待再看

直接看 dm_os_wait_stats 会被一堆正常空闲等待误导（这是「让 AI 看监控」最容易翻车的地方——把 idle 当瓶颈）。必须先把公认的 benign wait 排除掉：

SELECT TOP 15
    wait_type,
    wait_time_ms/1000.0                             AS wait_s,
    100.0 * wait_time_ms / SUM(wait_time_ms) OVER() AS pct,
    waiting_tasks_count
FROM sys.dm_os_wait_stats
WHERE waiting_tasks_count > 0
  AND wait_type NOT IN (   -- Paul Randal 那份著名的 benign 列表（节选）
      'CLR_SEMAPHORE','LAZYWRITER_SLEEP','RESOURCE_QUEUE','SLEEP_TASK',
      'SLEEP_SYSTEMTASK','SQLTRACE_BUFFER_FLUSH','WAITFOR','LOGMGR_QUEUE',
      'CHECKPOINT_QUEUE','REQUEST_FOR_DEADLOCK_SEARCH','XE_TIMER_EVENT',
      'BROKER_TO_FLUSH','BROKER_TASK_STOP','CLR_MANUAL_EVENT','CLR_AUTO_EVENT',
      'DISPATCHER_QUEUE_SEMAPHORE','FT_IFTS_SCHEDULER_IDLE_WAIT','XE_DISPATCHER_WAIT',
      'XE_DISPATCHER_JOIN','SQLTRACE_INCREMENTAL_FLUSH_SLEEP','ONDEMAND_TASK_QUEUE',
      'BROKER_EVENTHANDLER','SLEEP_BPOOL_FLUSH','DIRTY_PAGE_POLL','HADR_FILESTREAM_IOMGR_IOCOMPLETION')
ORDER BY wait_time_ms DESC;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

排完之后剩下的才是真信号：PAGEIOLATCH_*（读 IO 压力）、CXPACKET/CXCONSUMER（并行度）、LCK_M_*（锁等待）、WRITELOG（日志写）。

# 8. 巡检 checklist（按这个顺序跑）

连接验证（缺 sqlcmd 上 pymssql），导数据一律 -y 0 防截断。
慢 SQL Top-N（第 2 节）——记住是累计值，要速率自己做 Delta。
碎片：SAMPLED 扫全库 → 5% REORGANIZE / 30% REBUILD（+FILLFACTOR）。
缺失索引按 impact 排序挑着建，建完回查慢 SQL。
僵尸索引（reads=0、updates>0）删掉减写入税。
空间：文件级 → 数据/索引/LOB 三分 → SHRINK / CLEANTABLE。
Wait stats 先排 benign，再看 PAGEIOLATCH/CXPACKET/LCK/WRITELOG。
全程只读：所有 ALTER/DBCC 列进报告，标好预计收益与风险窗口，等人确认低峰期再执行。

下一篇讲量化交易监控——同样以可复现为主：怎么只读接入 Freqtrade 的 REST/WebSocket、拉持仓盈亏、以及带验证回滚的策略自动更新脚本。

#AI Agent #Hermes #SQLServer #DBA #数据库

上次更新: 6/21/2026

← Hermes Agent 实战 06｜让 Agent 自己上班：cron 驱动的无人值守巡检 Hermes Agent 实战 08｜量化交易助手：持仓盈亏、网格减仓，与「没开单」的真相→