Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > Procedure timeout when executing from the ASP.NET application

Reply
Thread Tools

Procedure timeout when executing from the ASP.NET application

 
 
Peter Afonin
Guest
Posts: n/a
 
      09-01-2010
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



 
Reply With Quote
 
 
 
 
Mr. Arnold
Guest
Posts: n/a
 
      09-01-2010
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.





 
Reply With Quote
 
 
 
 
Peter Afonin
Guest
Posts: n/a
 
      09-02-2010
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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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.
>
>
>
>
>



 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
procedure as argument in procedure AlexWare VHDL 2 10-23-2009 09:14 AM
Horrible performance when executing stored procedure staeri@gmail.com ASP .Net 2 11-08-2006 04:42 PM
'Procedure or function <stored procedure name> has too many arguments specified',,,ARGH! Mike P ASP .Net 0 06-19-2006 01:19 PM
Sample code for Connecting and Executing a stored procedure is SQL server using ASP.NET Peri ASP .Net 1 06-13-2005 04:56 AM
Timeout::timeout and Socket timeout Mark Probert Ruby 1 10-06-2004 09:30 AM



Advertisments