ASYNC_NETWORK_IO wait type on stored procedure that has finished giving the results to the web application and that is blocking another stored procedure

Question

ASYNC_NETWORK_IO wait type on stored procedure that has finished giving the results to the web application and that is blocking another stored procedure

mwitt 96024

SSC-Addicted

Points: 404
More actions
September 7, 2012 at 1:05 pm

#267372

Greetings,
I am a developer and am not a DBA or a Networking Engineer. I am just a lowly Developer. Please pardon my ignorance.
I have read bunches upon bunches of stuff on the Internet but I cannot figure out a resolution to my problem.
There is a web app that uses C# in the code behind and SQL Server 2008 R2. The SQL Server instance resides on another Windows Server 2003 machine.
When the Update report (ActiveReports 6 code based report) is loaded into a ReportViewer.aspx web page’s ActiveReports 6 WebViewer control, the code behind for the Update report’s file runs a stored procedure called ap_OvenProgramDataByOvenProgramID. The Update report’s file fills a SqlDataReader with the results of this stored procedure. The results can range for example from 50-60 rows to 100-200 rows and maybe up to 500-600 rows. So this stored procedure does NOT fill the SqlDataReader with hundreds of thousands of rows.
In that Update report’s file, that SqlDataReader that has been filled with the results of that stored procedure is used in the Update report file’s FetchData event. Again, the file for this Update report is an ActiveReports 6 code-based file and is NOT an ActiveReports 6 xml-based file.
In the Update file’s ReportEnd event, that SqlDataReader is closed and made null if it is not already null.
The Update report loads into the ActiveReports 6 WebViewer that is in the ReportViewer.aspx page. I see all of the data in the report. The report has loaded.
On the ReportViewer.aspx page underneath the WebViewer, I allow the user to enter comments to be added to the database for that report. In the ReportViewer’s code-behind file, when the user clicks to save the comment, the code does a Select to get the existing comment for that OvenProgramID and puts that value into a string variable. The code then gets the comments the user just added, adds the comments that the user just added to the existing comments that were pulled from the database, and then updates that column in the table with the comments that now consist of the comments that existed already plus the new comments that the user just added. I did not design this database, so I must retrieve the existing comments, add to them the new comment, and save back to the one column the comments.
The code-behind for the ReportViewer, after the user has clicked to save his or her comments and after the code has selected via C# inline code the comments from the Comments column for that particular OvenProgramID and after the code has added the new comment that the user is saving, the code then runs an update stored procedure that receives the comments in a parameter and receives the OvenProgramID as a parameter. The update stored procedure’s T-SQL code then just does the update. Update [dbo].[TableName] Set [Comments] = @Comments Where [OvenProgramID] = @OvenProgramID.
The comments are not updated though in the table. Eventually there will be a Timeout Expired error, even if I set in the C# code 540 seconds before the error happens.
So I found on the Internet the following query:
SELECT st.text AS [SQL Text],
w.session_id,
w.wait_duration_ms,
w.wait_type, w.resource_address,
w.blocking_session_id,
w.resource_description FROM sys.dm_os_waiting_tasks AS w
INNER JOIN sys.dm_exec_connections AS c ON w.session_id = c.session_id
CROSS APPLY (SELECT * FROM sys.dm_exec_sql_text(c.most_recent_sql_handle))
AS st WHERE w.session_id > 50
AND w.wait_duration_ms > 0
I put this query into a New Query when logged in as Administrator to the SQL Server Mgmt Studio on that Windows Server 2003 machine.
Just after clicking the button via the GUI of the web app to save a new comment, I clicked to run that query and see the following 2 results:
SQL Text = the CREATE Procedure et cetera T-SQL that is the stored procedure that fills the SqlDataReader of the Update report file Session_ID = 71 Wait_duration_ms = 1813 Wait_type = ASYNC_NETWORK_IO Blocking_session_id = NULL Resource_description = External ExternalResource=ASYNC_NETWORK_IO
SQL Text = the T-SQL for the stored procedure that updates the Comments column of a table Session_ID = 76 Wait_duration_ms = 3797 Wait_type = LCK_M_X Blocking_session_id = 71 Resource_description = keylock hobtid=72057594060931072 dbid=6 id= lock7767680 mode=S associatedObjectId=72057594060931072
Why is this first result that is the stored procedure that has run already, that already has filled the SqlDataReader in the Update report that has finished loading and has finished its ReportEnd event, holding up the second result that is the update stored procedure? The first stored procedure has finished already!
When I run on my Win 7 PC the web app from within Visual Studio 2010 (using localhost), SOMETIMES there is no problem and the update stored proc runs and updates the column in the table but SOMETIMES the update stored proc does not run and eventually the Timeout Expired error happens in the web app’s C# CATCH and I see the two results to the query that queries sys.dm_os_waiting_tasks that I provided above.
It seems that when I run the web app on the Win 7 PC putting into a browser the URL to use the IIS 6 on that same Windows Server 2003 machine that has the web app’s files, the update stored proc never runs successfully because it is being blocked by that stored proc that has finished running already to load successfully the Update report.
I am at a loss. I have read that the ASYNC_NETWORK_IO wait type COULD MEAN POSSIBLY that there is a network bottle neck BUT that it could mean that there is a problem with the C# code in the app and how it retrieves/handles the data returned by the stored proc that fills the SqlDataReader of the Update report. Yet the code in the app retrieves successfully the data and the Update report is loaded successfully into the WebViewer control of the ReportViewer.aspx!
If there is some kind of a “network bottleneck” issue, how can I figure out what it is? It would seem that there is no problem with the C# code as it gets the data and the Update report is loaded with no problems.
I verified that when a fellow employee tries to save a comment from her machine, she gets the same problem.
If anybody has any ideas, please let me know.
Please let me know if you have any questions.
I appreciate any interest or questions or advice if anybody has the time and interest to respond to this thread.
Again, I apologize for my lack of experience. I am used to writing stored procs and functions and views but this is the first time that I am trying to use the ActivityMonitor in SQL Server 2008 and these performance and optimization queries and such. Also, I apologize for my lack of experience in the realm of networking.
Thanks very much in advance for your consideration.
Hviezdoslav

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Alexander Suprun SSCertifiable Points: 6233 More actions · Answer 1

