6

I'm having some recent timeout issues in our SQL 2008 R2 server. This started occurring late last week and has since somewhat improved.

Issues: When using SSMS and right clicking over a database...menu will delay on showing. Could be anywhere from 2-3 seconds to 10 seconds.

When trying to access reports in SSMS, hour glasses. It will either error out or eventually show the reports.

When using Team Foundation Server and checking in changes, It won't check in due to timeouts. When debugging web apps, can timeout when accessing DB

I've looked at profiler and haven't seen anything that stands out. CPU utilization is low, memory is fine, and there is no locking issues of any sort.

Problems go away when: Logged into the local DB server and connect via SharedMemory? "[local]". Everything is quick and runs as it is suppose to. When I switch the connection to use TCP/IP ["127.0.0.1"] I see the same delays.

Again, this doesn't happen all day....mornings are typically fine and then points in the after noon I'll start seeing timeouts.

Not seeing anything odd in: "select * from sys.dm_exec_requests order by status". Most are showing sleeping.

Not seeing anything odd in: "select * from sys.dm_exec_sessions order by login_time desc". About 48 active sessions. 

 sp_monitor

 cpu_busy           io_busy         idle
 31331(2593)-17%    573(41)-0%  4025036(224233)-1531%

 packets_received         packets_sent  packet_errors
 6131943(343656)          6305718(356043)   0(0)

 total_read           total_write       total_errors    connections
 1372503(103382)      1678338(140267)   0(0)            5324156(296335)

 select @@MAX_CONNECTIONS == 32767
Improve this question
5
  • 7
    Sounds like you're experiencing some high network latency.
    – Thomas Stringer
    Commented Feb 16, 2012 at 18:57
  • Ditto @Shark's response. Had the same error a while back.
    – Nick Vaccaro
    Commented Feb 16, 2012 at 18:59
  • I'll have to take a look more into that, but everything I've tested out so far for network seem fine. I'm by no means a network person though. Why, however, when logging into DB while logged into that server, using SSMS would I get timeouts on loopback address? Its still using tcp/ip byt there shouldn't be latency. -Thanks
    – Clarke76
    Commented Feb 16, 2012 at 21:35
  • May I suggest adding a static entry to your hosts file on the client to point crl.microsoft.com to 127.0.0.1? While this is a total shot in the dark, it can sometimes fix odd network-related issues with Management Studio.
    – user1240
    Commented Feb 17, 2012 at 3:21
  • And to go along with @Shark's comment, have your network folks made uncommunicated changes?
    – jl01
    Commented Feb 17, 2012 at 14:16

1 Answer 1

Reset to default
1

Try looking at a couple of things to help determine if you are having issues with the network.

Wait Stats

  • ASYNC_NETWORK_IO

On The Server

  • Network Interface:Output queue length
  • Redirector:Network error/sec
    • This should always be zero
  • TCPv4:Segments retransmitted/sec & TCPv6:Segment retransmitted/sec
    • When divided over the total number of segments should not be more than 3%.
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.