MediaBanc
asked on
OutofMemoryException - ASYNC_NETWORK_IO
Hi Experts!
I have this application that whenever I open a particular module, it has this error:
I checked the database using DMV query and I saw Wait Type - ASYNC_NETWORK_IO and the query is:
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/executio
I also have this query plan for the query above:
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?
I have this application that whenever I open a particular module, it has this error:
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.AdsIDFirst, 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/executio
I also have this query plan for the query above:
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?
ASKER
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!
I used Perfmon for Available MBytes and it is 4.3GB while running the application.
Another information, the database size is 1.3GB.
Thanks!
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.
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.
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
Thanks guys!
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]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]Thanks guys!
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.
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.
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.
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.
ASKER
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.
Thanks for the help! My problem is an application problem and not a SQL Server problem.. The developers need to revise the codes.
Increase RAM. Wrap up your query in a stored procedure. Please make sure that SQL Server does not compete for resources with another application.