sql 7 server alternates between fast and slow
Posted on 2003-03-06
I have a sql 7 server running on a 2000 server being accessed from vb applications implemented on a citrix metaframe. VB apps use ADO 2.5 to access database and all access is through stored procedures.
Sometimes, notably first am, response times are 4-5 seconds for client lookups (Normal response times range 1-2 seconds). When the server slows down its usually 1-4 hours before returning to normal without any intervention.
When failing, cpu useage is +60% and there are an unusually large number of page locks generated for each access. Under normal conditions both are insignificant.
Can someone give me some insight and direction on what I can do to isolate a root cause. Traces and index tuning doesn't expose anything as a root cause. I am inclined to believe this is more of a server environment problem.