SQL Server Performance Forum – Threads Archive

Database Time-outs

We’re getting problems with our applications hanging and connections to databases timing out. Seems to be locks and in some case we can identify a blocking process and sort it out. Sometimes though no matter how hard we try we cannot explain why connections time out or applications hang. The server is no busier than normal, CPU is stead at between 14 & 40%, RAM is OK. There is no visible bottleneck (we’ve had specialists in to look at our config and we got the all clear). We have a 4 way Xeon dedicated SQL server with 6GB & SAN back-end, about 20 databases of between 250MB & 7GB, and around 400 users. Apps are on separate servers. We’ve run Profiler with all kinds of filters etc and cannot identify anything which corelates with the problem. sp_who & sp_who2 don’t show anything relevant either. When it does occur it affects different applications and different databases at the same time. Because of this I can’t see how we can be looking at locks in a specific database. It seems like it must be something like the master DB or something going on inside SQL. If we’re getting time-outs on the database connection, what could cause that? (Time out = 30). There are no locks on master or on the database we’re connecting too. Anyone got any further ideas on where to look?

How about Avrg. Disk Queue Length?.
Did you run Maintenance Plan, with rebuild and/or update statistics? Luis Martin
Moderator
SQL-Server-Performance.com

Disks also not particularly busy. % Disk Time & Avg Disk Queue Length are always same – something to do with Xeons or multiple disks. No visible bottlenecks at all. It’s got to be locks (though we just can’t seem them) or some SQL phenomenon that we don’t know about. EM current activity showing no blocks. This screen also times-out occasionally. Maintenance plans run at weekend. Auto stats were on, but currently trying off for a bit (not much difference so far).

I suggest to test network. May be there is some problems.
Also wait for post members with Xeon expertise. Luis Martin
Moderator
SQL-Server-Performance.com

Try to recompile all involved stored procedures on top of above suggestions. Also for reference review thishttp://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=3659 post. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

This actually sounds like blocking in tempdb… Do you applications make heavy use of tempdb? Do they create objects in tempdb within a transaction? I’d run Profiler looking for batches and RPC complete taking more than 100ms. Any statement that shows up should then be investigated to see if it is optimal and/or if it is being delayed by other statements executing at the same time also taking a long time. i.e. is statement 1 takes 5 seconds, but normally only takes 50ms, then there must be something holding it up. The trick will be to identify what other process that will be. Cheers
Twan

]]>