Are you sure that at the time when you run update stored procedure your SqlDataReader is already closed? Can you prove it?

Alex Suprun

RBarryYoung SSC Guru Points: 143329 More actions · Answer 2

What is probably happening is that the SQL Server session and connection associated with your SqlDataReader are not being disposed of properly.

Here is where I think you might be going wrong:

mwitt 96024 (9/7/2012)
...
In the Update file’s ReportEnd event, that SqlDataReader is closed and made null if it is not already null.
...

(emphasis mine)

You always need to Close() the SqlDataReader before setting it to null or letting it go out of scope. Setting the SqlDataReader to null by itself,, probably does nothing as long as the Connection and Command objects are still around. And worse, if it's already null, you cannot Close() it.

So don't do that. Always close it first. And add exception handling around the Close() and check to make sure that the Close() worked. If necessary, Cancel() the SqlCommand object first.

and if that still doesn't work, then I would try closing the Connection, setting that to null, and then make a new one.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

mwitt 96024 SSC-Addicted Points: 404 More actions · Answer 3

Hi Alexander Suprum and RBarry Young,

I thank you both for your replies.

In the code behind for the Update report file in the overloaded constructor, there is the following line of code:

mySqlDataReader = OvenProgramDataByProgramID_returningSqlDataReader(ovenProgramID);

This function OvenProgramDataByProgramID_returningSqlDataReader is in the same code behind of the Update report and its code is as follows:

public static SqlDataReader OvenProgramDataByProgramID_returnSqlDataReader(int ovenProgramID)

{

SqlDataReader sqlRead = null;

try

{

SqlParameter[] theSqlParams = new SqlParameter[1];

theSqlParams[0] = new SqlParameter("@OvenProgramID", SqlDbType.Int);

theSqlParams[0].Value = ovenProgramID;

mySqlConnection = new SqlConnection(connString1);

sqlRead = SqlHelper.ExecuteReader(mySqlConnection, CommandType.StoredProcedure, "ap_OvenProgramDataByOvenProgramID", theSqlParams);

}

catch (Exception theErrorThatHappened)

{

if (sqlRead != null)

{

sqlRead.Close();

}

string stringError;

stringError = theErrorThatHappened.ToString();

}

return sqlRead;

}

The ReportEnd event of the Update report has the following code and has had the following code before I created this thread:

private void UpdateReport_PC6000_ReportEnd(object sender, EventArgs e)

{

if (BatchDataSubReport != null)

{

BatchDataSubReport.Dispose();

BatchDataSubReport = null;

}

if (mySqlDataReader != null)

{

mySqlDataReader.Close();

mySqlDataReader = null;

}

if (theSqlConnection != null)

theSqlConnection.Close();

if (mySqlConnection != null)

mySqlConnection.Close();

}

So the ReportEnd event happens before the Update report is loaded into the WebViewer of the ReportViewer.aspx file and when debugging I see that the code in the ReportEnd event runs.

Reading your replies though, I made some changes.

I added the following line at the top of the Update report’s code behind file:

private static SqlDataReader sqlReaderForFunction;

I then changed the code so that now the Update report’s code behind has the following function:

public static SqlDataReader OvenProgramDataByProgramID_returnSqlDataReader(int ovenProgramID)

{

sqlReaderForFunction = null;

try

{

SqlParameter[] theSqlParams = new SqlParameter[1];

theSqlParams[0] = new SqlParameter("@OvenProgramID", SqlDbType.Int);

theSqlParams[0].Value = ovenProgramID;

mySqlConnection = new SqlConnection(connString1);

sqlReaderForFunction = SqlHelper.ExecuteReader(mySqlConnection, CommandType.StoredProcedure, "ap_OvenProgramDataByOvenProgramID", theSqlParams);

}

catch (Exception theErrorThatHappened)

{

if (sqlReaderForFunction != null)

{

sqlReaderForFunction.Close();

}

string stringError;

stringError = theErrorThatHappened.ToString();

}

return sqlReaderForFunction;

}

I changed the code in the ReportEnd event of the Update report’s code behind to the following by adding an IF for this new sqlReaderForFunction:

if (BatchDataSubReport != null)

{

BatchDataSubReport.Dispose();

BatchDataSubReport = null;

}

if (mySqlDataReader != null)

{

mySqlDataReader.Close();

mySqlDataReader = null;

}

if (theSqlConnection != null)

theSqlConnection.Close();

if (mySqlConnection != null)

mySqlConnection.Close();

if (sqlReaderForFunction != null)

{

sqlReaderForFunction.Close();

sqlReaderForFunction = null;

}

So after I made the aforementioned changes, and when I am debugging and loading the report into the ReportViewer’s WebViewer control before the user has the ability to update the comment from the ReportViewer after the Update report has loaded, I put breakpoints in the ReportEnd event and the following happens:

#1 The code goes inside each IF

#2 The code goes inside the IF for mySqlDataReader and the code then closes it and then the code makes it null

#3 The code goes inside the IF about theSqlConnection and then the code closes it

#4 The code goes inside the IF for mySqlConnection and then the code closes it

#5 The code goes inside the IF for sqlReaderForFunction and then the code closes it and then the code makes it null

This past Friday when I posted this thread, the ReportEnd had this same code (except for the IF about the new sqlReaderForFunction that I added today) and that ReportEnd event fired when the ReportViewer file for the Update report loads that Update report. So the IF statements for the SqlDataReader do close and then make null the SqlDataReader and then the IF statements for the SqlConnections do close those connections.

Still though with my changes today I get the Timeout Expired error and that query that selects from db_os_waiting_tasks and joins with dm_exec_connections shows that the stored procedure that fills the mySqlDataReader in the Update report’s overloaded constructor is waiting with ASYNC_NETWORK_IO and that the stored procedure to update the Comments column is being blocked by the stored proc that fills the mySqlDataReader in the Update report’s constructor.

In the ReportViewer’s code behind, after the user clicks to update the Comment column in the table with his or her comment that he or she just entered, I tried when I select the existing string in the Comment column using WITH (NOLOCK) after the “from dbo.Ovenprogram” but this does not cause the error to not happen always. Yet sometimes I do not get the Timeout Expired error with or without the WITH (NOLOCK) in that Select. So using WITH (NOLOCK) in that Select does not solve always the problem. Sometimes though the Timeout Expired error does not happen and the update stored procedure in ReportViewer’s code behind runs fine and is not blocked by the stored proc that fills the mySqlDataReader in the Update report’s constructor. Late Friday afternoon, this past Friday as today is Monday 10-SEP-2012, I successfully saved a comment to the Update report after adding again the WITH (NOLOCK) yet today WITH or WITHOUT the WITH (NOLOCK) in that Select it is not possible for me now to save a comment for that same Update report. So sometimes the update stored proc is not blocked by that other stored proc and the comment is saved/updated while other times the Timeout Expired error happens and that Update report’s stored proc blocks the update stored proc of the Update report’s ReportViewer file.

Wait a second. I continued debugging after the ReportEnd event of the Update report’s code behind and noticed something.

Okay, the user is on a web page named Options_TwoListboxesOnlyOneDate.aspx. Here the user checks or unchecks checkboxes concerning what probes he or she wants to graph, whether or not he wants to see the probe calibration comments, et cetera. On this Options_TwoListboxesOnlyOneDate.aspx, the user must choose a bolded date from the calendar to see what oven controls had cook programs run that day, then when the user clicks on whichever oven control he wants a report a different listbox shows all of the cook programs that were run on that day for that selected oven control, and then the user clicks to view the report to redirect to the ReportViewer.aspx file and its code behind loads the Update report into its WebViewer control. The program then goes into the Update report’s code behind at this point. The programs goes through the Update report’s code and goes through the ReportEnd event of the Update report’s code behind.

Now the Update report has loaded into the ReportViewer web page.

When the user clicks to save his or her comment to the existing string of comments in the Comments column of a table, I noticed that the program goes back into the constructor of the Update report and goes through the line of code “mySqlDataReader = OvenProgramDataByProgramID_returnSqlDataReader(ovenProgramID);” but the program does NOT go through the Update report’s ReportEnd event again. I guess after the user clicks on ReportViewer.aspx to save the comment when the Update report is already loaded into the ReportViewer file, the post back causes the program to go back into the constructor of the Update report file through that line of code “mySqlDataReader = OvenProgramDataByProgramID_returnSqlDataReader(ovenProgramID);” but then the program does not go through the Update report’s other events and methods like FetchData and ReportEnd events but rather leaves the Update report’s constructor having gone through it, then goes into the Dispose of the Update report’s Designer.cs file, then the program goes back to the ReportViewer’s code behind after it has done its code “WebViewer.Report = new UpdateReport_PC6000(OvenProgramID, options, probes, GraphSizeFromDropDown), and then the program goes into the Master page’s code behind, and then while debugging the program goes into a new tab in Visual Studio 2010 that is named Disassembly.

Yet sometimes the update comment is saved successfully with no problem even though after clicking on the Update report’s ReportViewer file to save a comment it seems that the postback in its ReportViewer file causes the program to go again into just the constructor of the Update report.

A former employee created this web app. I created in it these ActiveReports 6 code-based reports and I was told to create the reports in the web app just like they exist and work in a big Windows application.

