SQL Server 2019中QueryStore的Stale Query Threshold参数失效求助

首先明确：Microsoft官方文档中没有规定Stale Query Threshold (Days)参数必须≥30天，该参数支持设置1天及以上的数值，因此问题出在其他配置或运行机制上。以下是针对性的排查方向和解决步骤：

1. 确认参数是否真正生效 #

先通过T-SQL查询确认参数设置是否正确落地，避免GUI操作未生效的情况：

SELECT stale_query_threshold_days, current_state_desc, readonly_reason
FROM sys.database_query_store_options;

如果查询结果显示stale_query_threshold_days不是3，用以下语句重新设置：

ALTER DATABASE [你的数据库名] SET QUERY_STORE (STALE_QUERY_THRESHOLD_DAYS = 3);

2. 检查QueryStore是否处于只读模式 #

只读模式下QueryStore会停止所有写入和清理操作，通过上述查询的current_state_desc和readonly_reason字段判断：

• 如果current_state_desc为READ_ONLY，需排查触发原因：
  • 数据库磁盘空间不足
  • QueryStore的MAX_STORAGE_SIZE_MB已达上限（即使关闭了Size Based Cleanup，达到上限仍会进入只读）
  • 磁盘IO性能异常

3. 核查运行时统计间隔（Runtime Stats Interval） #

QueryStore的清理逻辑基于运行时统计间隔的结束时间，而非查询的最后执行时间。如果interval_length_minutes设置过大，会延迟清理触发时机：

SELECT interval_length_minutes
FROM sys.database_query_store_options;

若该值远大于默认的60分钟（比如设为43200分钟=30天），会导致清理任务要等到间隔周期结束才会处理旧数据，建议改回默认值或更小的数值：

ALTER DATABASE [你的数据库名] SET QUERY_STORE (INTERVAL_LENGTH_MINUTES = 60);

4. 排查后台清理任务的运行状态 #

QueryStore的清理由内部后台任务执行，可通过以下查询检查相关等待状态和运行时统计间隔：

-- 检查QueryStore相关等待事件
SELECT wait_type, wait_time_ms, signal_wait_time_ms
FROM sys.dm_os_wait_stats 
WHERE wait_type LIKE 'QUERY_STORE%';

-- 查看运行时统计间隔的起止时间
SELECT start_time, end_time, runtime_stats_interval_id
FROM sys.query_store_runtime_stats_interval
ORDER BY end_time DESC;

如果存在大量QUERY_STORE_CLEANUP等待，说明清理任务被阻塞，需进一步排查系统资源或锁冲突问题。

5. 分析旧查询的实际执行状态 #

查询超过30天的记录，确认其最后执行时间和状态：

SELECT 
    q.query_id,
    qt.query_sql_text,
    q.last_execution_time,
    q.state_desc,
    rs.last_execution_time AS stats_last_exec_time
FROM sys.query_store_query q
JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
LEFT JOIN sys.query_store_runtime_stats rs ON q.query_id = rs.query_id
WHERE DATEDIFF(day, ISNULL(rs.last_execution_time, q.last_execution_time), GETDATE()) > 30
ORDER BY q.last_execution_time DESC;

• 如果这类查询的last_execution_time实际是最近的，说明统计数据存在异常，可尝试手动清理后重新开启QueryStore；
• 如果确实是超过30天未执行的查询，说明清理逻辑未触发，需检查后台任务是否正常运行。

6. 对比正常实例的参数差异 #

与你提到的、设置30天能正常清理的同版本实例对比所有QueryStore参数，重点检查：

• QUERY_CAPTURE_MODE（是否为ALL/AUTO/NONE）
• MAX_STORAGE_SIZE_MB
• INTERVAL_LENGTH_MINUTES
• SIZE_BASED_CLEANUP_MODE

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