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