So in this web app, there is a Complete report that has exactly the same code as the Update report but the Complete report is loaded into a DIFFERENT ReportViewer file that is named differently because the Complete report does not allow the user to add a comment. So the web app’s Complete report itself is exactly the same as the Update report (in their code-behind files) but the Complete report has a different ReportViewer file that is named differently because the user only uses the Update report when he or she wants to add a comment. The complete report loads without any problem. Well heck, the Update report loads without any problem too.

Anyway, it seems that when the program is in the ReportViewer file for the Update report and instantiates a new Update report to load it into the ReportViewer file for the Update report and the program goes into the Update report for the first time, the ReportEnd event of the Update report closes the SqlDataReader and then makes it null AND ALSO closes the SqlConnections.

I am confused about this problem, especially because sometimes the update of the comments works with no problem while other times this Timeout Expired exception occurs because it seems the stored proc that fills the SqlDataReader in the constructor of the Update report file is blocking the update stored procedure that is used from the Update report’s ReportViewer file.

I thank you both though very much for your replies. It seems that the SqlDataReaders and SqlConnections are indeed closed in the Update report’s ReportEnd event when the Update report is loaded into its ReportViewer file.

Hviezdoslav

Monday

10-SEP-2012

mwitt 96024 SSC-Addicted Points: 404 More actions · Answer 4

By the way, here is the stored proc that is used in the constructor of the Update report. I did not write it. It was written by someone who used to work here. It is the same stored procedure that is used in the Update report of the Windows application.

USE [PowisData]

GO

/****** Object: StoredProcedure [dbo].[ap_OvenProgramDataByOvenProgramID] Script Date: 09/10/2012 12:39:37 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER Procedure [dbo].[ap_OvenProgramDataByOvenProgramID]

@OvenProgramID int

/*************************************************************************************/

-- 11/14/2004 JohnP. - returns all data for a given OvenProgramStepID

--

/*************************************************************************************/

As Begin -- Procedure

Set NoCount On

Declare @Return int

Select OP.OvenProgramID,OP.OvenID,O.OvenName,OP.OvenProgramID, OP.OvenProgramStart,

OP.ProbeCount, OP.RunTime,OPS.OvenProgramStepID, OPS.OvenProgramStepName, OPD.OvenProgramDataID,

OPD.StageRunTime,

Case When StageNumber = '1' And StageRunTime = 0 Then 'START' Else StageNumber End as StageNumber,

OPD.OvenStageTypeID, OST.OvenStageTypeDescription,OPD.DryBulbSetPoint, OPD.WetBulbSetPoint, OPD.InternalSetPoint,

OPD.RelativeHumiditySetPoint, OP.ControlType,

Case When OPD.PowerFailure = 1 Then -999 Else OPD.DryBulbValue End as DryBulbValue,

Case When OPD.PowerFailure = 1 Then -999 Else OPD.WetBulbValue End as WetBulbValue,

Case When OPD.PowerFailure = 1 Then -999 Else OPD.InternalValue End as InternalValue,

Case When OPD.PowerFailure = 1 Then -999 Else OPD.RelativeHumidityValue End as RelativeHumidityValue,

Case When OPD.PowerFailure = 1 Then -999 Else OPD.ProbeData01 End as ProbeData01,

Case When OPD.PowerFailure = 1 Then -999 Else OPD.ProbeData02 End as ProbeData02,

Case When OPD.PowerFailure = 1 Then -999 Else OPD.ProbeData03 End as ProbeData03,

Case When OPD.PowerFailure = 1 Then -999 Else OPD.ProbeData04 End as ProbeData04,

Case When OPD.PowerFailure = 1 Then -999 Else OPD.ProbeData05 End as ProbeData05,

Case When OPD.PowerFailure = 1 Then -999 Else OPD.ProbeData06 End as ProbeData06,

Case When OPD.PowerFailure = 1 Then -999 Else OPD.ProbeData07 End as ProbeData07,

Case When OPD.PowerFailure = 1 Then -999 Else OPD.ProbeData08 End as ProbeData08,

Case When OPD.PowerFailure = 1 Then -999 Else OPD.ProbeData09 End as ProbeData09,

Case When OPD.PowerFailure = 1 Then -999 Else OPD.ProbeData10 End as ProbeData10,

Case When OPD.PowerFailure = 1 Then -999 Else OPD.ProbeData11 End as ProbeData11,

Case When OPD.PowerFailure = 1 Then -999 Else OPD.ProbeData12 End as ProbeData12,

Case When OPD.PowerFailure = 1 Then -999 Else OPD.ProbeData13 End as ProbeData13,

Case When OPD.PowerFailure = 1 Then -999 Else OPD.ProbeData14 End as ProbeData14,

OP.Comments,OPD.Accelerated, OPD.PowerFailure, OPD.Disabled

From dbo.OvenProgram OP

Inner Join dbo.OvenProgramStep OPS

On OP.OvenProgramID = OPS.OvenProgramID

Inner Join dbo.OvenProgramData OPD

On OPS.OvenProgramStepID = OPD.OvenProgramStepID

Inner Join dbo.Oven O

On OP.OvenID = O.OvenID

Inner Join dbo.OvenStageType OST

On OST.OvenStageTypeID = OPD.OvenStageTypeID

Where OP.OvenProgramID = @OvenProgramID

Order By OP.OvenProgramStart, O.OvenName, OPD.StageRunTime, OPD.OvenProgramDataID

Select @Return = @@Error

ProcedureExit:

Return @Return

ErrorHandler:

Goto ProcedureExit

End -- Procedure

So I am not sure if this stored procedure is inefficient because it's Select statement is in the Recent Expensive Queries section and I am not experienced enough personally to modify this stored procedure to make it more efficient. Like I said though, sometimes this stored proc does not block the update stored procedure while other times it does. Also, the Complete report opens with no problem and it uses in its constructor this same stored proc but the Complete report's ReportViewer file does not allow the user to update comments.

Hviezdoslav

Monday

10-SEP-2012

mwitt 96024 SSC-Addicted Points: 404 More actions · Answer 5

Today I have been trying unsuccessfully to save a comment for the Update report for the date 04-SEP-2012 for Oven 5's cook program but I am getting today that Timeout Expired error and that Update report's stored proc is blocking the update stored procedure in the Update report's ReportViewer code behind. Today I was connected, as I am usually, to the database on the Windows Server 2003 machine that is an older machine.

So just now I restored the database to a different machine that has Windows Server 2008 and that is much beefier than the ol' Windows Server 2003 machine.

I connected the web app's code where applicable to this new Windows Server 2008 machine.

I ran the application from within VS 2010 connected to this new Windows Server 2008 machine. I tried to add comments to the Update report for the 04-SEP-2012 for Oven 5's cook program and it worked! This is the same cook program for which I'd been trying to update the comments in its Update report all day when connected to the old Win Server 2003 machine with no success today.

But then, still connected to the Windows Server 2008 machine, I tried to update the comments for the Update report for the 04-SEP-2012 cook program for a different Oven 2 and again I get the Timeout Expired error in the web app and again that Waiting Query showed that the Update report's stored procedure that selects data is blocking the update stored procedure.

So this problem occurs sometimes on a Windows Server 2008 machine just like it happens sometimes on a different Windows Server 2003 machine.

Hviezdoslav Monday 10-SEP-2012

mwitt 96024 SSC-Addicted Points: 404 More actions · Answer 6

When I change the code to use WITH (NOLOCK) for the stored procedure in the constructor of the Update report to the following, I do not seem to get the Timeout Expired error and that stored proc in the constructor of the Update rpt does not seem to have the ASYNC_NETWORK_IO that is blocking the Update stored proc in the Update report's ReportViewer file:

ALTER Procedure [dbo].[ap_OvenProgramDataByOvenProgramID_3]

@OvenProgramID int

/*************************************************************************************/

-- 11/14/2004 JohnP. - returns all data for a given OvenProgramStepID

--

/*************************************************************************************/

As Begin -- Procedure

Set NoCount On

--Declare @Return int