Intermittant performance

Intermittant performance

Post by Star » Tue, 12 Feb 2002 23:21:03



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.

 
 
 

Intermittant performance

Post by Star » Tue, 12 Feb 2002 23:23:59


I forgot to mention - our database is only around 500Mb total. We have
around 5 full time users.

Dave


Quote:> 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.


 
 
 

Intermittant performance

Post by Andrew J. Kell » Tue, 12 Feb 2002 23:45:54


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


Quote:> 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.

 
 
 

Intermittant performance

Post by Brian Mora » Tue, 12 Feb 2002 23:57:37


I always start with SQL Profiler when trying to isolate performance
problems...

--

Brian Moran
SQL Server MVP
SQL Server Magazine Columnist


Quote:> 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.

 
 
 

Intermittant performance

Post by Star » Wed, 13 Feb 2002 02:12:34


Could you be a little more specific please? I do have a
profiler trace that captured one of the overloads, but
aside from telling me that things were running slowly I'm
not sure what else I can glean form it.

>-----Original Message-----
>I always start with SQL Profiler when trying to isolate
performance
>problems...

>--

>Brian Moran
>SQL Server MVP
>SQL Server Magazine Columnist



>> Hi,

>> Our (bespoke) business system runs over SQL Server

2000. Most of the time,

- Show quoted text -

Quote:>> 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.

>.

 
 
 

Intermittant performance

Post by Star » Thu, 14 Feb 2002 01:18:07


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

Quote:>-----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
Quote:>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,

- Show quoted text -

Quote:>> 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.

>.

 
 
 

Intermittant performance

Post by Avnesh gupt » Thu, 14 Feb 2002 05:14:27


Star,

Check to see if you are backing the t database and/or transaction log files
during the time you are experiencing the slow down. I had the same issue,
twice a day I was dumping the log file, with "Attemp to repair minor
problems" checked. This puts the database in single user mode and caused
blocking, which in turn affected peformance. Once I unchekd this box,
performance issue went away.

Thanks - Avnesh


> Could you be a little more specific please? I do have a
> profiler trace that captured one of the overloads, but
> aside from telling me that things were running slowly I'm
> not sure what else I can glean form it.

> >-----Original Message-----
> >I always start with SQL Profiler when trying to isolate
> performance
> >problems...

> >--

> >Brian Moran
> >SQL Server MVP
> >SQL Server Magazine Columnist



> >> 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.

> >.

 
 
 

Intermittant performance

Post by Andrew J. Kell » Thu, 14 Feb 2002 05:34:09


Star,

If checkpoints pages/persec are 0 then that's probably not an issue but the
high disk queues are.  IF you have high disk queues then the cpu will be low
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 backup
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 maintenance
plan set up that may be issuing backups and or DBCC's that you aren't aware
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.

> >.

 
 
 

Intermittant performance

Post by dave bou » Fri, 15 Feb 2002 04:00:23


Hi Andrew,

I am sure that no backups are running at this time (these happen in
the middle of the night). Also my maintenance plan is empty - so that
doen't explain where the DBCCs are coming from. Any other ideas?

I believe I have cured the problem by setting 'max server memory' to
600Mb. I believe this works because it leaves 150Meg of RAM available
for anything else that needs it. I woud love to know why SQL Server
exhibiting tis problem when set to dynamic allocation though - esp.
when dynamic allocn is so strongly recommended in BOL.

Do you have any insights into any of the other questions posed in my
last message.

I very much appreciate the help.

Star


> Star,

> If checkpoints pages/persec are 0 then that's probably not an issue but the
> high disk queues are.  IF you have high disk queues then the cpu will be low
> 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 backup
> 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 maintenance
> plan set up that may be issuing backups and or DBCC's that you aren't aware
> 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.

> > >.

 
 
 

Intermittant performance

Post by Andrew J. Kell » Fri, 15 Feb 2002 04:16:48


It sounds as if you might have another application or service on the server
that runs something at these times and uses memory and disk I/O.

--
Andrew J. Kelly,  SQL Server MVP
TargitInteractive


> Hi Andrew,

> I am sure that no backups are running at this time (these happen in
> the middle of the night). Also my maintenance plan is empty - so that
> doen't explain where the DBCCs are coming from. Any other ideas?

> I believe I have cured the problem by setting 'max server memory' to
> 600Mb. I believe this works because it leaves 150Meg of RAM available
> for anything else that needs it. I woud love to know why SQL Server
> exhibiting tis problem when set to dynamic allocation though - esp.
> when dynamic allocn is so strongly recommended in BOL.

> Do you have any insights into any of the other questions posed in my
> last message.

> I very much appreciate the help.

> Star




- Show quoted text -

> > Star,

> > If checkpoints pages/persec are 0 then that's probably not an issue but
the
> > high disk queues are.  IF you have high disk queues then the cpu will be
low
> > 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
backup
> > 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
maintenance
> > plan set up that may be issuing backups and or DBCC's that you aren't
aware
> > 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.

> > > >.

 
 
 

Intermittant performance

Post by Mike Moh » Sat, 16 Feb 2002 12:05:28



> Hi Andrew,

> I am sure that no backups are running at this time (these happen in
> the middle of the night). Also my maintenance plan is empty - so that
> doen't explain where the DBCCs are coming from. Any other ideas?

> I believe I have cured the problem by setting 'max server memory' to
> 600Mb. I believe this works because it leaves 150Meg of RAM available
> for anything else that needs it. I woud love to know why SQL Server
> exhibiting tis problem when set to dynamic allocation though - esp.
> when dynamic allocn is so strongly recommended in BOL.

> Do you have any insights into any of the other questions posed in my
> last message.

I know this may sound like a dumb question, but do you have any third-party archive tools in your network?
It sounds like you might have a backup to a remote backup server going on.  I'd also check if you have any
local ntbackup scheduled.  Either of those can mess with performance something drastic.

================================================================

Information Technology Group      === Phone: 64 9 917-9999 x8133
Auckland University of Technology === Fax:   64 9 917-9901
PO Box 92006, Auckland, New Zealand =
http://home.aut.ac.nz/staff/mmohr/
================================================================
Chaos reigns within.  Repent, reflect, reboot.  Order shall return.

 
 
 

1. Intermittant ODBC connection to SQL Server 7

I am having an intermittant problem connecting to SQL Server 7 via an ODBC DSN
from an ASP page.

The error message I recieve (in the web page) is:

Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open database requested in
login 'mydsnname'. Login fails.

This occurs when the ASP page attepts to create a connection object.

The error doesnt always happen, but points to when there are several users.

Any help would be greatly appreciated.

Thank in advance,
Andrew

2. What is the best book for T-SQL Language

3. Intermittant ORA-06512 on LONG RAW insert

4. record level security per user

5. Error 16924 Intermittant

6. Importing Format from Windows Phone Log

7. Very strange, intermittant error with Delete command

8. Filemaker web site?

9. Help! Intermittant 408 Informix JDBC Driver errors

10. Intermittant problem with an ASP connection to SQL Server 2000

11. Help! Intermittant 408 Informix JDBC Driver errors

12. Intermittant Read Errors - anyone else experienced and/or solved

13. intermittant error with ADO