ansaurus

Question

SQL Timeout Errors

Answer 1

A:

Does the SQL Server have a regular maintenance plan scheduled (and running), including rebuilding indexes?

Mitch Wheat 2009-05-27 10:46:25

Answer 2

A:

Since you haven't changed the code, there is nothing wrong with your connections.

Go find out why the SQL statements are taking so long. It's hard to say it any simpler. You may find you have a deadlock situation between your code that has not changed, and some other use of the database, which has changed. But you need to go track that down and not touch the working code that hasn't changed.

John Saunders 2009-05-27 10:50:38

Answer 3

+1 A:

You mention changing the timeout of SQL connections, but it's not clear what exactly is timing out: is it the connection to the database, or do the queries take too long?

One tool that might help is Sql Profiler. Limit it to the user or database that is experiencing the timouts. If query length is an issue, this should give you insight in exactly which queries are running long.

If the connection itself is timing out, check Current Activity in Management Studio. This shows the number of open connections; if they're large, there probably is a leak somewhere. You can test that in development by running with a connection string that contains:

MAX POOL SIZE=2;

This will cause leaks to quickly exhaust the connection pool.

Mitch Wheat's suggestion is very good, inaccurate statistics can really deteriorate performance over time. This query can tell you if your statistics are up to date:

SELECT 
    object_name = Object_Name(ind.object_id),
    IndexName = ind.name,
    StatisticsDate = STATS_DATE(ind.object_id, ind.index_id)
FROM SYS.INDEXES ind
order by STATS_DATE(ind.object_id, ind.index_id) desc

Andomar 2009-05-27 11:26:28

I've since installed some software to monitor the Performance of SQL which has given me some interesting information - but I'm a bit at a loss as to how I can use it effectively.... My Seek Time Writes (Disk Wait Time, MS) seems to be taking between 60ms and 140ms - normal reports to be about 10-15ms. Also Cache Hits (Logical Reads/Physical Reads) seem to be very bad and Physical Reads (RW Per Sec) seems to be in the region of 150ms to 400ms.... What would that point to? Answer on a postcard....

Carl Crawley 2009-05-28 15:36:48

You could look at the time an "execution batch" takes. If it's less than ten seconds, that's not giving a timeout to the web server. I'd investigate the number of simultaneous connections.

Andomar 2009-05-28 21:23:24

ansaurus

tags:

views:

answers:

SQL Timeout Errors

related questions