Solved

OutofMemoryException - ASYNC_NETWORK_IO

Posted on 2013-06-17
12
449 Views
Last Modified: 2013-07-04
Hi Experts!

I have this application that whenever I open a particular module, it has this error:

out of memory exception
I checked the database using DMV query and I saw Wait Type - ASYNC_NETWORK_IO and the query is:

SELECT        PrintAds.PrintAdsID, PrintAds.AdsID, PrintAds.AdsCode, Ads.Height, Ads.Width, Ads.TotalSize, Ads.Duration, PrintAds.TypeOfAdsID, PrintAds.TypeOfAds,                            PrintAds.ADinProgram, PrintAds.Commercial, PrintAds.AdsDate, PrintAds.MediaTitleID, PrintAds.MediaTitleCode, PrintAds.MediaTitleName, PrintAds.SectionID,                            PrintAds.SectionCode, PrintAds.Section, PrintAds.Page, PrintAds.TotalPage, PrintAds.Time, PrintAds.TimeTo, PrintAds.DividedBy, PrintAds.AdValue, PrintAds.ColorID,                            PrintAds.Color, PrintAds.PositionID, PrintAds.PositionCode, PrintAds.Position, PrintAds.ProgrammeID, PrintAds.ProgrammeName, PrintAds.ProgrammeDetailsID,                            PrintAds.ProgrammeBCTime, PrintAds.ProgrammeBCTimeTo, PrintAds.LanguageVersionID, PrintAds.LanguageVersionCode, PrintAds.LanguageVersionName,                            PrintAds.LangID, PrintAds.LangName, PrintAds.Breaks, (CASE WHEN substring(Breaks, 2, 1) = '/' THEN LEFT(Breaks, 1) ELSE CASE WHEN substring(Breaks, 3, 1)                            = '/' THEN LEFT(Breaks, 2) ELSE '' END END) AS Breaks1, (CASE WHEN substring(Breaks, 2, 1) = '/' THEN substring(Breaks, 3, 2)                            ELSE CASE WHEN substring(Breaks, 3, 1) = '/' THEN substring(Breaks, 4, 2) ELSE '' END END) AS Breaks2, PrintAds.TBreaks, (CASE WHEN substring(TBreaks, 2, 1)                            = '/' THEN LEFT(TBreaks, 1) ELSE CASE WHEN substring(TBreaks, 3, 1) = '/' THEN LEFT(TBreaks, 2) ELSE '' END END) AS TBreaks1,                            (CASE WHEN substring(TBreaks, 2, 1) = '/' THEN substring(TBreaks, 3, 2) ELSE CASE WHEN substring(TBreaks, 3, 1) = '/' THEN substring(TBreaks, 4, 2)                            ELSE '' END END) AS TBreaks2, PrintAds.Master, PrintAds.XKMSampleID, PrintAds.XKMadid, PrintAds.CreatedUserID, PrintAds.CreatedDateTime,                            PrintAds.ModifiedUserID, PrintAds.ModifiedDateTime, PrintAds.DeletedUserID, PrintAds.DeletedDateTime, PrintAds.AuditedUserID, PrintAds.AuditedDateTime,                            PrintAds.TranslatedUserID, PrintAds.TranslatedDateTime, PrintAds.OtherTranslatedUserID, PrintAds.OtherTranslatedDateTime, PrintAds.CodedUserID,                            PrintAds.CodedDateTime, PrintAds.OtherUserID, PrintAds.OtherDateTime  FROM            PrintAds INNER JOIN                           Ads ON PrintAds.AdsID = Ads.AdsID

Open in new window


First, I checked network if this is the real problem but when I tried to install the application on the server, it still has the error. I also checked the plan cache
total_worker_time/execution_count and the result is 8864747

I also have this query plan for the query above:

query plan
The server has 7.8GB RAM and I allocated 4GB RAM for SQL Server Memory

My question is: What will be your advice to prevent this error? or Do you have any advice to that query?
0
Comment
Question by:MediaBanc
  • 4
  • 4
  • 3
  • +1
12 Comments
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
Comment Utility
What will be your advice to prevent this error?
Increase RAM.  Wrap up your query in a stored procedure.  Please make sure that SQL Server does not compete for resources with another application.
0
 

Author Comment

by:MediaBanc
Comment Utility
Hi Racimo,

I used Perfmon for Available MBytes and it is 4.3GB while running the application.

Another information, the database size is 1.3GB.

Thanks!
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
We need some additional info:
Is SQL Server running standalone as recommended?
How are you executing the query?
Is the application that is executing the query on a different box?

The ASYNC_NETWORK_IO wait state simply means that SQL Server is waiting on the network for additional data.
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
Comment Utility
The ASYNC_NETWORK_IO wait state simply means that SQL Server is waiting on the network for additional data.

You are probably aware of this but ASYNC_NETWORK_IO  also occurs when the client does not process, due to lack of memory most of the time), the data sent by the server fast enough (for example when large data sets are sent to the client). In this case, the server waits for the client .  more on this...

http://blogs.msdn.com/b/joesack/archive/2009/01/09/troubleshooting-async-network-io-networkio.aspx
0
 
LVL 60

Accepted Solution

by:
chapmandew earned 200 total points
Comment Utility
How much data is your query bringing back?  What kind of data structure are you storing the data in on the client side?  This sounds like an application level problem and not a SQL level problem.
0
 

Author Comment

by:MediaBanc
Comment Utility
Hi acperkins,

Is SQL Server running standalone as recommended?

Yes, I also put user database inside different spindle, tempdb is also inside different spindle same with the log files.

How are you executing the query?

With regards to the system event, upon clicking the module, it runs the query above before the window opens.

Is the application that is executing the query on a different box?

The application is actually in a different box, it just happened that I need to test inside the server to check if it is really a network issue. Since I run it inside the SQL Server box and still have same issue, I think it's not a network issue.
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 150 total points
Comment Utility
If SQL Server is running standalone then you should not be limiting it to only 4GB.  Set it to at least 6GB.

The application is actually in a different box, it just happened that I need to test inside the server to check if it is really a network issue.
Right, but how are you executing it?  If you have are executing from the server than SQL Server is not running standalone.  Have you tried running the query from the application on a different box? Have you tried running the query from SSMS? How many rows should be returned?  

This sounds like an application level problem and not a SQL level problem.
Absolutely.  Although the author needs to ensure that there is an index on PrintAds.AdsID and Ads.AdsID.
0
 
LVL 23

Assisted Solution

by:Racim BOUDJAKDJI
Racim BOUDJAKDJI earned 150 total points
Comment Utility
What kind of data structure are you storing the data in on the client side?
Definitely a good track.  Among frequent reasons to explain:

> Lack of client side RAM resources: adding RAM on the client helps and constitutes a fast way to troubleshoot this.
> Excessive client side cache resources: exceptions related to unstable data structures receiving the data sent.
> Poor client side connection pooling : when there is too much data sent through a single pool, there may be a client side network congestion botlleneck.


hope this helps.
0
 

Author Comment

by:MediaBanc
Comment Utility
Hi acperkins,

If SQL Server is running standalone then you should not be limiting it to only 4GB.  Set it to at least 6GB.
Now I set it to 6GB.

If you have are executing from the server than SQL Server is not running standalone.
Originally, the application is running from a client. I just installed the application inside the server since I saw the wait type and to see if it has the same error with the client. Since it has the same error, I assume that it is not a network problem.

Have you tried running the query from the application on a different box?
Yes, same wait type

Have you tried running the query from SSMS? How many rows should be returned?
806219 rows

Hi chapmandew,

What kind of data structure are you storing the data in on the client side?
I have this Create Table Statement for both tables used in query

