?
Solved

sql 7 server alternates between fast and slow

Posted on 2003-03-06
9
Medium Priority
?
186 Views
Last Modified: 2010-08-05
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
Comment
Question by:jw608ems
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 7

Expert Comment

by:TroyK
ID: 8082430
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
 
LVL 1

Expert Comment

by:wdalton
ID: 8083689
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 8087254
listening
0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:jw608ems
ID: 8103324
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
 
LVL 7

Expert Comment

by:TroyK
ID: 8104277
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
 
LVL 1

Accepted Solution

by:
wdalton earned 500 total points
ID: 8105097
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
 

Author Comment

by:jw608ems
ID: 8105673
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
 

Author Comment

by:jw608ems
ID: 8113091
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
 
LVL 1

Expert Comment

by:wdalton
ID: 8226013
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

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question