Main Page | Report Page

 

  .NET DotNet Forum Index » ADO .NET Forum » ASP.NET Application Gets Frequent SQL Timeout...

Author Message
dm1608...
Posted: Wed May 20, 2009 5:32 pm
 
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! Wink
 
Cor Ligthert[MVP]...
Posted: Wed May 20, 2009 10:35 pm
 
The first think I would do was to use the SQL profiler to see by instance
what other processes are locking the connections.

Cor

"dm1608" <dm3281 at (no spam) nospam.com> wrote in message
news:%23er1BNa2JHA.3544 at (no spam) TK2MSFTNGP04.phx.gbl...
Quote:
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! ;-)




 
sloan...
Posted: Thu May 21, 2009 11:57 am
 
Geeze dude. Don't blame Microsoft for the world's ills.

Take a look here:
http://glennberrysqlperformance.spaces.live.com/blog/cns!45041418ECCAA960!715.entry
http://glennberrysqlperformance.spaces.live.com/blog/cns!45041418ECCAA960!893.entry?sa=646237086
http://glennberrysqlperformance.spaces.live.com/blog/cns!45041418ECCAA960!828.entry

You can find missing indexes and also bad indexes.
You can also look at some I/O stats.

Here is one metaphor for Sql Server db tuning.
Think about stock car racing.

Every team is allowed to take a stock car and turn it into a racing machine.
If someone took a (true, off the dealer's car lot) stock car and put it on
the track......they'd get blown away.

The people who can tweak the car, put improvements on the cars (like correct
indexes), remove things weighing down the car (like bad indexes) are going
to have a chance to win the race.

Sql Server is like that. If you don't learn how to tweak it correctly, then
its nothing but a (off the car lot) "stock" car running in a race. And it
won't perform well.

Glenn's articles above are good ones.


You can also get the "Sql Server Dashboard Reports" up and running.
Google it, they take alot of the information glenn persents, and puts them
in a nice GUI format.

.....................

PS
You might google "Parameter Sniffing" "Sql Server" for another known issue
about performance.


"dm1608" <dm3281 at (no spam) nospam.com> wrote in message
news:%23er1BNa2JHA.3544 at (no spam) TK2MSFTNGP04.phx.gbl...
Quote:
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! ;-)




 
sloan...
Posted: Thu May 21, 2009 12:39 pm
 
//> What makes this even more interesting is that the result set only
returns
Quote:
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.//


That would suggest the Parameter Sniffing issue. Make sure you investigate
that (google search it).



"sloan" <sloan at (no spam) ipass.net> wrote in message
news:uLFbC2j2JHA.3476 at (no spam) TK2MSFTNGP05.phx.gbl...
Quote:

Geeze dude. Don't blame Microsoft for the world's ills.

Take a look here:
http://glennberrysqlperformance.spaces.live.com/blog/cns!45041418ECCAA960!715.entry
http://glennberrysqlperformance.spaces.live.com/blog/cns!45041418ECCAA960!893.entry?sa=646237086
http://glennberrysqlperformance.spaces.live.com/blog/cns!45041418ECCAA960!828.entry

You can find missing indexes and also bad indexes.
You can also look at some I/O stats.

Here is one metaphor for Sql Server db tuning.
Think about stock car racing.

Every team is allowed to take a stock car and turn it into a racing
machine.
If someone took a (true, off the dealer's car lot) stock car and put it on
the track......they'd get blown away.

The people who can tweak the car, put improvements on the cars (like
correct indexes), remove things weighing down the car (like bad indexes)
are going to have a chance to win the race.

Sql Server is like that. If you don't learn how to tweak it correctly,
then its nothing but a (off the car lot) "stock" car running in a race.
And it won't perform well.

Glenn's articles above are good ones.


You can also get the "Sql Server Dashboard Reports" up and running.
Google it, they take alot of the information glenn persents, and puts them
in a nice GUI format.

....................

PS
You might google "Parameter Sniffing" "Sql Server" for another known issue
about performance.


"dm1608" <dm3281 at (no spam) nospam.com> wrote in message
news:%23er1BNa2JHA.3544 at (no spam) TK2MSFTNGP04.phx.gbl...
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! ;-)






 
dm1608...
Posted: Thu May 21, 2009 6:36 pm
 
Hi -- I have already done this.

There is nothing blocking any connections. I've also used sp_who2 to
verify.

Typically, SQL Profiler doesn't show where the ASP.NET page is executing the
SQL statement until the timeout exception has occurred. Which makes me
think that ADO.NET is not able to communicate with SQL for some reason.




"Cor Ligthert[MVP]" <Notmyfirstname at (no spam) planet.nl> wrote in message
news:e9ObV2c2JHA.480 at (no spam) TK2MSFTNGP06.phx.gbl...
Quote:
The first think I would do was to use the SQL profiler to see by instance
what other processes are locking the connections.

Cor

"dm1608" <dm3281 at (no spam) nospam.com> wrote in message
news:%23er1BNa2JHA.3544 at (no spam) TK2MSFTNGP04.phx.gbl...
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! ;-)





 
dm1608...
Posted: Thu May 21, 2009 6:39 pm
 
