SQL Server 中的查询持续时间估计 [英] Query duration estimation in SQL Server

问题描述

我在 Oracle 10g 中看到一个功能可以估计长时间运行的查询的剩余时间，我想知道这在 SQL Server 中是否也可能(至少在 2008 年?)?

I've seen in Oracle 10g a feature that estimates the remaining time for a long running query and I was wondering if this is possible too in SQL Server (at least in 2008?)?

假设我有一个包含数千万行(索引良好等)的非常大的表，我需要搜索一些特定的行.我知道这需要很多时间，我对此很满意，但我想向用户展示某种进度条.

Suppose I have a very large table with tens of millions of rows (well indexed etc. etc.) and I need to search for some particular rows. I know it will take a lot of time and I'm cool with that but I would like to present the user with some kind of a progress bar.

如何显示进度?

推荐答案

我会忘记它，只是放了一个旋转的圆圈！

I'd forget about it and just put a spinning circle!

说真的，为了进一步理解 MrTelly 的想法，有动态管理视图可以为您提供某些查询的平均执行时间 - 也许这可以让您有所收获.

Seriously though, to take MrTelly's idea further, there are dynamic management views that can give you average execution times for certain queries - maybe that can get you somewhere.

http://msdn.microsoft.com/en-us/library/ms188754.aspx

我在 Adam Machanic 的 sp_whoisactive 过程中看到了完成百分比.也许这是另一个值得研究的地方.SQL 2016 具有持久化计划缓存信息的查询存储 - 替代 dmv 计划缓存，它在重新启动时被清除.

edit: I have seen percent complete in the sp_whoisactive procedure from Adam Machanic. Maybe that is another place to look into. SQL 2016 has the query store which persists plan cache information - a substitute for the dmv plan cache, which is cleared on reboot.

这篇关于SQL Server 中的查询持续时间估计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！