Need to debug/trace ADO's Fill method

[moondaddy]

I have a website where cataloge pages are populated by calling a stored
procedure on sql server. I use the sql data adapter's fill method to call
this stored procedure and fill the dataset. about 6 to 15 times a day the
server hangs and times out when this fill method is called. The SP is
simple and uses very little resources (as tested using client statistics in
query analyzer). Here's data from my error log which includes the
exception:

Log ID: 722
Mod: da_Catalog,
Proc: Get_ProductListingGrid,
ID=143,
CustID=0
ex.Message=Timeout expired. The timeout period elapsed prior to completion
of the operation or the server is not responding.
ex.StackTrace= at
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior,
RunBehavior runBehavior, Boolean returnStream)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
behavior)
at
System.Data.SqlClient.SqlCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior
behavior)
at System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32
startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32
startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
at Charmpix.da_Catalog.Get_ProductListingGrid(Int32 ID, Int32 CustID)
Time Stamp: 10/19/2004 11:18:37 PM


The application is being hosted in a shared environment, but the ISP is very
good and has extremely knowledgeable technicians helping on the backend.
They cant find any issues with their servers and are asking for more
information from me.

Is there anyway I can log a stack trace starting from the point where Fill
Method is called so we can see exactly where the code is hanging? We would
like to know if its hanging on IIS where the fill method is, or is it
hanging on the sql server then the SP is trying to execute.

Can anyone help me trouble shoot this?

Thanks!

 

[Marina]

3 things I can think of:

1) Some sort of deadlocking - do you have transactions or something else
that might cause a deadlock
2) Need to increase command timeout as the query cannot complete in the time
specified
3) The SQL Server is busy doing other work (perhaps from someone else's app)
and just doesn't have enough resources to complete your request.

I don't think you are going to be able to get anywhere else as far as
debugging further into this - fill is a framework method, you cannot debug
into it.

 

[Ken Tucker [MVP]]

Hi,

In addition to Marina's comments Here is a link on How to debug a stored
procedure.

http://support.microsoft.com/default.aspx?scid=kb;en-us;316549

Ken
------------------------
I have a website where cataloge pages are populated by calling a stored
procedure on sql server. I use the sql data adapter's fill method to call
this stored procedure and fill the dataset. about 6 to 15 times a day the
server hangs and times out when this fill method is called. The SP is
simple and uses very little resources (as tested using client statistics in
query analyzer). Here's data from my error log which includes the
exception:

Log ID: 722
Mod: da_Catalog,
Proc: Get_ProductListingGrid,
ID=143,
CustID=0
ex.Message=Timeout expired. The timeout period elapsed prior to completion
of the operation or the server is not responding.
ex.StackTrace= at
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior,
RunBehavior runBehavior, Boolean returnStream)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
behavior)
at
System.Data.SqlClient.SqlCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior
behavior)
at System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32
startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32
startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
at Charmpix.da_Catalog.Get_ProductListingGrid(Int32 ID, Int32 CustID)
Time Stamp: 10/19/2004 11:18:37 PM


The application is being hosted in a shared environment, but the ISP is very
good and has extremely knowledgeable technicians helping on the backend.
They cant find any issues with their servers and are asking for more
information from me.

Is there anyway I can log a stack trace starting from the point where Fill
Method is called so we can see exactly where the code is hanging? We would
like to know if its hanging on IIS where the fill method is, or is it
hanging on the sql server then the SP is trying to execute.

Can anyone help me trouble shoot this?

Thanks!

 

[Marina]

Regardless of how simple it is, if the sql server is being completely
consumed by some other long running query, then it won't be able to execute
the simplest of queries.

I agree that it is unlikely that you will be able to sucessfully debug
anything on a server being hosted elsewhere.

Perhaps you could ask your ISP to move the database you are using to another
SQL Server to see if the problem persists.

 

[moondaddy]

Thanks to both of your replies and here's my response.
1) deadlock is not likely as I'm not using temp tables in the SP and we're
not performing transactions. Just simple a select statement.

2) The command timeout is already pretty long. The fact that its timing out
means there's already an issue happening. Also, by waiting so long to
timeout (refering to the current timeout period) the user browsing our site
will have become disgruntled and probably gone elsewhere. Therefore, its
really important to resolve the real problem.

3) SQL server could be real busy, but once again, this SP is very simple and
executes very quickly. Its possible the server is too busy, but the ISP has
been monitoring it and doesn't see anything that suggests its too busy.

FYI: the ISP is MaximumASP and I think you will find them a very credible
service provider that's extremely competent on the back end.

As for debugging the stored procedure:
A) I could hit this SP 200 times and it would fire perfectly, then later on
in the day it will hang a few times with in a span of a few minutes or few
hours. Its very difficult to catch.
B) The server's I'm using are remotely hosted at MaximumASP and from what I
know, debugging would be difficult or impossible to do in that scenario. I
don't have this problem on my local dev network.

Any more feedback would be greatly appreciated.

Thanks Again!

 

[moondaddy]

Thanks for all the feedback. I already put in a request to move my db to a
different server. Wish me luck...