Procedure timeout when executing from the ASP.NET application

[Peter Afonin]

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

 

[Mr. Arnold]

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.

 

[Peter Afonin]

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