I seriously do not think its a query plan or anything that is taking
excessive time. If so, why would me running the same SP that I copied from
SQL Profiler with SQL Management Studio run and only take a couple secs?

SQL is installed on the same box as the ASP.NET application.

Since this is a website that I inherited and simply install, I have no
access to the source code to see what they're doing or how. I can simply
reverse engineer it by looking at SQL Trace and unenrypting their 2005 SP.

It almost seems like ASP.NET/ADO.NET cannot communicate to SQL Server for
some strange reason. But other, smaller, queries work.




"Paul" <paulriley at (no spam) novareconsulting.com> wrote in message
news:evIMW2i2JHA.6056 at (no spam) TK2MSFTNGP03.phx.gbl...
Quote:
Hi mate,

Sorry I feel your frustration, however If anyone is to help you we need
far more infromation. SQL Server performance is an art and a vast subject
as is perfromance of any DB.

Try to replicate the issue and do a query plan for the SP's causing an
issue, and post the query plan here. You should be able to get the queries
by profiling the server and identifying the queries when the problem
occurs. Best to do in a dev environment but push come to shove in the
live. Lets be honest will it really impact the users more than the current
state of affairs?

I have used Oracle and SQL Server and any DB will have its problems
depending on design, so this is most likely not an MS problem so don't get
frustrated with them.

There are many SQLServerPerfomance websites so do a search, some of the
information can be a bit intimidating tho.

Finally another area of contention to look at is your network. Is there a
mass file transfer occuring at the time it occurs?


 
dm1608...
Posted: Thu May 21, 2009 6:42 pm
 
Thanks for the links. I'll look at them and see if they help.

I don't blame Microsoft for anything, however they do give us all enough
rope to hang ourselves.... Plus they make everything so darn easy and
drag-and-drop that anyone that knows how to use a mouse thinks they're an
expert.

I really do not think this is an index problem or blocking issue since I can
run the same query while the ASP.NET page is waiting for the SQL timeout
error without issue.

It's almost like ADO.NET cannot communicate to SQL Server for this
particular query.




"sloan" <sloan at (no spam) ipass.net> wrote in message
news:uLFbC2j2JHA.3476 at (no spam) TK2MSFTNGP05.phx.gbl...
Quote:

Geeze dude. Don't blame Microsoft for the world's ills.

Take a look here:
http://glennberrysqlperformance.spaces.live.com/blog/cns!45041418ECCAA960!715.entry
http://glennberrysqlperformance.spaces.live.com/blog/cns!45041418ECCAA960!893.entry?sa=646237086
http://glennberrysqlperformance.spaces.live.com/blog/cns!45041418ECCAA960!828.entry

You can find missing indexes and also bad indexes.
You can also look at some I/O stats.

Here is one metaphor for Sql Server db tuning.
Think about stock car racing.

Every team is allowed to take a stock car and turn it into a racing
machine.
If someone took a (true, off the dealer's car lot) stock car and put it on
the track......they'd get blown away.

The people who can tweak the car, put improvements on the cars (like
correct indexes), remove things weighing down the car (like bad indexes)
are going to have a chance to win the race.

Sql Server is like that. If you don't learn how to tweak it correctly,
then its nothing but a (off the car lot) "stock" car running in a race.
And it won't perform well.

Glenn's articles above are good ones.


You can also get the "Sql Server Dashboard Reports" up and running.
Google it, they take alot of the information glenn persents, and puts them
in a nice GUI format.

....................

PS
You might google "Parameter Sniffing" "Sql Server" for another known issue
about performance.


"dm1608" <dm3281 at (no spam) nospam.com> wrote in message
news:%23er1BNa2JHA.3544 at (no spam) TK2MSFTNGP04.phx.gbl...
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! ;-)






 
dm1608...
Posted: Thu May 21, 2009 6:44 pm
 
I never heard of this, but will look into it.

I will unencrypt the SQL 2005 SP and see how they're running the query. I
woudl still think that if this is the issue, by me bouncing SQL, that the
query plan would be lost and a new one would have to be created for the
first query.






"sloan" <sloan at (no spam) ipass.net> wrote in message
news:uZyYfNk2JHA.4412 at (no spam) TK2MSFTNGP06.phx.gbl...
Quote:
//> 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.//


That would suggest the Parameter Sniffing issue. Make sure you
investigate that (google search it).



"sloan" <sloan at (no spam) ipass.net> wrote in message
news:uLFbC2j2JHA.3476 at (no spam) TK2MSFTNGP05.phx.gbl...

Geeze dude. Don't blame Microsoft for the world's ills.

Take a look here:
http://glennberrysqlperformance.spaces.live.com/blog/cns!45041418ECCAA960!715.entry
http://glennberrysqlperformance.spaces.live.com/blog/cns!45041418ECCAA960!893.entry?sa=646237086
http://glennberrysqlperformance.spaces.live.com/blog/cns!45041418ECCAA960!828.entry

