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! ;-)

 

[Cor Ligthert[MVP]]

The first think I would do was to use the SQL profiler to see by instance
what other processes are locking the connections.

Cor

 

[Paul]

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?

 

[sloan]

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.

 

[sloan]

//> 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).

 

[Jim Rand]

In another post, I read that someone was having performance problems calling
stored procedures from .Net. Running the stored procedure using SQL Server
Management studio returned results much faster. One respondent suggested
using local variables in the stored procedure assigned with the incoming
parameter values. Apparently, it impacts the way the query analyzer works
and yields better results when calling from .Net.

Just a thought

 

[dm1608]

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.

 

[dm1608]

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.

 

[dm1608]

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.

 

[dm1608]

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.

 

[dm1608]

Thanks -- I'll check into this.

In another post, I read that someone was having performance problems
calling stored procedures from .Net. Running the stored procedure using
SQL Server Management studio returned results much faster. One respondent
suggested using local variables in the stored procedure assigned with the
incoming parameter values. Apparently, it impacts the way the query
analyzer works and yields better results when calling from .Net.

Just a thought

 

[sloan]

// One respondent
Correct. That is one of the workarounds for Parameter Sniffing.

The other one (that I know about it) is
OPTION(RECOMPILE);

on the query.

As I suggested, google "Parameter Sniffing" and you'll find the few
workaround available.

 

[sloan]

While I think your issue is parameter sniffing.........here is another thing
to educate yourself on.


ConnectionString = _
"Network Library=DBMSSOCN;" & _
"Data Source=xxx.xxx.xxx.xxx,1433;" & _
"Initial Catalog=myDatabaseName;" & _
"User ID=myUsername;" & _
"Password=myPassword"
oSQLConn.Open()
Where:
- "Network Library=DBMSSOCN" tells SqlClient to use TCP/IP Q238949
- xxx.xxx.xxx.xxx is an IP address of the remote SQL Server.
- 1433 is the port number for the remote SQL Server. Q269882 and Q287932
- You can also add "Encrypt=yes" for encryption

For more information, see: SqlConnection Class, Q308656, and .NET Data
Providers



DBMSSOCN refers to the tcp/ip protocol. If you want to see a little bit how
this works......go to Control Panel / ODBC / System DSN / and create a new
(you can delete it later).....connection.

Go through the wizard.....and there is a button called "Client
Connectivity". This will show you the different protocols.



The connection string above ..... is how you specific the protocol in the
connection string.

When trouble shooting an issue, its best to provide the most anal connection
string you can.

IP Address, Port# (usually 1433) and then the Network Library. Check
www.connectionstrings.com for other examples of specifying the
network-library.



Again......I don't think this is your issue, but it something to be aware
of.





//I've changed the timeouts for IIS and SQL Connection //

I'm pretty sure you specify the timeout on the SqlCOMMAND object, not the
connection object. The connection object is is for "wait this long to try
to make a connection (aka, find the server)".

Connection Timeout is NOT how long a stored procedure (or inline sql) will
try to run.

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.commandtimeout.aspx

That "how long" is for the SqlCommand object.

 

[sloan]

//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.

 

[Paul]

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.

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.

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.