database 在 SQL SERVER 中监视 SQL 查询的进度
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3289670/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Monitoring the progress of an SQL query in SQL SERVER
提问by Roni Vered
I saw a similar question which asked how to monitor the progress of a backup/restore operation: Is there a SQL script that I can use to determine the progress of a SQL Server backup or restore process?
我看到了一个类似的问题，该问题询问如何监视备份/恢复操作的进度： 是否有 SQL 脚本可用于确定 SQL Server 备份或恢复过程的进度？
I would like to know if there's a similar query/way to see how much time the query has left until it will end.For example, one query usually has an elapsed time of 5 minutes. I would like to know how much time is left until it will end DURING the query's execution.
我想知道是否有类似的查询/方式来查看查询在结束之前还剩多少时间。例如，一个查询的经过时间通常为 5 分钟。我想知道在查询执行期间还剩下多少时间。
采纳答案by KM.
There is no way to know how much time is left. A query's runtime depends on many things beyond the actual query itself: locking/blocking of other queries, other processes consuming resources (CPU/disk usage), the operating system, network, etc. What if your 5-minute query is running, yet someone else kicks off a large report, your query may run 5:30 now. What if the someone starts to download a large file and hogs all the network bandwidth? What if the OS decides to do something in the background, etc. Until all the rows are returned, the query isn't done, but it can run in a variable time frame.
没有办法知道还剩下多少时间。查询的运行时间取决于实际查询本身之外的许多因素：其他查询的锁定/阻塞、消耗资源的其他进程（CPU/磁盘使用情况）、操作系统、网络等。如果您的 5 分钟查询正在运行，但是其他人启动了一个大型报告，您的查询现在可能会在 5:30 运行。如果有人开始下载大文件并占用所有网络带宽怎么办？如果操作系统决定在后台执行某些操作，等等。在返回所有行之前，查询并未完成，但它可以在可变时间范围内运行。
回答by John
What you want are Live Query Statistics.
你想要的是Live Query Statistics。
You can activate it in the most recent version of SSMS with a button next to the one that gives you the normal query plan:
您可以在最新版本的 SSMS 中使用提供正常查询计划的按钮旁边的按钮激活它：
This then gives you a live query plan:
这将为您提供实时查询计划：
At the bottom you see the total progress:
在底部，您会看到总进度：
回答by Andrey.ca
sys.dm_exec_requestshas that info, so something like that will give you the progress:
sys.dm_exec_requests有这些信息，所以这样的事情会给你进步：
SELECT
percent_complete
FROM sys.dm_exec_requests
--where session_id=51 or command like 'restore%'
回答by JameSQL
Yes you can know the estimated elapsed time unless there would be some unexpected situation affecting the execution of the process.
是的，您可以知道估计的经过时间，除非有一些意外情况影响流程的执行。
Select total_elapsed_time,
* from sys.dm_exec_sessions where session_id="your Id here"
