October 6, 2011 at 5:19 am
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.
Thanks
October 6, 2011 at 9:53 am
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.
We have stopped SQL Sentry running off and on over the past couple of days and we tried to figure out how the numbering of the traces work. We fired up a couple of profilers and ran SELECT * FROM :: fn_trace_getinfo(default).
Since there is a default trace and we have SQL Sentry running, we added two new traces via profiler. Using fn_trace_grtinfo, 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! There were at least three of us watching when he did this twice. Which is good and bad, it’s good in that we know how it probably happened, and bad that it happens. We think that there might have been an initial problem that was resolved but in doing so we created the rouge trace. It’s a little disconcerting that we are unable to reproduce.
Server is 2003 with SQL Server 2005, Client is Windows 7 with 2008 R2 SSMS.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply