Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

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 Expand / Collapse
Author
Message
Posted Friday, September 7, 2012 1:05 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 6, 2014 11:30 AM
Points: 6, Visits: 28
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
Post #1356198
Posted Friday, September 7, 2012 2:19 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 3:05 PM
Points: 193, Visits: 945
Are you sure that at the time when you run update stored procedure your SqlDataReader is already closed? Can you prove it?


Alex Suprun
Post #1356238
Posted Friday, September 7, 2012 2:29 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,902, Visits: 9,480
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.


-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #1356246
Posted Monday, September 10, 2012 10:34 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 6, 2014 11:30 AM
Points: 6, Visits: 28
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
Post #1356884
Posted Monday, September 10, 2012 11:47 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 6, 2014 11:30 AM
Points: 6, Visits: 28
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
Post #1356923
Posted Monday, September 10, 2012 1:43 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 6, 2014 11:30 AM
Points: 6, Visits: 28
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
Post #1356987
Posted Tuesday, September 11, 2012 9:18 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 6, 2014 11:30 AM
Points: 6, Visits: 28
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

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 WITH (NOLOCK)
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


I have read that I can get "dirty reads" with this approach. This approach seems to work though. I would prefer of course to refrain from using the WITH (NOLOCK) and find the solution to why the Update report's constructor's Select stored procedure gets the ASYNC_NETWORK_IO wait and blocks the Update stored proc.

I guess that without the WITH (NOLOCK) there is some locking that is not being unlocked and that is causing the ASYNC_NETWORK_IO wait to block the Update stored proc from running.

Hviezdoslav
Tuesday
11-SEP-2012
Post #1357510
Posted Friday, September 14, 2012 12:35 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 6, 2014 11:30 AM
Points: 6, Visits: 28
Alexander Suprum and RBarry Young,

You were both right and I was wrong.

I thank you though for your questions helped me to look into this issue and recognize what I was doing wrong.

When the Update rpt is loaded into the ReportViewer's WebViewer control, the Update report's constructor has a line of code that calls a function to run the Select stored proc and fill the SqlDataReader for the Update report AND that I verified when debugging that the SqlDataReader and SqlConnection are closed in the Update report's ReportEnd event.

I think in one of my prior posts I mentioned that after updating the comment in the ReportViewer file that has in its WebViewer the Update report, the program on post back went back into the constructor for the Update report and there the Select stored procedure filled the SqlDataReader with the data BUT that none of the other events fired in the Update report after the post back.

I kept thinking about how the two of you asked if I am ABSOLUTELY SURE that the SqlDataReader and SqlConnection are being closed. I was sure that they were being closed when the Update report is being loaded into the WebViewer control of the ReportViewer file.

I realized though that after the postback caused by clicking to save the comment on the ReportViewer web page happens, the program goes into the constructor of the Update report again to create a SqlConnection and fill the SqlDataReader BUT the program goes no farther in the Update report and never goes into the ReportEnd event to close SqlDataReader and SqlConnection after the post back on the ReportViewer file.

I needed in the Page_Load of the ReportViewer file TO CHECK IF IT IS A POST BACK so as to REFRAIN from causing the program after post back to go back into the constructor of the Update report to create the SqlConnection and fill the SqlDataReader that would not get closed after post back in the ReportEnd of the Update report.

So now in the ReportViewer file, if it is NOT a postback I will continue to pass some information via the URL to the constructor of the Update report to allow the Update report's constructor to get the values in the QueryString of the URL and in the constructor to fill the SqlDataReader and to go through all of the events and methods in the Update report including the ReportEnd event and finally to bring the Update report into the WebViewer of the ReportViewer file. So now in the ReportViewer file, if it is NOT a post back the code in the Page_Load of ReportViewer file DOES NOT run the code "WebViewer.Report = new UpdateReport(OvenProgramID, options, probes, GraphSizeFromDropDown)" and therefore the program does not after a post back go again into the constructor of the Update report.

I hope that I am communicating successfully what I am trying to convey.

As it turns out, I do NOT need to use WITH (NOLOCK) in the Select stored procedure since it was my C# code in the Page_Load of the ReportViewer file that was the culprit and that was causing after the post back the program to go into the constructor again in the Update report to connect again to SQL Server and fill again the SqlDataReader since after post back there is no need to go again to the Update report and because after post back in the Update report the ReportEnd does not run again to close the SqlConnection and SqlDataReader. After post back in ReportViewer after the user updates the Comment column of the table in the database, the code just redirects back to the Options web page where the user had chose the date on the calendar, chosen what probes to graph, et cetera and then clicked to view the Update report.

So I thank you both for stressing to me that I must be certain that I am closing the SqlDataReader and SqlConnection in the Update report file because I kept thinking about it and when continuing to debug AFTER clicking to save the comment in the ReportViewer file that shows the Update report in its WebViewer I saw how I incorrectly was allow the Update report to be loaded again into the WebViewer and how I need to check in the Page_Load of ReportViewer file whether or not it is a post back. You both were right and it was my fault how I was not checking for post back.

Hviezdoslav
Friday 14-SEP-2012
Post #1359563
Posted Friday, September 14, 2012 1:06 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,902, Visits: 9,480
Thanks for the follow-up and the candor Hviezdoslav. Glad it worked out for you.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #1359590
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse