ASP.NET Application Gets Frequent SQL Timeout Exceptions

[dm1608]

Hello, I have an ASP.NET 2.0 application that is running under Windows 2003
with SQL 2005/SP2. The server is fully patched with all updates, Windows,
..NET, etc.

I have this ASP.NET application that I host that basically has a webform for
the user to plug in a search criteria, hit submit, and one or more SP are
run against the SQL database and then it returns a gridview of data.

The problem is that periodically, users experience a huge slowness with the
server, however the server CPU and memory look fine, best I can tell. There
are typically no more than 1-3 users hitting the application concurrently.

I have ELMAH installed and I typically will get a SQL Timeout Exception for
ADO.NET. I've changed the timeouts for IIS and SQL Connection to like 5
minutes, but this doesn't really appear to be the issue.

The kicker is that doing an IIS and/or SQL restart doesn't fix the problem.
Only have a restart the server does the query actually work.

What makes this even more interesting is that the result set only returns 2
rows by running a query for the past 24 hours, however if I run the SP
manually, it only takes a few seconds to run in SQL Management Studio.

This problem seems to surface every so often out of no where. Sometimes the
application runs weeks without any issues. Other times I get 5-8 phone
calls a day at different times that its slow (timing out) and, again,
nothing is going on.

*** RANT MODE ON ***
I'm really getting tired of this issue and I'm getting tired of
non-technical folks always saying, "Can't you just restart the server" as if
its an on/off switch and not a server. It is very frustrating and having
users that run XP/Vista, etc, that remote into other servers for running
non-enterprise applications (I.E., applications that are written in VB6 or
something else that run as a console application) and require full admin
access to the server to use the program. Then every time there is any
performance problem (or perceived problem) they call me and I'm suppose to
miraculous fix their issues; knowing good and well that they do what the
please on the box and I can't stop them... Yes, they use Explorer all the
freakin' time and accidently drag/drop folders to other folders and treat
the server environment like its a glorified desktop workstation. ARRGHHHHH

Microsoft really did everyone a disservice by merging their workstation and
server code and GUI. At the very least, they should have made the GUI less
intutive to make folks that have a laptop and XP/Vista not think they know
how to operate a server! I'm sick of it! I'd use Ubuntu in a heartbeat
if I could run the applications on it and make it more difficult for normal
users to maneuver around the system and force them to use command line!
**** RANT MODE OFF ****

Any hep regarding my issue would be greatly appreciated and if you feel like
commenting on my rant, please do so! ;-)

 

[Cowboy \(Gregory A. Beamer\)]

I disagree with your rant. While there are many things I will agree with
rants on, this is most likely your ignorance rather than a Microsoft
problem. Let's try to see if we can figure out where the issue is rather
than ranting on:

1. Is SQL Server on the same server as the website? If not, you might want
to check your connection libraries on the server side. With some SQL Server
installs, you do not have the best libs enabled for websites.

2. What does your dataa access code look like?

3. Are you using a single connection shared on the entire site? If so, stop
it.

4. Are you trying to chain multiple calls on a single connection or
opening/closing? If so, you should consider either chaining the calls
together as a single message or using the standard Open(), Dispose()
pattern.

5. Are you using the Dispose() method on the connection object? If not, you
should.

You really need to dissect the code and determine where the block is. I have
had ASPX/SQL sites that served thousands of users an hour, with dozens to
hundreds at the same time, without an issue.

 

[dm1608]

Thanks for the response, Coyboy.

However, I'm not ignorant in Microsoft or .NET technologies. I've been
using NT/Windows since 3.5 days and .NET since 2000 and SQL since 6.5. And
yes, I've worked with mainframes for the past 22 years and still have some
for our core product offering. I'd imagine you've never worked in a real
data center where you have a tone of server-based products and mainframes
where a lot of the windows applications were written in VB6 and/or COM and
must run as a foreground application on the server. Which, in return,
typically requires administrative permission. And, requires support folks
(who typically don't even know how to use Windows) are responsible for
product implementation and support. I swear some of these programs are
mainframe COBOL programmers that all of a sudden started writing for the PC.
It shows.

Most of the applications I run, I have to support from an administrative
perspective and I have no control over how it is written, deployed, or used.
Yes, I point out the flaws in every one of these applications that I have to
support. They're awful and many times have memory leaks, crash, or are
simply written for one customer yet deployed to support many, many customers
where it trips over itself. Far from perfect environment.

I do think that if Microsoft will stop makign the GUI so darn easy for any
wannabe administrator, then perhaps I would not have to deal with ignorant
support folks that think since they use "Windows" on their laptop and the
server environment looks the same, that they know how to operate it. I'd be
perfectly fine--if all our applications would work--to load up server core
and drop the GUI. But that isn't going to happen in my short life-time.

To get back to my issue at hand -- I cannot look at the code. I do not have
access or a way to look at it. I have no idea how they're using Dispose()
or Open() or anything else. I suppose I could maybe use reflector or
something, but that isn't going to fix my problem and it'll just take me
hours and still not be able to fix it.

There is a single connection defined in the web.config file that is only
used for the ASP.NET application. The foreground application, that is a
VB6 application that basically reads data from mainframe overnetwork does a
bunch of INSERTs. It, however, uses a totally different connection.

Typically when I notice the problem, I do not see any SQL activity or any
blocking (sp_who2) and I do not see any real CPU activity either.

I have run SQL Trace and don't see the SQL SP call until a few seconds
before the ASP.NET page times out.

My thoughts at this time is that IIS/ASP.NET is somehow not making the SQL
conneciton via ADO.NET to the database.

I can run the same SP for SQL Management Studio and it runs in a couple secs
without issue.

I have tried bouncing IIS and restarting SQL with the same results.
Typically rebooting server fixes the problem until it occurs again.

 

[JM]

Boy, do I feel your pain on this one! LOL

I can't tell you how many times I've had to undo the knots caused by a
server
neophyte over whom I have zero control. And you're right, because the server
GUI looks like the desktop GUI, it must all be the same -- right?

I've tried many, many times to educate management on the perils of excessive
access, but they don't seem to care. So, I do the best I can to keep them
from
shooting themselves in the foot and realize that I've done all that I can
given
the circumstances.

Which, allows me to sleep at night.

John

 

[dm1608]

I'm still not convinced its related to not disposing objects or anything.
Sometimes, even when the server is rebooted, the problem reappears within a
few minutes of them trying ot run the web query again. Other times, the
server runs for days before they start complaining.

What you said makes sense, I'm just not seeing affect that you're describing
over time.

I'm looking at the index and rebuilding them again on the query and have
added a 'WITH RECOMPILE' to the SP. So far nothing has helped.

I can still run the query much faster in Query Analyzer than thru web page;
when problem appears.

 

[dm1608]

Thanks for the comments.

I have lots of logging and junk going on and can monitor both console and
RDP access to server.

The real challenge is since I own the server environment and basically
anyone that is allowed to remote in as full admin access, any/all server
issues are my issue -- whether the user doing something stupid or has
inadvently dragged a folder to another location an the application no longer
works.

My latest issue on a server where 5 users can RDP to the server to run end
of day work on a particualr server is that there was one operator that has
to use this one application (only one instance can be running) and it has
about 3-4 gridviews and other windows on the main console He is trying to
end out work and is constantly hitting "refresh" and then gets an hour-glass
for 10-20 seconds, depending ho frequently he is dong it....

Other users are complaining the sytem is slow... all the while that this guy
is hitting "refresh" and the CPU spikes to 100% while its re-populating all
the controls.

I guess I need to move from 4 processors to 8 processors now because that is
what they want.... geez

 

[Guest]

How would rebuilding indexes or recompiling stored procedures help? That's
clearly not where the problem lies...

This is absolutely right and regular way to fix problems with the slow
respond.
http://technet.microsoft.com/en-us/library/ms189858.aspx

 

[Guest]

I'm still not convinced its related to not disposing objects or anything.
Sometimes, even when the server is rebooted, the problem reappears within a
few minutes of them trying ot run the web query again.   Other times, the
server runs for days before they start complaining.

What you said makes sense, I'm just not seeing affect that you're describing
over time.

I'm looking at the index and rebuilding them again on the query and have
added a 'WITH RECOMPILE' to the SP.  So far nothing has helped.

I can still run the query much faster in Query Analyzer than thru web page;
when problem appears.

It's difficult to answer without seeing the code, but I would
recommend to check execution plan and ensure you have all indexes
created. Use Database Engine Tuning Advisor (DTA) for this.

 

[dm1608]

All --

I do not have any source code or anything to look at. All I know is that we
have this code running in 4 other data centers.

There could be a problem with the code, but I seriously doubt it.

The fact is that typically after a reboot, the problem disappears.
However, I've seen it re-surface after a few minutes of rebooting. There
are only a few users ever using this application at any point in time. So
the likelihood of it being a dispose() issue or something that manifests
itself over time is slim.

I may not ever get this solved, but I do know I've been running the
application without any updates (other than Microsoft patching on a monthly
basis) for like 2+ years now. There has been no code changes and
performance over the last 6 months seems to have gotten worse. The
database is about 80GB total, which isn't that huge in the grand scheme of
things.

I'm still not convinced its related to not disposing objects or anything.
Sometimes, even when the server is rebooted, the problem reappears within
a
few minutes of them trying ot run the web query again. Other times, the
server runs for days before they start complaining.

What you said makes sense, I'm just not seeing affect that you're
describing
over time.

I'm looking at the index and rebuilding them again on the query and have
added a 'WITH RECOMPILE' to the SP. So far nothing has helped.

I can still run the query much faster in Query Analyzer than thru web
page;
when problem appears.

It's difficult to answer without seeing the code, but I would
recommend to check execution plan and ensure you have all indexes
created. Use Database Engine Tuning Advisor (DTA) for this.

 

[Guest]

All --

I do not have any source code or anything to look at.  All I know is that we
have this code running in 4 other data centers.

There could be a problem with the code, but I seriously doubt it.

The fact is that typically after a reboot, the problem disappears.
However, I've seen it re-surface after a few minutes of rebooting.    There
are only a few users ever using this application at any point in time.  So
the likelihood of it being a dispose() issue or something that manifests
itself over time is slim.

I may not ever get this solved, but I do know I've been running the
application without any updates (other than Microsoft patching on a monthly
basis) for like 2+ years now.   There has been no code changes and
performance over the last 6 months seems to have gotten worse.   The
database is about 80GB total, which isn't that huge in the grand scheme of
things.

--dm1608

For me it looks like during last 2 years you got a lot of new data and
your queries became slow because of bad structure, bad sql and/or
missing indexes. Without seeing the code of sql I can only suggest
again to look at DTA. This is the only way if you have no access to
the source code of your ASP.NET application. If you know what stored
procedure is working slow then you should check its sql. I would also
check log files and perform shrinking and backup of log and data

--Mark

Mark, I saw what he said. I meant, he is right when he is trying to
fix his problem by re-doing indexes. From my experience this
definitely helps BUT when indexes are there. If he has no index on
"slow" table, then re-doing indexes on other tables would not help.

 

[Guest]

Yes, but (to me, at least) this is clearly not the problem here not only
because of the "deterioration over time" factor, but specifically because
when the OP runs the SP in Query Analyzer it runs very fast. If the database
was inefficiently designed, or table indexes were inefficient or
non-existent, then the SP would run slowly in Query Analyzer as well.

It's the fact that it runs fast in Query Analyzer but not (after a while)
via ADO.NET that makes me think that the database access code must be where
the problem lies. Assuming that is indeed the case, and the database access
code is leaking memory because it doesn't close connections and/or dispose
objects, then no amount of database fine-tuning will help...

But, until / unless the OP can gain access to the source code, we'll never
know...

That's what (connection leaks) I immediately thought of when I read
the very first post but he said later on that sp_who2 returns nothing.
So, I think, that can't be the case.

Maybe he should look at the following list of What to do if your SQL
Server system is slow
http://sql-server-performance.com/Community/forums/p/2213/2213.aspx

And proceed all steps to ensure that SQL is running properly and no
errors in log. Yes, I know that it works fast in QA, but maybe it was
due to a wrong test method...

 

[dm1608]

Hi all -- I reindexes all the tables this weekend in the database. It
took about 8+ hours to run, but it should be done.

Only time will tell now. Maybe I won't get any complains this week. We
shall see.

Yes, but (to me, at least) this is clearly not the problem here not only
because of the "deterioration over time" factor, but specifically because
when the OP runs the SP in Query Analyzer it runs very fast. If the
database
was inefficiently designed, or table indexes were inefficient or
non-existent, then the SP would run slowly in Query Analyzer as well.

It's the fact that it runs fast in Query Analyzer but not (after a while)
via ADO.NET that makes me think that the database access code must be
where
the problem lies. Assuming that is indeed the case, and the database
access
code is leaking memory because it doesn't close connections and/or dispose
objects, then no amount of database fine-tuning will help...

But, until / unless the OP can gain access to the source code, we'll never
know...

That's what (connection leaks) I immediately thought of when I read
the very first post but he said later on that sp_who2 returns nothing.
So, I think, that can't be the case.

Maybe he should look at the following list of What to do if your SQL
Server system is slow
http://sql-server-performance.com/Community/forums/p/2213/2213.aspx

And proceed all steps to ensure that SQL is running properly and no
errors in log. Yes, I know that it works fast in QA, but maybe it was
due to a wrong test method...