up vote 0 down vote favorite
share [g+] share [fb]

We recently moved physical database servers from our corporate office to a collocation facility, and brought up virtual servers to replace the web servers. We are now experiencing periodic system slowness that is related to load. When this occurs, we see sp_Reset_Connection executions running long (2 to 20 seconds) and our system hangs for this duration. Everything then breaks free at once and we see up to 20 sp_Reset_Connections completing at the same millisecond. Our query execution durations are severely impacted when this occurs. We are on SQL Server 2005 SP2. We can reproduce from virtual and physical webservers running Windows 2003 and Windows 2008. We have hit the database server from our pre-existing corporate web servers, and can still reproduce the issue. Microsoft is working with us, and they have deemed our database server to be healthy. They have moved our ticket to the database connectivity team. Data is being captured and analyzed, but the issue is still unresolved. Business is being impacted. HP has analyzed our switches and found no issues. We have no packet loss. We are looking for suggestions as to what to check next.

link|improve this question
Our issue seems to be resolved. We found a rouge trace running and killed it. We found three traces running; 1) the default, 2) SQL Sentry and 3) rouge Trace. This is after Microsoft said there was nothing wrong with the database. – user982136 Oct 6 '11 at 15:48
We fired up a couple of profilers and ran fn_trace_getinfo. we saw two additional traces due to profiler and found that a trace will take the next free number. (If trace 1, 2 and 4 are running creating a new will have trace id of 3, not 5). Ok here is the strange part that we can’t reproduce. The person with two profilers running stopped one and the other window, running profile, showed that trace was stopped as well. Both windows were closed but one of the traces was stilling. We created our rouge trace! – user982136 Oct 6 '11 at 15:51
Server is 2003 with SQL Server 2005, Client is Windows 7 with 2008 R2 SSMS. – user982136 Oct 6 '11 at 15:51
feedback

migrated from stackoverflow.com Nov 19 '11 at 23:38

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

Know someone who can answer? Share a link to this question via email, Google+, Twitter, or Facebook.

Your Answer

 
or
required, but never shown
discard