Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question

migrated from stackoverflow.com Feb 16 '12 at 19:23

This question came from our site for professional and enthusiast programmers.

7  
Sounds like you're experiencing some high network latency. –  Thomas Stringer Feb 16 '12 at 18:57
    
Ditto @Shark's response. Had the same error a while back. –  Nick Vaccaro Feb 16 '12 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 Feb 16 '12 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 Feb 17 '12 at 3:21
    
And to go along with @Shark's comment, have your network folks made uncommunicated changes? –  jl01 Feb 17 '12 at 14:16

1 Answer 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%.
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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