Coping with slow SQL Servers in ASP.NET

[Microsoft Newsserver]

Hi,

We have situations occasionally where the SQL server is extremely slow to
respond. The problm is that the user is left with loading page and has no
idea what in heaven is going on.

Does anyone have any ideas how the DAL can be confiured to timeout and throw
an exception if the response is not quick enough ?

Cheers

 

[Guest]

Hi,

We have situations occasionally where the SQL server is extremely slow to
respond. The problm is that the user is left with loading page and has no
idea what in heaven is going on.

Does anyone have any ideas how the DAL can be confiured to timeout and throw
an exception if the response is not quick enough ?

Cheers

Add "Connect Timeout=<seconds>;" to your connection string and use a
try-catch to handle the sql exception.

You can also try to execute that slow task using an asynchronous call.
If it's a buk copying process, look at the SqlBulkCopy Class
(System.Data.SqlClient)

 

[Michael Nemtsev [MVP]]

Hello Microsoft,

You need to use Performance counters or profiler to understand system bottlenecks
first

---
WBR,
Michael Nemtsev [.NET/C# MVP] :: blog: http://spaces.live.com/laflour

"The greatest danger for most of us is not that our aim is too high and we
miss it, but that it is too low and we reach it" (c) Michelangelo


MN> Hi,
MN>
MN> We have situations occasionally where the SQL server is extremely
MN> slow to respond. The problm is that the user is left with loading
MN> page and has no idea what in heaven is going on.
MN>
MN> Does anyone have any ideas how the DAL can be confiured to timeout
MN> and throw an exception if the response is not quick enough ?
MN>
MN> Cheers
MN>

 

[Mark Rae [MVP]]

We have situations occasionally where the SQL server is extremely slow to
respond. The problm is that the user is left with loading page and has no
idea what in heaven is going on.

Does anyone have any ideas how the DAL can be confiured to timeout and
throw an exception if the response is not quick enough ?

You're trying to fix the wrong problem!

You should be looking at why the database query is so slow and fixing
that...

 

[Cowboy \(Gregory A. Beamer\)]

You can time out the query, but I would find out why you are having the
problem first.

Step 1 is to run performance counters and see what is happening

Then, you will likely find out which queries are your problem child and fix
them. Followed by possibly adding or correcting indexes. Or moving to a
stronger box, as you have outgrown the box you are running SQL on.

Throwing in the connection timeout will let you tell the user the server is
too busy, but it will not solve the problem.