• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 187
  • Last Modified:

sql 7 server alternates between fast and slow

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.
0
jw608ems
Asked:
jw608ems
  • 3
  • 3
  • 2
  • +1
1 Solution
 
TroyKCommented:
jw608ems;

Are there any automated backups or SQL Server jobs that
are being run during the period you experience the slowdown?

HTH,
TroyK, MCSD
0
 
wdaltonCommented:
The combination of high CPU usage and high locking is unusual. Page locks usually only happen on Tempdb. (dbid=2) A combination of these 2 facts leads me to believe you have a query or batch that is doing a huge join or sort, thus allocating a high number of pages in Tempdb.
If this occurs again, use sp_who2 to see which spid uses the most cpu : run sp_who2 a few times and check which spid has the biggest change in cpu and IO
Then execute DBCC INPUTBUFFER(<spid>) to find out what the query is that is being executed.
It might also be that it's just a server side cursor with a huge resultset. This should not give any long durations in profiler but will use a lot of tempdb.
Solutions?
Move Tempdb to another drive, and/or optimize the heavy query and/or get rid of server-side cursors.

hth
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
listening
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
jw608emsAuthor Commented:
Sorry about the delay.  I was sent out of town.  I did notice something this morning (monday).  The queries were running fast with no locking issues and cache pages in memory were about 3300.  I looked at the server memory allocation in the property pages of enterprise manager and it is set to configure dynamically.  When I exited the property pages I checked cache pages on the performance monitor and they were 0.  Queries were once again slow and locks high.  I believe something is resetting cache since this is exactly what is happening.

The server is used for other network business.  There were no SQL automated tasks running at the time.  Any suggestions what to look for?
0
 
TroyKCommented:
jw608ems;

Is there a possibility that someone with 'sysadmin' or 'serveradmin' permissions is running DBCC FREEPROCCACHE without your knowledge?

Does this problem occur only during business hours (i.e., when there are users on-site or connected to the db)?

TroyK, MCSD
0
 
wdaltonCommented:
some things you can check:
Execute:  dbcc sqlperf(waitstats,clear)
wait for a minute or so, while things are slow, and then execute : dbcc sqlperf(waitstats)

This should give an overview of the things your server is losing time with.
I have a hunch that your checkpoints are not dynamic, and that every now and then all your cache memory is being written to disk, but the disks can't keep up at that moment. so the other transactions slow down, causing locks. This behavior should only last a few seconds.
What do the prefmon counters 'checkpoint pages/sec' and recompiles/sec show?
0
 
jw608emsAuthor Commented:
wdalton - Sounds interesting.  I will try that.

TroyK, MCSD - Very unlikely anyone else running DBCC since Iam the only DBA.  Small office about 70 people and all of them are granted role permissions only.

I have a java class to attend now so I can't continue until tomorrow.  I will try all suggestions in the morning.
0
 
jw608emsAuthor Commented:
wdalton/troyk - I tried the sqlperf(waitstats) after a clear but did not know what I was looking at.  The only wait type with any entry was miscellaneous with 555 requests, 1394516 wait time, 903000 signal wait time.  This was during a brief slow down period shortly after noon (transaction log is backed up at noon but only takes 2 seconds).  I could not capture the other counters you suggested during this brief slow down.

Cache on the other hand looked real good with plenty of space for data and compiled stored procedures.

I did make a discovery.  Our network administrator has a SQL database on the same server which is used by multiple citrix servers to store config and operational stuff.  Its transaction log is sitting at over 98% usage, he's never backed it up.  Its 4 times larger than his database.  Other than that I am kind of at a loss.

Any more suggestions?
0
 
wdaltonCommented:
That full transaction log might be the cause of your problem: If it gets full, it will allocate extra space , by default 10% of the size, so if the logfile is rather big already, allocating extra space can take up several minutes. During that time the disks will be so busy that all other activities on the server seem to stall. (especially if they all use the same disk).
Try backing up the log, or truncating it (dump transaction <dbname> with NO_LOG)
There are some counters in profiler to trace when a file size changes.

hth,
W.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 3
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now