Suspended status in SQL Activity Monitor

Question

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

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.

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

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

However, the issue still occurs with a different query.

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

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).

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.

Answer 1

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.

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

Answer 2

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.

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.

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.