ASPX pages failing when SQL Server busy

[John]

I have a single server running SQL Server 2000 sp3a, and acting as a ASP.NET
webserver (.net 1.1 sp1). W2k sp4. Dual Xeon processor, 2 gigs RAM.

Everything works fine, but sometimes when I execute a particular stored
procedure (on server console using command line, nothing .net), my webapps
will fail intermitently. Custom errors are turned on, so at this stage I
can't give you error codes! But my assumption is that it's a SQL problem,
not a .NET one.

The sproc does lots of joining, aggrigates and sorting, and makes the tempdb
grow from nothing to over 3 gigs. The database it's actually in is around
300mb in size, and doesn't grow that much itself. All db's are in 'simple'
recovery model. The sproc contains no explicit transactions or temp tables.

Can anyone suggest what's going wrong?? I suspect tempdb, if only because of
it's massive growth. Could it be getting too big for the filesystem??? (I
have lots of disc space free, but maybe W2k can't cope with a single file
larger than x?)

Thanks,

John

 

[Miha Markic [MVP C#]]

John,

You might start by catching the errors and logging them to see where the
problem is.

 

[Mark]

Modify the web.config file to allow for asp.net errors to when accessed from
the local web server, but not the clients. Then log into the local web
server and try our your application. Something like:

<customErrors mode="RemoteOnly" /> or similar.

Too many factors involved below to NOT find out what the exact message is.

Also - make the tempdb a static size at 3 GB. Don't let it autogrow. That
will kill performance in a hurry. Also, your connection string should
include a time out setting that is higher than normal.

Good luck.

Mark
www.dovetaildatabases.com

 

[John]

The error from the aspx pages is:

"Error: System.Data.SqlClient.SqlException: Timeout expired. The
timeout period elapsed prior to completion of the operation or the
server is not responding."

The timeout is the default 30 seconds.

This occured on multiple apps, using unrelated databases (but all the
same server, and same instance). They all seemed to exhibit timeout
behaviour, they all stopped responing for 30 seconds +, even though
some of the apps were using different db's so nothing could be locked
(and the big sproc that's running isn't using any explicit
transactions). The problems seem to appear just before the sproc
finished (after about 20 minutes of running).

Help!!!!


John

 

[http://www.visual-basic-data-mining.net/forum]

The stored procedure may be poorly designed. Analyze the execution plan of
the stored procedure to look for joins and statements that you can further
optimize

 

[John]

The error i'm getting is a timeout:

"Error: System.Data.SqlClient.SqlException: Timeout expired. The
timeout period elapsed prior to completion of the operation or the
server is not responding."

The timeout is the default 30 seconds. This occured on multiple apps, using
unrelated databases (but all the
same server, and same instance). They all seemed to exhibit timeout
behaviour, they all stopped responing for 30 seconds +, even though some of
the apps were using different db's so nothing could be locked
(and the big sproc that's running isn't using any explicit transactions).
The problems seem to appear just before the sproc
finished (after about 20 minutes of running).

How can I fix it!?????!!

John

 

[John]

Is it possible that, following a massive update on one production database
(several hundred thousand rows being deleted / inserted), that locks in
tempdb could escalate to table locks?? Would that cause the problems I'm
seeing? (ie the whole instance of SQL Server basically going single-task for
about 2 minutes)

If so, how can I avoid this escalation in tempdb?

Thanks,

John

 

[Guest]

First thing, I would't run the web server on the same machine as SQLServer.
It will kill the consistency of your site's performance.

 

[William \(Bill\) Vaughn]

Sure, TempDb is used quite a bit as a "scratchpad" to hold temporary
(intermediate) products and to sort the data. What kind of hard drives do
you have? Is the database segmented so it's not putting TempDb on the same
spindle as the OS and the other databases. Sure, you might be disk bound. If
you're sharing space with IIS, that could also hurt performance as could the
complexity of your SP. It could be that the query plan does not match the
set of parameters you're using. It could be that your connection pool might
be filling up as inbound customers can't get a connection because the ones
that are in use are still busy. It could be a dozen dozen other things as
well.
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Jeff Dillon]

Sounds like your sql is bad, and possibly forgot a join, causing a cartesian
product. Break apart the code and test, perhaps with SQL Profiler

Jeff