SQL 活动监视器中的挂起状态

Suspended status in SQL Activity Monitor

提问by Racter

What would cause a query being done in Management Studio to get suspended?

什么会导致在 Management Studio 中执行的查询被暂停？

1. I perform a simple select top 60000 from a table (which has 11 million rows) and the results come back within a sec or two.
2. I change the query to top 70000 and the results take up to 40 min.

1. 我从一个表（有 1100 万行）中执行一个简单的 select top 60000，结果在一两秒内返回。
2. 我将查询更改为 top 70000，结果最多需要 40 分钟。

From doing a bit of searching on another but related issue I came across someone using DBCC FREEPROCCACHE to fix it.

通过对另一个但相关的问题进行一些搜索，我遇到了使用 DBCC FREEPROCCACHE 来修复它的人。

1. I run DBCC FREEPROCCACHE and then redo the query for 70000 and it seemmed to work.

1. 我运行 DBCC FREEPROCCACHE，然后重做 70000 的查询，它似乎工作。

However, the issue still occurs with a different query.

但是，使用不同的查询仍然会出现此问题。

1. I increase to say 90000 or if I try to open the table using [Right->Open Table], it pulls about 8000 records and stops.

2. 我增加说 90000 或者如果我尝试使用 [Right->Open Table] 打开表，它会拉出大约 8000 条记录并停止。

Checking the activity log for when I do the Open Table shows the session has been suspended with a wait type of "Async_Network_IO". For the session running the select of 90000 the status is "Sleeping", this is the same status for the above select 70000 query which did return but in 45min. It is strange to me that the status shows "Sleeping" and it does not appear to be changing to "Runable" (I have the activiy monitor refreshing ever 30sec).

检查活动日志以了解何时打开表显示会话已暂停，等待类型为“Async_Network_IO”。对于运行 90000 选择的会话，状态为“睡眠”，这与上述选择 70000 查询的状态相同，但在 45 分钟内返回。我很奇怪状态显示“睡眠”并且它似乎没有更改为“可运行”（我的活动监视器每 30 秒刷新一次）。

Additional notes:

补充说明：

• I am not running both the Open Table and select 90000 at the same time. All queries are done one at a time.
• I am running 32bit SQL Server 2005 SP2 CU9. I tried upgrading to SP3 but ran into install failurs. The issues was occuring prior to me trying this upgrade.
• Server setup is an Active/Active cluster the issue occurs on either node, and the other instance does not have this issue.
• I have ~20 other database on this same server instance but only this one DB is seeing the issue.
• This database gets fairly large. It is currently at 76756.19MB. Data file is 11,513MB.
• I am logged in locally on the Server box using Remote Desktop.

• 我没有同时运行 Open Table 和选择 90000。所有查询一次完成一个。
• 我正在运行 32 位 SQL Server 2005 SP2 CU9。我尝试升级到 SP3，但遇到安装失败。这些问题是在我尝试此升级之前发生的。
• 服务器设置是一个 Active/Active 集群，问题发生在任一节点上，而另一个实例没有此问题。
• 我在同一个服务器实例上有大约 20 个其他数据库，但只有这个数据库看到了这个问题。
• 这个数据库变得相当大。它目前为 76756.19MB。数据文件为 11,513MB。
• 我使用远程桌面本地登录到服务器框。

采纳答案by Chris Chilvers

The wait type "Async_Network_IO" means that its waiting for the client to retrieve the result set as SQL Server's network buffer is full. Why your client isn't picking up the data in a timely manner I can't say.

等待类型“Async_Network_IO”意味着它等待客户端检索结果集，因为 SQL Server 的网络缓冲区已满。为什么你的客户没有及时获取数据我不能说。

The other case it can happen is with linked servers when SQL Server is querying a remote table, in this case SQL Server is waiting for the remote server to respond.

另一种可能发生的情况是当 SQL Server 查询远程表时链接服务器，在这种情况下 SQL Server 正在等待远程服务器响应。

Something worth looking at is virus scanners, if they are monitoring network connections sometimes they can get lagged, its often apparent by them hogging all the CPU.

值得一看的是病毒扫描程序，如果它们正在监视网络连接，有时它们可​​能会滞后，通常很明显它们会占用所有 CPU。

回答by Raj More

Suspended means it is waiting on a resource and will resume when it gets its resource. Judging from the sizes you are pulling back, it seems you are in an OLAP type of query.

暂停意味着它正在等待资源，并在获得资源时恢复。从您撤回的大小来看，您似乎处于 OLAP 类型的查询中。

Try the following things:

尝试以下几点：

1. Use NOLOCK or set the TRANSACTION ISOLATION LEVEL at the top of the query
2. Check your execution plan and tune the query to be more efficient

1. 使用 NOLOCK 或在查询顶部设置 TRANSACTION ISOLATION LEVEL
2. 检查您的执行计划并调整查询以提高效率