that runs something at these times and uses memory and disk I/O.
> > Star,
> > If checkpoints pages/persec are 0 then that's probably not an issue but
> > high disk queues are. IF you have high disk queues then the cpu will be
> > since it will usually be waiting for data from the disks to process. My
> > guess is that you have backups running at this time. What is your
> > schedules like, both SQL and NT? Are you doing disk backups and is the
> > destination the same drive array as the database? Do you have a
> > plan set up that may be issuing backups and or DBCC's that you aren't
> > of?
> > --
> > Andrew J. Kelly, SQL Server MVP
> > TargitInteractive
> > > Sorry for the long post, but I wanted to give as much
> > > detail as possible...
> > > Using sp_who, I have determined that blocking is not
> > > occuring during the overload.
> > > I have captured a perfmon log of a typical slowdown (all
> > > counters) - so I have plenty of data. Just need a little
> > > guidence in interpreting the data.
> > > My main questions are as follows.
> > > 1. What is the trigger for this overload that happens only
> > > a few times a day? During the overload, Checkpoint
> > > pages/sec is near zero - so can I take it from this that
> > > Checkpointing is not the trigger? Also, if I issue a
> > > manual Checkpoint, this never triggers an overload.
> > > I have gathered a Profiler log but have been unable to pin
> > > the overload down to a particular SQL command.
> > > 2. When each overload occurs, it typically takes several
> > > minutes to clear. Either SQL Server is sending/receiving
> > > excessive amounts of data to/from disk, or the disk
> > > subsystem is too slow (possibly faulty). How do I
> > > determine which is the case? During the overload,
> > > PhysicalDisk:Avg Disk Queue Length maxes out at 37 for a
> > > minute or two.
> > > 3. CPU utilisation is low (30%) throughout the overload.
> > > 4. During the overload the counter 'SQLServer:Databases
> > > DBCC Logical Scan Bytes/sec' exhibits a spike (of
> > > 20Mb/sec). The business system does not use DBCC, nor were
> > > any DBCC commands typed in at the console to cause the
> > > overload. Do you know what might have triggered this? Does
> > > SQL Server issue internal DBCC commands for some reason?
> > > Scrutiny of the log shows several DBCC CHECKDB and DBCC
> > > CHECKFILEGROUP commands. What causes these? (These spikes
> > > occur every 40 minutes or so. Most are *not* accompanied
> > > by an overload)
> > > 5. At the onset of an overload, Memory:Available Bytes
> > > drops from its (normally static) value of 85Mb to 3Mb.
> > > During the overload it climbs back to its 85Mb value. My
> > > assessment of this is that something is suddenly grabbing
> > > 82M of memory, and that the overload is SQL trying to
> > > release memory to bring available bytes back to 85Mb. Are
> > > there any counters I can examine to prove this (or
> > > otherwise). (BTW, the administrator reckons there are no
> > > other apps running on the server that might be grabbing
> > > this memory). The fact that after the
> > > overload 'Process:Working Set' for the sqlserver process
> > > is unchanged suggests my assessment is incorrect.
> > > 6. During the overload Memory:Page Reads/sec maxes out at
> > > 1112 pages/sec. Memory:Page Writes/sec is much smaller at
> > > 68 pages/sec.
> > > 7. Is it possible that the cause of the IO bottleneck is
> > > that the disk needs defragging? Does NT server come with a
> > > disk defragger?
> > > Any help would be *very* much appreciated
> > > Cheers
> > > Dave
> > > >-----Original Message-----
> > > >That's not a lot to go on. You need to monitor more with
> > perfmon to see
> > > >where the bottle neck is. You can also use sp_who2 and
> > Profiler to see what
> > > >commands are being run when this slowdown occurs.
> > Sp_who2 will also tell
> > > >you if anyone is blocking the others. But my guess is
> > you are having disk
> > > >bottlenecks possibly due to CheckPoints. Perfmon disk %
> > and queue counters
> > > >will verify the bottlenecks.
> > > >--
> > > >Andrew J. Kelly, SQL Server MVP
> > > >TargitInteractive
> > > >> Hi,
> > > >> Our (bespoke) business system runs over SQL Server
> > 2000. Most of the time,
> > > >> performance is fine, but approx twice daily, the system
> > overloads for
> > around
> > > >> 10 minutes or so. During this time, users have to wait
> > much longer that
> > > >> usual post data.
> > > >> Data captured during the overload using perfmon shows
> > the following:
> > > >> - Pages/sec goes to 600 (normal value is 2 or less)
> > > >> - CPU stays at around 30%
> > > >> Does anyone have any idea what might be causing this,
> > or any tips on what
> > I
> > > >> can do to get to the bottom of it?
> > > >> Thanks
> > > >> Dave
> > > >> SQL Server running on:
> > > >> Twin 500Mhz PIIIs
> > > >> 785Meg RAM
> > > >> 4x9Gig SCSI disks (hotswap), RAID.
> > > >.