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(Com mandBehavior cmdBehavior,
RunBehavior runBehavior, Boolean returnStream)
at System.Data.SqlClient.SqlCommand.ExecuteReader(Com mandBehavior
behavior)
at
System.Data.SqlClient.SqlCommand.System.Data.IDbCo mmand.ExecuteReader(CommandBehavior
behavior)
at System.Data.Common.DbDataAdapter.FillFromCommand(O bject 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!

--
mo*******@nospam.com

[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.

"moondaddy" <mo*******@nospam.com> wrote in message
news:en**************@TK2MSFTNGP14.phx.gbl...

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(Com mandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
at System.Data.SqlClient.SqlCommand.ExecuteReader(Com mandBehavior
behavior)
at
System.Data.SqlClient.SqlCommand.System.Data.IDbCo mmand.ExecuteReader(Comman
dBehavior behavior)
at System.Data.Common.DbDataAdapter.FillFromCommand(O bject 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!

--
mo*******@nospam.com

[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...b;en-us;316549

Ken
------------------------
"moondaddy" <mo*******@nospam.com> wrote in message
news:en**************@TK2MSFTNGP14.phx.gbl...
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(Com mandBehavior cmdBehavior,
RunBehavior runBehavior, Boolean returnStream)
at System.Data.SqlClient.SqlCommand.ExecuteReader(Com mandBehavior
behavior)
at
System.Data.SqlClient.SqlCommand.System.Data.IDbCo mmand.ExecuteReader(CommandBehavior
behavior)
at System.Data.Common.DbDataAdapter.FillFromCommand(O bject 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!

--
mo*******@nospam.com

[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!

--
mo*******@nospam.com
"Ken Tucker [MVP]" <vb***@bellsouth.net> wrote in message
news:%2****************@tk2msftngp13.phx.gbl...

Hi,

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

http://support.microsoft.com/default...b;en-us;316549

Ken
------------------------
"moondaddy" <mo*******@nospam.com> wrote in message
news:en**************@TK2MSFTNGP14.phx.gbl...
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(Com mandBehavior
cmdBehavior,
RunBehavior runBehavior, Boolean returnStream)
at System.Data.SqlClient.SqlCommand.ExecuteReader(Com mandBehavior
behavior)
at
System.Data.SqlClient.SqlCommand.System.Data.IDbCo mmand.ExecuteReader(CommandBehavior
behavior)
at System.Data.Common.DbDataAdapter.FillFromCommand(O bject 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!

--
mo*******@nospam.com

[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" <mo*******@nospam.com> wrote in message
news:Oe**************@TK2MSFTNGP15.phx.gbl...

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!

--
mo*******@nospam.com
"Ken Tucker [MVP]" <vb***@bellsouth.net> wrote in message
news:%2****************@tk2msftngp13.phx.gbl...

Hi,

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

http://support.microsoft.com/default...b;en-us;316549

Ken
------------------------
"moondaddy" <mo*******@nospam.com> wrote in message
news:en**************@TK2MSFTNGP14.phx.gbl...
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(Com mandBehavior
cmdBehavior,
RunBehavior runBehavior, Boolean returnStream)
at System.Data.SqlClient.SqlCommand.ExecuteReader(Com mandBehavior
behavior)
at
System.Data.SqlClient.SqlCommand.System.Data.IDbCo mmand.ExecuteReader(Comman
dBehavior behavior)
at System.Data.Common.DbDataAdapter.FillFromCommand(O bject 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!

--
mo*******@nospam.com

[moondaddy]

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

--
mo*******@nospam.com
"Marina" <so*****@nospam.com> wrote in message
news:Oy**************@TK2MSFTNGP11.phx.gbl...

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" <mo*******@nospam.com> wrote in message
news:Oe**************@TK2MSFTNGP15.phx.gbl...

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!

--
mo*******@nospam.com
"Ken Tucker [MVP]" <vb***@bellsouth.net> wrote in message
news:%2****************@tk2msftngp13.phx.gbl...

> Hi,
>
> In addition to Marina's comments Here is a link on How to debug a
> stored
> procedure.
>
> http://support.microsoft.com/default...b;en-us;316549
>
> Ken
> ------------------------
> "moondaddy" <mo*******@nospam.com> wrote in message
> news:en**************@TK2MSFTNGP14.phx.gbl...
> 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(Com mandBehavior
> cmdBehavior,
> RunBehavior runBehavior, Boolean returnStream)
> at System.Data.SqlClient.SqlCommand.ExecuteReader(Com mandBehavior
> behavior)
> at
> System.Data.SqlClient.SqlCommand.System.Data.IDbCo mmand.ExecuteReader(Comman
dBehavior > behavior)
> at System.Data.Common.DbDataAdapter.FillFromCommand(O bject 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!
>
>
>
> --
> mo*******@nospam.com
>
>
>