CREATE TABLE [dbo].[Ads](
	[AdsID] [int] IDENTITY(1,1) NOT NULL,
	[AdsCode] [nvarchar](255) NOT NULL,
	[TypeOfAdsID] [int] NULL,
	[TypeOfAds] [nvarchar](50) NULL,
	[ADinProgram] [int] NULL,
	[Commercial] [int] NULL,
	[VersionID] [int] NULL,
	[VersionCode] [nvarchar](50) NOT NULL,
	[Version] [nvarchar](50) NOT NULL,
	[AdsDate] [smalldatetime] NOT NULL,
	[MediaTitleID] [int] NOT NULL,
	[MediatitleCode] [nvarchar](50) NOT NULL,
	[MediaTitleName] [nvarchar](255) NOT NULL,
	[MediaTypeID] [int] NULL,
	[MediaTypeCode] [nvarchar](50) NOT NULL,
	[MediaTypeName] [nvarchar](50) NOT NULL,
	[SectionID] [int] NOT NULL,
	[SectionCode] [nvarchar](50) NOT NULL,
	[Section] [nvarchar](50) NOT NULL,
	[CountryCode] [nvarchar](50) NULL,
	[Country] [nvarchar](50) NULL,
	[Page] [nvarchar](50) NULL,
	[TotalPage] [nvarchar](50) NULL,
	[Time] [nvarchar](50) NULL,
	[TimeTo] [nvarchar](50) NULL,
	[FileSize] [nvarchar](50) NULL,
	[Height] [numeric](10, 0) NULL,
	[Width] [numeric](10, 0) NULL,
	[TotalSize] [numeric](18, 1) NULL,
	[DividedBy] [int] NULL,
	[AdValue] [numeric](18, 2) NULL,
	[ColorID] [int] NULL,
	[Color] [nvarchar](50) NULL,
	[Duration] [nvarchar](50) NULL,
	[PositionID] [int] NULL,
	[PositionCode] [nvarchar](50) NULL,
	[Position] [nvarchar](50) NULL,
	[ProgrammeID] [int] NULL,
	[ProgrammeName] [nvarchar](255) NULL,
	[ProgrammeDetailsID] [int] NULL,
	[ProgrammeBCTime] [nvarchar](50) NULL,
	[ProgrammeBCTimeTO] [nvarchar](50) NULL,
	[Copyline] [nvarchar](255) NOT NULL,
	[Copyline_TH] [nvarchar](255) NULL,
	[Filename] [nvarchar](4000) NOT NULL,
	[LanguageVersionID] [int] NULL,
	[LanguageVersionCode] [nvarchar](50) NOT NULL,
	[LanguageVersionName] [nvarchar](50) NOT NULL,
	[LangID] [int] NOT NULL,
	[LangName] [nvarchar](50) NOT NULL,
	[BrandID] [int] NOT NULL,
	[Brand] [nvarchar](255) NOT NULL,
	[SubBrandID] [int] NOT NULL,
	[SubBrand] [nvarchar](255) NOT NULL,
	[MainCategoryID] [int] NOT NULL,
	[MainCategory] [nvarchar](255) NOT NULL,
	[SubCategoryID] [int] NOT NULL,
	[SubCategory] [nvarchar](255) NOT NULL,
	[SubSubCategoryID] [int] NOT NULL,
	[SubSubCategory] [nvarchar](255) NOT NULL,
	[MaterialID] [int] NULL,
	[Material] [nvarchar](255) NULL,
	[CommentFaultID] [int] NULL,
	[CommentFault] [nvarchar](255) NULL,
	[XKMSampleID] [nvarchar](255) NULL,
	[XKMadid] [nvarchar](255) NULL,
	[MergeAdsID] [int] NULL CONSTRAINT [DF_Ads_MergeAdsID]  DEFAULT ((0)),
	[Status] [int] NULL,
	[CreatedUserID] [int] NOT NULL,
	[CreatedDateTime] [datetime] NULL,
	[ModifiedUserID] [int] NULL,
	[ModifiedDateTime] [datetime] NULL,
	[DeletedUserID] [int] NULL,
	[DeletedDateTime] [datetime] NULL,
	[AuditedUserID] [int] NULL,
	[AuditedDateTime] [datetime] NULL,
	[TranslatedUserID] [int] NULL,
	[TranslatedDateTime] [datetime] NULL,
	[OtherTranslatedUserID] [int] NULL,
	[OtherTranslatedDateTime] [datetime] NULL,
	[CodedUserID] [int] NULL,
	[CodedDateTime] [datetime] NULL,
	[OtherUserID] [int] NULL,
	[OtherDateTime] [datetime] NULL,
	[OldAdsStatus] [bit] NULL,
 CONSTRAINT [PK_Ads] PRIMARY KEY CLUSTERED 
(
	[AdsID] ASC,
	[AdsCode] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = ON, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]

Open in new window


USE [ads_elibrary]
GO
/****** Object:  Table [dbo].[PrintAds]    Script Date: 06/20/2013 09:27:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[PrintAds](
	[PrintAdsID] [int] IDENTITY(1,1) NOT NULL,
	[AdsID] [int] NOT NULL,
	[AdsCode] [nvarchar](255) NOT NULL,
	[TypeOfAdsID] [int] NULL,
	[TypeOfAds] [nvarchar](50) NULL,
	[ADinProgram] [int] NULL,
	[Commercial] [int] NULL,
	[AdsDate] [smalldatetime] NOT NULL,
	[MediaTitleID] [int] NOT NULL,
	[MediaTitleCode] [nvarchar](50) NOT NULL,
	[MediaTitleName] [nvarchar](255) NOT NULL,
	[SectionID] [int] NOT NULL,
	[SectionCode] [nvarchar](50) NOT NULL,
	[Section] [nvarchar](50) NOT NULL,
	[Page] [nvarchar](50) NULL,
	[TotalPage] [nvarchar](50) NULL,
	[Time] [nvarchar](50) NULL,
	[TimeTo] [nvarchar](50) NULL,
	[DividedBy] [int] NULL,
	[AdValue] [numeric](18, 2) NULL,
	[ColorID] [int] NULL,
	[Color] [nvarchar](50) NULL,
	[PositionID] [int] NULL,
	[PositionCode] [nvarchar](50) NULL,
	[Position] [nvarchar](50) NULL,
	[ProgrammeID] [int] NULL,
	[ProgrammeName] [nvarchar](255) NULL,
	[ProgrammeDetailsID] [int] NULL,
	[ProgrammeBCTime] [nvarchar](50) NULL,
	[ProgrammeBCTimeTo] [nvarchar](50) NULL,
	[LanguageVersionID] [int] NULL,
	[LanguageVersionCode] [nvarchar](50) NOT NULL,
	[LanguageVersionName] [nvarchar](50) NOT NULL,
	[LangID] [int] NULL,
	[LangName] [nvarchar](50) NULL,
	[Breaks] [nvarchar](10) NULL,
	[AutoBreaks] [bit] NULL CONSTRAINT [DF_PrintAds_AutoBreaks]  DEFAULT ((1)),
	[TBreaks] [nvarchar](10) NULL,
	[AutoTBreaks] [bit] NULL CONSTRAINT [DF_PrintAds_AutoTBreaks]  DEFAULT ((1)),
	[Master] [int] NULL,
	[XKMSampleID] [nvarchar](255) NULL,
	[XKMadid] [nvarchar](255) NULL,
	[Status] [int] NULL CONSTRAINT [DF_PrintAds_Status]  DEFAULT ((1)),
	[CreatedUserID] [int] NULL,
	[CreatedDateTime] [datetime] NULL,
	[ModifiedUserID] [int] NULL,
	[ModifiedDateTime] [datetime] NULL,
	[DeletedUserID] [int] NULL,
	[DeletedDateTime] [datetime] NULL,
	[AuditedUserID] [int] NULL,
	[AuditedDateTime] [datetime] NULL,
	[TranslatedUserID] [int] NULL,
	[TranslatedDateTime] [datetime] NULL,
	[OtherTranslatedUserID] [int] NULL,
	[OtherTranslatedDateTime] [datetime] NULL,
	[CodedUserID] [int] NULL,
	[CodedDateTime] [datetime] NULL,
	[OtherUserID] [int] NULL,
	[OtherDateTime] [datetime] NULL,
	[ImportUserID] [int] NULL,
	[ImportDateTime] [datetime] NULL,
 CONSTRAINT [PK_Ads_Detail] PRIMARY KEY CLUSTERED 
(
	[PrintAdsID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = ON, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]

Open in new window


Thanks guys!
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
Since it has the same error, I assume that it is not a network problem.
First a wait type is not an error (the error is the Out of Memory Exception) and second you are confusing ASYNC_NETWORK_IO with a "network" problem.  Again SQL Server is waiting on the client (whether it comes from a different box or the same is irrelevant) for the data.

Simply put you are passing too much information, you will have to redesign or break it up into batches.
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
Comment Utility
Now I set it to 6GB.
Is the client the same as the server box ?  I mentioned that ...

adding RAM on the client helps and constitutes a fast way to troubleshoot this.

which means that you should add memory on the box hosting the application not necessarily on the box hosting SQL Server.

Again SQL Server is waiting on the client (whether it comes from a different box or the same is irrelevant) for the data.
To be detailed, the server box waits for the client box to acknowledge the full reception of data set output.  This wait occurs often when the client can not receive/process, for some reason, the full data set sent, fast enough and SQL Server must put the emitting (let's call it streaming) of data on hold.  By design, SQL Server won't unlock data until the server side execution output until it is fully sent to the client according to the session request.  For instance, this situation happens when large query outputs are to be sent to the client and the client does not have sufficient memory pool to process/receive it and send and end a success request to the server.  

In other words, ASYNC_NETWORK_IO may be cause by a network bottleneck but it can also be caused by a lack of resources on the client box even if the network is perfectly fine.

Hope this clarifies.
0
 

Author Closing Comment

by:MediaBanc
Comment Utility
Hi Guys!

Thanks for the help! My problem is an application problem and not a SQL Server problem.. The developers need to revise the codes.
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now