是否可以为 Microsoft SQL Server 上的 SQL 查询设置超时? [英] Is it possible to set a timeout for a SQL query on Microsoft SQL Server?

问题描述

我遇到过这样的情况:有时用户选择正确的参数并执行需要几分钟或更长时间才能执行的查询.我不能阻止他选择这样的参数组合(这很合法)，所以我想在查询上设置超时.

I've got a scenario when sometimes a user selects the right parameters and makes a query which takes several minutes or more to execute. I cannot prevent him to select such a combination of parameters (it's quite legal), so I'd like to set a timeout on the query.

请注意，我真的想停止查询执行本身并回滚任何事务，否则它会占用大部分服务器资源.添加一个不耐烦的用户，他会重新启动应用程序并再次尝试组合，这样您就有了灾难的秘诀(阅读:SQL Server DoS).

Note that I really want to stop the query execution itself and rollback any transactions, because otherwise it hogs up most of server resources. Add an impatient user who restarts the application and tries the combination again, and you've got a recipe for a disaster (read: SQL Server DoS).

这能做到吗?如何做到?

Can this be done and how?

推荐答案

据我所知，除了在客户端设置命令或连接超时之外，没有办法在客户端中逐个查询更改超时服务器.

As far as I know, apart from setting the command or connection timeouts in the client, there is no way to change timeouts on a query by query basis in the server.

您确实可以使用 sp_configure，但这些是服务器范围的.

You can indeed change the default 600 seconds using sp_configure, but these are server scoped.

这篇关于是否可以为 Microsoft SQL Server 上的 SQL 查询设置超时?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！