You can find missing indexes and also bad indexes.
You can also look at some I/O stats.

Here is one metaphor for Sql Server db tuning.
Think about stock car racing.

Every team is allowed to take a stock car and turn it into a racing
machine.
If someone took a (true, off the dealer's car lot) stock car and put it
on the track......they'd get blown away.

The people who can tweak the car, put improvements on the cars (like
correct indexes), remove things weighing down the car (like bad indexes)
are going to have a chance to win the race.

Sql Server is like that. If you don't learn how to tweak it correctly,
then its nothing but a (off the car lot) "stock" car running in a race.
And it won't perform well.

Glenn's articles above are good ones.


You can also get the "Sql Server Dashboard Reports" up and running.
Google it, they take alot of the information glenn persents, and puts
them in a nice GUI format.

....................

PS
You might google "Parameter Sniffing" "Sql Server" for another known
issue about performance.


"dm1608" <dm3281 at (no spam) nospam.com> wrote in message
news:%23er1BNa2JHA.3544 at (no spam) TK2MSFTNGP04.phx.gbl...
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! ;-)








 
sloan...
Posted: Fri May 22, 2009 8:25 am
 
//Plus they make everything so darn easy and
drag-and-drop that anyone that knows how to use a mouse thinks they're an
expert.//

I can agree with that. I call them "right clickers".

Need a table? Sure, right-click .. Add Table.
Need replication? Sure, right-click.. Replication Wizard.





"dm1608" <dm3281 at (no spam) nospam.com> wrote in message
news:%23fro%23Yn2JHA.5772 at (no spam) TK2MSFTNGP02.phx.gbl...
Quote:
Thanks for the links. I'll look at them and see if they help.

I don't blame Microsoft for anything, however they do give us all enough
rope to hang ourselves.... Plus they make everything so darn easy and
drag-and-drop that anyone that knows how to use a mouse thinks they're an
expert.

I really do not think this is an index problem or blocking issue since I
can run the same query while the ASP.NET page is waiting for the SQL
timeout error without issue.

It's almost like ADO.NET cannot communicate to SQL Server for this
particular query.




"sloan" <sloan at (no spam) ipass.net> wrote in message
news:uLFbC2j2JHA.3476 at (no spam) TK2MSFTNGP05.phx.gbl...

Geeze dude. Don't blame Microsoft for the world's ills.

Take a look here:
http://glennberrysqlperformance.spaces.live.com/blog/cns!45041418ECCAA960!715.entry
http://glennberrysqlperformance.spaces.live.com/blog/cns!45041418ECCAA960!893.entry?sa=646237086
http://glennberrysqlperformance.spaces.live.com/blog/cns!45041418ECCAA960!828.entry

You can find missing indexes and also bad indexes.
You can also look at some I/O stats.

Here is one metaphor for Sql Server db tuning.
Think about stock car racing.

Every team is allowed to take a stock car and turn it into a racing
machine.
If someone took a (true, off the dealer's car lot) stock car and put it
on the track......they'd get blown away.

The people who can tweak the car, put improvements on the cars (like
correct indexes), remove things weighing down the car (like bad indexes)
are going to have a chance to win the race.

Sql Server is like that. If you don't learn how to tweak it correctly,
then its nothing but a (off the car lot) "stock" car running in a race.
And it won't perform well.

Glenn's articles above are good ones.


You can also get the "Sql Server Dashboard Reports" up and running.
Google it, they take alot of the information glenn persents, and puts
them in a nice GUI format.

....................

PS
You might google "Parameter Sniffing" "Sql Server" for another known
issue about performance.


"dm1608" <dm3281 at (no spam) nospam.com> wrote in message
news:%23er1BNa2JHA.3544 at (no spam) TK2MSFTNGP04.phx.gbl...
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! ;-)







 
Paul...
Posted: Fri May 22, 2009 9:27 am
 
Quote:
I seriously do not think its a query plan or anything that is taking
excessive time. If so, why would me running the same SP that I copied
from SQL Profiler with SQL Management Studio run and only take a couple
secs?

Yes but this is part of the process. Queries do not always fail, and
deadlocks/locking does not always occur, it is just a random set of events
and not even always at peek times. Profiling could also rule out if it is
SQL server causing the problem.

Quote:
SQL is installed on the same box as the ASP.NET application.

Since this is a website that I inherited and simply install, I have no
access to the source code to see what they're doing or how. I can simply
reverse engineer it by looking at SQL Trace and unenrypting their 2005 SP.

No one suggested you needed access to code to run SQL Profiler, and you do
not need to unencrypt SP's to call them for execution plans.

Quote:
It almost seems like ASP.NET/ADO.NET cannot communicate to SQL Server for
some strange reason. But other, smaller, queries work.

Then it can obviously communicate with the SQL Server, thats a given, and
considering ASP and SQL are on the same box it would be highly unlikely to
be a network issue.
 
 
Page 1 of 1    
All times are GMT - 5 Hours
The time now is Tue Feb 09, 2016 2:39 am