Solved

OutofMemoryException - ASYNC_NETWORK_IO

Posted on 2013-06-17
12
457 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
ID: 39252711
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
ID: 39252760
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
ID: 39254178
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 39254210
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
ID: 39254418
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
ID: 39254835
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
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 150 total points
ID: 39254994
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
ID: 39255268
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
ID: 39261286
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
ID: 39262379
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
ID: 39263166
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
ID: 39300939
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

820 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