SQL Server 2017 QueryStore相关DMV删除语句资源占用及访问问题求助

背景说明 #

sys.plan_persist_wait_stats和sys.plan_persist_plan是Query Store内部使用的未公开系统表，官方无文档，普通权限无法直接查询。你遇到的高资源删除语句，本质是Query Store在清理历史数据，而SentryOne的监控触发了这个操作。

解决方案步骤 #

1. 调整Query Store清理配置 #

频繁的自动清理是资源占用的核心原因，先检查并修改Query Store的保留与清理策略：

• 查看当前配置：

SELECT name, state_desc, flush_interval_seconds, retention_days, size_based_cleanup_mode_desc, query_capture_mode_desc
FROM sys.database_query_store_options;

• 优化配置（示例：延长保留天数、关闭基于大小的自动清理）：

ALTER DATABASE [你的数据库名]
SET QUERY_STORE (RETENTION_DAYS = 90, SIZE_BASED_CLEANUP_MODE = OFF);

根据业务需求调整retention_days数值，避免过短导致频繁清理。

2. 限制SentryOne账号的资源使用 #

针对SentryOne的登录账号设置资源调控，避免其操作占用过多CPU/IO：

-- 创建自定义资源池（限制CPU和IO上限）
CREATE RESOURCE POOL SentryOnePool
WITH (MAX_CPU_PERCENT = 30, MAX_IOPS_PER_VOLUME = 1000);
GO

-- 创建绑定到该池的工作负载组
CREATE WORKLOAD GROUP SentryOneGroup
USING SentryOnePool;
GO

-- 创建分类器函数，将SentryOne账号定向到自定义组
CREATE FUNCTION dbo.SentryOneClassifier()
RETURNS sysname
WITH SCHEMABINDING
AS
BEGIN
    IF SUSER_SNAME() = 'SentryOne登录账号名'
        RETURN 'SentryOneGroup';
    RETURN 'default';
END;
GO

-- 启用资源调控器
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.SentryOneClassifier);
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

根据实际环境调整资源限制数值，确保不影响SentryOne的正常监控。

3. 切换为手动清理Query Store #

关闭自动清理后，在业务低峰期手动执行清理，避免资源竞争：

• 关闭自动清理：

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

• 手动清理旧数据：

ALTER DATABASE [你的数据库名]
SET QUERY_STORE CLEANUP;

可以将手动清理配置为SQL Agent作业，定期在夜间执行。

4. 升级SQL Server版本 #

SQL Server 2017 CU26属于较旧版本，后续CU（如CU35）优化了Query Store的清理逻辑，能有效降低资源占用。升级前务必做好全库备份，验证兼容性后执行升级。

5. 内部表的临时访问（仅用于排查） #

若需排查内部表数据，需使用专用管理员连接（DAC），且仅sysadmin权限可操作：

• 在SSMS中，服务器名前添加ADMIN:（如ADMIN:你的服务器名）建立连接，然后执行：

SELECT * FROM sys.plan_persist_wait_stats;
SELECT * FROM sys.plan_persist_plan;

注意：内部表结构无官方保障，版本升级可能变更，不建议作为日常操作依据。

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