SQL Server 2005 | Queries Going Into Suspended Mode

Question

Hi,

We moved to SQL server 2005 (SP1) recently and are observing a strange problem. During our performance tuning runs, some queries would never finish execution. Looking at the traces on the client side we don't get any hint of root cause. We see traces right upto the DB operation API (for instance C#'s ExecuteCommand) and no traces beyond that. The call never returns/errors out despite setting the command timeout to 30 sec. On the server side we see the SPIDs of these queries in suspended state with one of the below mentioned wait types (so far):
-resource_semaphore_query_compile
-ASYNC_NETWORK_IO
-Cxpacket

Server config:
Winserver 2003
4 dual core Xeon 3.2 Ghz
18 gigs of RAM
parallelism level set to 6

Has anyone experienced something similar on SQL server 2005 ?

Regards,

Answer 1

Did you ever find a solution to your problem? I have exactly the same issue.

 

felix

Answer 2

Got it just an hour back. SQL server 2005 Sp2 contains the fix. I am going to post the actual issue also soon.

Answer 3

Ok guys.. here is the explanation:

This is an issue that is new in SQL Server 2005, and is only exposed under specific conditions – namely, the use of the x64 version of SQL Server 2005 and the execution of large ad hoc query batches.  Unfortunately, the memory that is used to store the execution plans for those ad hoc queries is not trimmed aggressively enough in the x64 version, and as a result we see more and more connections having to wait for memory allocations before they can continue.  If any connection is suspended for more than 30 seconds, the application terminates the connection by reporting that the command currently executing has timed out.
This issue has been previously identified and is fixed in the upcoming service pack release (SP2) for SQL Server 2005, the Community Technology Preview (CTP) release of which is available today, and is confirmed to fix the issue in your environment. 

Possible Solutions:

1. Sp2

5)      2. create a job that manually frees the memory that is being consumed by query plans through the use of the DBCC FREEPROCCACHE command.  The documentation for this command states that “freeing the procedure cache causes, for example, an ad hoc SQL statement to be recompiled instead of reused from the cache.” (http://msdn2.microsoft.com/en-us/library/ms174283.aspx) 

We have not tried out the second solution so far.

Answer 4

Mohit:

Did SP2 fix your issue?  We are having a similar issue - I have put up a post on that:

http://forums.microsoft.com/msdn/ShowPost.aspx?postid=1166196&isthread=true&siteid=1&authhash=c5ac04fe77f86cfd4ab0c0e93fc0fe24326c7351&ticks=633056877842757142

We are still seeing this issue even after applying SP2 (CTP from December)...did you have to do anything else besides the application of SP2?

 

Answer 5

Change the Max Degree of Parallelism argument at the server/instance level, to something less than # or processors.  Ours was at max and we saw instant improvement with this.

Answer 6

Hi there,

Has anyone found a resolution to this?

I am experiencing the same problem even after loading SQL 2005 SP2.

Currently, I am running with:

2 Quad Core Xeon

8 GB Memory

SQL 2005 STD x64

Windows XP Prof x64

My SSIS Data Flow Task performs a simple query against 2 large tables and produces a resultset with ~90M rows and loads it into another table using Fast Load. This is all happening within 1 database. It runs for the first ~75M rows and then freezes. Looking at the Activity monitor, I could see that the SELECT process gets suspended on CXPACKET while the BULK INSERT process gets suspended on ANSYC_NETWORK_IO. 

Can anyone help me?

Thanks,

Ben

Answer 7

I believe I am having the same problem (after installing SP2). Also see my thread on the subject, which contain some diagnostics taken from a stalling query:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1384867&SiteID=1&mode=1

Regards,
Lars

Answer 8

I had the same issue after installing SP2. However, I noticed when I did the bcp and added the /b switch with some number to comit, /b50000, the suspended status never appeared.

 

Answer 9

Hello,

 

my solution was to set 'Maximum insert commit size' value to 50000, for all OLD DB Destination in my packages.

And the random 'Suspended mode' then disappeared.

 

Greg

Answer 10

Did someone have fixed this?

We are facing the same problem using SP2 but not in a package.

We have a internal code (ADO.NET) that creates multiple queries and after running more than 20 minutes the spid (connection) goes to SUSPENDED and does not come back anymore.

How to fix this?

thanks

Answer 11

did anybody fixed this? please let me know we have similar problems on sql server 2005 where query remaining in suspended state for looooooooooooong time!!!! pl help

 

Answer 12

 

did anybody fixed this? please let me know we have similar problems on sql server 2005 where query remaining in suspended state for looooooooooooong time!!!! pl help

Answer 13

Hello,

    can anyone help i am facing same problem large adhoc queries going in suspend mode.

 

I have

Enterprise Edition 9.00.3042

Platform NT intel x86

Ram 8Gb

4 processor

 

and Max Degree of parallelism 4

cost threshold for parallelism 5

 

 

 

Thanks,

NP

 

 

Answer 14

All,
a few months late but though someone else might be having this same issue. Changing the memory as described above resolved the issue for me. In SQL right click on SQL Server Instance, Properties, Memory and changed the maximim memory to 50000. Processes now work fine. Hope this help.


MO

Answer 15

@mgtceo : i tried to set the max memory to 50000 and it seemed to reduce the suspended connection, thanx. i will monitor the server over a period of time.

Answer 16

@mgtceo : i tried to set the max memory to 50000 and it seemed to reduce the suspended connection, thanx. i will monitor the server over a period of time.

i have checked the mamory page of server property, we have 2147483647 max memory levels, we still have the same issue. suspended with networ i/o.

any suggestions.