Problem with Sql Server

[Guest]

I have a sproc on sql server that I am calling from an asp.net app using the data application block v2 method ExecuteDataSet. The sproc returns data that is bound to a datagrid. The problem is that the command times out when being called through .net but only takes 1-3 sec when run from query analyzer

I've used sql profiler to try to diagnose the problem, here are the results

Command Agent CPU Reads Duratio
------------------------------------------ ------ ----- ------ -------
RptInvRoyalty 126, '2/1/2004', '2/29/2004' SQL QA 562 2187 126
RptInvRoyalty 126, '2/1/2004', '2/29/2004' .net 26578 231648 3053
------------------------------------------ ------ ----- ------ -------

Things I have tried (sa account was used for all tests)

- Executing the command as an sproc by passing the sproc name and the parameters to the executedataset method

- Executing the command using CommandType.Text and passing the same string I use in query analyzer (for example: "dbo.MySp 5, '01/02/2004'")

- Executing the command by creating my own SqlCommand and other objects directly (as opposed to using SqlHelper)

- Executing the command using the oledb data provider

- Increasing both the command timeout and connection timeout to 120 sec for all of the above

- Running the command against a backup db. It works the first couple times but then starts timing out

- Running the command from different web servers

- Running both web server and db server on same machine

- Creating a new sproc with the same tsql code

- Using ExecuteReader and ExecuteNonQuery

- Running it in both c# and vb

- Stopping and starting the db server

- Rebooting the db server

Any help would be appreciated

Thanks
Josh

 

[Sushil Chordia]

There is one difference between the way QA and SqlClient retrieve data, they
have a different default value for arithabort.

Can you try setting arithabort on and off before running your stored
procedure in either QA or your code to see if this is what you are running
into?
"set arithabort on"

--
This posting is provided "AS IS" with no warranties, and confers no rights.

I have a sproc on sql server that I am calling from an asp.net app using

the data application block v2 method ExecuteDataSet. The sproc returns data
that is bound to a datagrid. The problem is that the command times out when
being called through .net but only takes 1-3 sec when run from query
analyzer.

I've used sql profiler to try to diagnose the problem, here are the results:

Command Agent CPU Reads Duration
------------------------------------------ ------ ----- ------ --- -----
RptInvRoyalty 126, '2/1/2004', '2/29/2004' SQL QA 562 2187 1266
RptInvRoyalty 126, '2/1/2004', '2/29/2004' .net 26578 231648 30530
------------------------------------------ ------ ----- ------ --- -----

Things I have tried (sa account was used for all tests):

- Executing the command as an sproc by passing the sproc name and the

parameters to the executedataset method.

- Executing the command using CommandType.Text and passing the same string

I use in query analyzer (for example: "dbo.MySp 5, '01/02/2004'").

- Executing the command by creating my own SqlCommand and other objects

directly (as opposed to using SqlHelper).

 

[Guest]

I tried it from QA both with it on and off with no luc

Thanks anyway
Jos

----- Sushil Chordia wrote: ----

There is one difference between the way QA and SqlClient retrieve data, the
have a different default value for arithabort

Can you try setting arithabort on and off before running your store
procedure in either QA or your code to see if this is what you are runnin
into
"set arithabort on

--
This posting is provided "AS IS" with no warranties, and confers no rights

I have a sproc on sql server that I am calling from an asp.net app usin

the data application block v2 method ExecuteDataSet. The sproc returns dat
that is bound to a datagrid. The problem is that the command times out whe
being called through .net but only takes 1-3 sec when run from quer
analyzer

 

[Guest]

It is very difficult to tell what is going on based on the information provided. There could be many factors. But from looking at your trace events, one thing is obvious that when you try to execute the sproc from the .net code, # of logical reads are so high that it times out. Now question is what is it in your query thats causing it. If I were you, I would try to comment out code in the Sproc line by line and see the effect, if it is a query then, reduce the joins, filters and see the effect. Once you have identified which part of the code has a problem then you could tackle it

HTH
Sach

----- JoshuaLuke wrote: ----

I tried it from QA both with it on and off with no luc

Thanks anyway
Jos

----- Sushil Chordia wrote: ----

There is one difference between the way QA and SqlClient retrieve data, the
have a different default value for arithabort

Can you try setting arithabort on and off before running your store
procedure in either QA or your code to see if this is what you are runnin
into
"set arithabort on

--
This posting is provided "AS IS" with no warranties, and confers no rights

I have a sproc on sql server that I am calling from an asp.net app usin

the data application block v2 method ExecuteDataSet. The sproc returns dat
that is bound to a datagrid. The problem is that the command times out whe
being called through .net but only takes 1-3 sec when run from quer
analyzer