We have a SQL 7.0 Standard Server running on a Windows 2000 Server
machine with 2 800mhz Pentium III with 2GB memory. Our front end is
Access 97 and 2000 with most ADO connections for the scripts but some
DAO for forms and reports. We recently "released" a new version of
the "database" that caused a catastrophic event to start happening
with our SQL server.
Using PerfMon we monitored the CPU utilization on the server and
noticed that the CPU load would drop to 0 for approx 5-10 seconds and
then jump back up to our average 60-70% utilization. During this
drop, there is NO disk activity no new connections being made, etc.
We then took the process a step further and loaded a "stress" program
that put about 30% load on the server to start with. Then we
monitored each processes load. SQL Server process would drop to 0%
while the stress process continued at 30%.
The problem is that the SQL does absolutely NOTHING for 5-10 seconds.
You cannot connect, any querys that are running stop, their is no disk
activity (logs, data drives), and you cannot even get sp_who2 to run
from Query Analyser. We thought maybe blocking (we have built an
"app" that monitors this), but we don't see any blocking before it
locks and nothing after it locks.
Out of despiration we "rolled back" to our previous version to get
people working again. After business hours, we have tried to
duplicate the problem on machines (2 or 3 at a time) but cannot get it
to duplicate the problem.
The only experience we had previous to this was using DNS to resolve
the server name which caused a problem EXTREMELY familar to this
problem. However, we have double checked every machine we have, and
none of them are using DNS to resolve.
Any idea's would be most appreciated.
Patrick Moore
machine with 2 800mhz Pentium III with 2GB memory. Our front end is
Access 97 and 2000 with most ADO connections for the scripts but some
DAO for forms and reports. We recently "released" a new version of
the "database" that caused a catastrophic event to start happening
with our SQL server.
Using PerfMon we monitored the CPU utilization on the server and
noticed that the CPU load would drop to 0 for approx 5-10 seconds and
then jump back up to our average 60-70% utilization. During this
drop, there is NO disk activity no new connections being made, etc.
We then took the process a step further and loaded a "stress" program
that put about 30% load on the server to start with. Then we
monitored each processes load. SQL Server process would drop to 0%
while the stress process continued at 30%.
The problem is that the SQL does absolutely NOTHING for 5-10 seconds.
You cannot connect, any querys that are running stop, their is no disk
activity (logs, data drives), and you cannot even get sp_who2 to run
from Query Analyser. We thought maybe blocking (we have built an
"app" that monitors this), but we don't see any blocking before it
locks and nothing after it locks.
Out of despiration we "rolled back" to our previous version to get
people working again. After business hours, we have tried to
duplicate the problem on machines (2 or 3 at a time) but cannot get it
to duplicate the problem.
The only experience we had previous to this was using DNS to resolve
the server name which caused a problem EXTREMELY familar to this
problem. However, we have double checked every machine we have, and
none of them are using DNS to resolve.
Any idea's would be most appreciated.
Patrick Moore
Comment