Prev: setting a column value in code for an updatable ViewGrid
Next: Problem with my forms authentication
From: Peter Afonin on 1 Sep 2010 12:22
Hello,

I'm working with ASP.NET 3.5 and SQL Server 2008.

I have a very weird problem. When I execute the procedure from my ASP.NET
application (either from my PC or the Web server), it's timing out. At the
same time when I execute the same procedure with the same parameters from
the SQL Server Management Studio - it executes instantly, so aparently there
is nothing wrong with it.

This applies only to the few procedures, with most of them I don't have any
issues.

To add to this mistery, this problem is intermittent, i.e. occasionally
everything works OK.

It seems that something is happening somewhere in the middle tier between my
application and the SQL Server, but I have no idea what could it be and how
to find the problem.

I would appreciate any comments on this.

Thank you,

Peter



From: Mr. Arnold on 1 Sep 2010 18:09
On 9/1/2010 12:22 PM, Peter Afonin wrote:
> Hello,
>
> I'm working with ASP.NET 3.5 and SQL Server 2008.
>
> I have a very weird problem. When I execute the procedure from my ASP.NET
> application (either from my PC or the Web server), it's timing out. At the
> same time when I execute the same procedure with the same parameters from
> the SQL Server Management Studio - it executes instantly, so aparently there
> is nothing wrong with it.
>
> This applies only to the few procedures, with most of them I don't have any
> issues.
>
> To add to this mistery, this problem is intermittent, i.e. occasionally
> everything works OK.
>
> It seems that something is happening somewhere in the middle tier between my
> application and the SQL Server, but I have no idea what could it be and how
> to find the problem.
>
> I would appreciate any comments on this.
>
> Thank you,
>
> Peter
>
>
>

Is this something you're playing with or is this something at the
enterprise level?

One issue you might be having if lots of users are using the application
or this application is being used with other applications that use SQL
Server heavily is that connections are being left opened by an
application, which will cause timeout issues as connection limits to SQL
Server have been reached.

Any application that wants a new connection under this condition is
going to wait for a connection to be released. And if the wait is too
long, past the set 'Connection Timeout', then the application is going
to throw a timeout condition.

Another issue might be that ADO.NET is not using SQL Server connection
pooling, which is the default setting.

You can go to the SQL Server Manager application, 'Create a new Query'
and run the sp_who or sp_who1 system sprocs, which will give you
information as to how many concurrent connections are opened, and what
userid has the connection open, which should be done at the time of
connection timeout issues with your application. If you see a lot of
userid(s) with connections open, then it may be a sign the connection
pooling is not be used, pooling is not being used right or connections
are not being closed properly by an application.

Lastly, you just might have to set the Connection Timeout to a higher
setting too with your application to avoid the timeout issues on the
connection string.





From: Peter Afonin on 2 Sep 2010 11:14
Thank you.

The application is in testing stage, so the number of connections is not an
issue. All procedures in the production environment don't give us any
problems.

I'd checked all other settings - they are OK.The connection timeout is set
to maximum. Usually the procedure finally works, it just takes about 2-3
minutes to execute, which is not acceptable.

Peter

"Mr. Arnold" <Arnold(a)Arnold.com> wrote in message
news:uAIzoJiSLHA.1184(a)TK2MSFTNGP06.phx.gbl...
> On 9/1/2010 12:22 PM, Peter Afonin wrote:
>> Hello,
>>
>> I'm working with ASP.NET 3.5 and SQL Server 2008.
>>
>> I have a very weird problem. When I execute the procedure from my ASP.NET
>> application (either from my PC or the Web server), it's timing out. At
>> the
>> same time when I execute the same procedure with the same parameters from
>> the SQL Server Management Studio - it executes instantly, so aparently
>> there
>> is nothing wrong with it.
>>
>> This applies only to the few procedures, with most of them I don't have
>> any
>> issues.
>>
>> To add to this mistery, this problem is intermittent, i.e. occasionally
>> everything works OK.
>>
>> It seems that something is happening somewhere in the middle tier between
>> my
>> application and the SQL Server, but I have no idea what could it be and
>> how
>> to find the problem.
>>
>> I would appreciate any comments on this.
>>
>> Thank you,
>>
>> Peter
>>
>>
>>
>
> Is this something you're playing with or is this something at the
> enterprise level?
>
> One issue you might be having if lots of users are using the application
> or this application is being used with other applications that use SQL
> Server heavily is that connections are being left opened by an
> application, which will cause timeout issues as connection limits to SQL
> Server have been reached.
>
> Any application that wants a new connection under this condition is going
> to wait for a connection to be released. And if the wait is too long, past
> the set 'Connection Timeout', then the application is going to throw a
> timeout condition.
>
> Another issue might be that ADO.NET is not using SQL Server connection
> pooling, which is the default setting.
>
> You can go to the SQL Server Manager application, 'Create a new Query' and
> run the sp_who or sp_who1 system sprocs, which will give you information
> as to how many concurrent connections are opened, and what userid has the
> connection open, which should be done at the time of connection timeout
> issues with your application. If you see a lot of userid(s) with
> connections open, then it may be a sign the connection pooling is not be
> used, pooling is not being used right or connections are not being closed
> properly by an application.
>
> Lastly, you just might have to set the Connection Timeout to a higher
> setting too with your application to avoid the timeout issues on the
> connection string.
>
>
>
>
>


 | 
Pages: 1
Prev: setting a column value in code for an updatable ViewGrid
Next: Problem with my forms authentication