我正在运行 Query Store 以使数据库性能更好。直到现在它一直运行良好。当我尝试加载前一天的 Top 持续时间时,我花了 26 分钟来加载屏幕。
我正在运行的 SQL 版本是:Microsoft SQL Server 2017 (RTM-CU27) (KB5006944) - 14.0.3421.10 (X64) Oct 14 2021 00:47:52 版权所有 (C) 2017 Microsoft Corporation Standard Edition (64-bit) on Windows Server 2019 数据中心 10.0(内部版本 17763:)(管理程序)
查询在QS中弹出:
/*
This query text was retrieved from showplan XML, and may be truncated.
*/
SELECT TOP (@results_row_count)
p.query_id query_id,
q.object_id object_id,
ISNULL(OBJECT_NAME(q.object_id),'') object_name,
qt.query_sql_text query_sql_text,
ROUND(CONVERT(float, SUM(rs.avg_duration*rs.count_executions))*0.001,2) total_duration,
SUM(rs.count_executions) count_executions,
COUNT(distinct p.plan_id) num_plans
FROM sys.query_store_runtime_stats rs
JOIN sys.query_store_plan p ON p.plan_id = rs.plan_id
JOIN sys.query_store_query q ON q.query_id = p.query_id
JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
WHERE NOT (rs.first_execution_time > @interval_end_time OR rs.last_execution_time < @interval_start_time)
GROUP BY p.query_id, qt.query_sql_text, q.object_id
HAVING COUNT(distinct p.plan_id) >= 1
ORDER BY total_duration DESC
执行计划显示对象上的聚集索引扫描成本为 96% [plan_persist_runtime_stats]
。
以下执行计划来自不同的服务器:
我没有通过脚本配置查询存储,只是通过属性。但在我的设置之下:
ALTER DATABASE [<DATABASE>] SET QUERY_STORE (
OPERATION_MODE = READ_WRITE,
CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 15),
DATA_FLUSH_INTERVAL_SECONDS = 900,
MAX_STORAGE_SIZE_MB = 500,
INTERVAL_LENGTH_MINUTES = 60,
SIZE_BASED_CLEANUP_MODE = AUTO,
QUERY_CAPTURE_MODE = AUTO,
MAX_PLANS_PER_QUERY = 200
WAIT_STATS_CAPTURE_MODE = OFF
)
问题
如果是 SQL Server 错误,是否可以在某个地方找到具有此行为的版本列表?
有谁知道修复是什么?
核心问题是驱动 SSMS GUI 报告的 T-SQL 代码效率不高。不幸的是,微软长期以来一直在 SSMS 的部分内容中包含低效的 T-SQL。查询存储数据模型和构成 DMV 的代码使问题更加复杂。许多地方都有不寻常的性能陷阱。例如,对于某些查询存储 DMV ,连接消除可能无法按预期工作。
我将通过对您捕获的查询文本进行最小的努力来支持我的批评。照原样,对我的一个生产数据库执行原始代码需要 6 秒:
请注意,由于 runtime_stats_interval_id 的硬编码过滤器值,以下重写仅适用于我开发它的数据库:
新代码返回相同的结果,但在大约 600 毫秒内完成:
当然,以上内容对你没有太大帮助。这只是一个迹象,表明 SSMS 存在缺陷,Microsoft 可以通过最终的代码更改来解决该缺陷。我可以考虑以下选项来解决您的问题:
在不相关的说明中,我在您的问题中注意到您没有启用等待统计信息收集。如果可能的话,我鼓励你启用它。我发现它是解决查询超时原因的强大工具。不幸的是,它确实破坏了一些 GUI 报告。
如果您的 Query Store 配置得有点过大,那么对 Query Store 执行任何查询都会非常慢。
尝试减小查询存储的大小:
我们在 650 GB 大小的数据库上遇到了查询存储问题,其中 10240 MB 的查询存储大小基本上会破坏数据库。我们无法查询 QS,它会充满数千条 SQL 语句。
将 Query Store 的大小减少到 6 GB 以下是(在我们的例子中)无法查询 Query Store 问题的解决方案。
参考阅读
核对查询实际上解决了这个问题。使用下面的代码,我发现所有瓶颈,在该顶部持续时间再次在一秒钟内加载之后。