SEO

We are currently running MS-SQL2000 on a MS Server 2003 OS with 8G of
memory (AWE enabled) with 4 HT processors (OS thinks that there are 8
because of the HT). We are experiencing 1 - 3 days of very slow activity
about the same time each month. Processes that usually take 20 minutes are
lasting 3 hours, this slowed processing only occurs on these days and then
everything goes back to normal. One more thing I should note is that when
this occurs the processors are still running at usual capacity (below 50%),
disk activity is not maxed out (Raid 5 configuration) and there is still
physical memory available. If anything it would appear that the processor
load goes down and well as disk activity when this problem occurs.

We have checked the following:

Blocking - Other programs running. We have stopped all other processes and
run just one as a test with the same results, very slow.

No Code changes to the affected processes.

Server logs - nothing going on no rebuilding of indexes or repairing of DB
etc.

Applied the latest MS updates.

We are at a loss and were wonder if anyone else had experienced any similar
issues. Any help would be greatly appreciated.

-p

Make sure the statistics are up to date. When in doubt, run UPDATE
STATISTICS.

Also check when you run your maintenance plans and what fill factor you
use when reindexing tables.

HTH,
Gert-Jan


Pippen wrote:
>
> We are currently running MS-SQL2000 on a MS Server 2003 OS with 8G of
> memory (AWE enabled) with 4 HT processors (OS thinks that there are 8
> because of the HT). We are experiencing 1 - 3 days of very slow activity
> about the same time each month. Processes that usually take 20 minutes are
> lasting 3 hours, this slowed processing only occurs on these days and then
> everything goes back to normal. One more thing I should note is that when
> this occurs the processors are still running at usual capacity (below 50%),
> disk activity is not maxed out (Raid 5 configuration) and there is still
> physical memory available. If anything it would appear that the processor
> load goes down and well as disk activity when this problem occurs.
>
> We have checked the following:
>
> Blocking - Other programs running. We have stopped all other processes and
> run just one as a test with the same results, very slow.
>
> No Code changes to the affected processes.
>
> Server logs - nothing going on no rebuilding of indexes or repairing of DB
> etc.
>
> Applied the latest MS updates.
>
> We are at a loss and were wonder if anyone else had experienced any similar
> issues. Any help would be greatly appreciated.
>
> -p

Gert-Jan,

Thank you for you suggestions, we have UPDATE STATISTICS set to run
automatically. We are looking into the fill factor on a supporting DB. The
indexes are rebuilt each week for the DB in question.

Once again thank you for your help,

-p

"Gert-Jan Strik" <sorry@toomuchspamalready.nl> wrote in message
news:42446691.CBD42783@toomuchspamalready.nl...
> Make sure the statistics are up to date. When in doubt, run UPDATE
> STATISTICS.
>
> Also check when you run your maintenance plans and what fill factor you
> use when reindexing tables.
>
> HTH,
> Gert-Jan
>
>
> Pippen wrote:
>>
>> We are currently running MS-SQL2000 on a MS Server 2003 OS with 8G of
>> memory (AWE enabled) with 4 HT processors (OS thinks that there are 8
>> because of the HT). We are experiencing 1 - 3 days of very slow activity
>> about the same time each month. Processes that usually take 20 minutes
>> are
>> lasting 3 hours, this slowed processing only occurs on these days and
>> then
>> everything goes back to normal. One more thing I should note is that when
>> this occurs the processors are still running at usual capacity (below
>> 50%),
>> disk activity is not maxed out (Raid 5 configuration) and there is still
>> physical memory available. If anything it would appear that the processor
>> load goes down and well as disk activity when this problem occurs.
>>
>> We have checked the following:
>>
>> Blocking - Other programs running. We have stopped all other processes
>> and
>> run just one as a test with the same results, very slow.
>>
>> No Code changes to the affected processes.
>>
>> Server logs - nothing going on no rebuilding of indexes or repairing of
>> DB
>> etc.
>>
>> Applied the latest MS updates.
>>
>> We are at a loss and were wonder if anyone else had experienced any
>> similar
>> issues. Any help would be greatly appreciated.
>>
>> -p

Pippen (name@notreal.add) writes:
> We are currently running MS-SQL2000 on a MS Server 2003 OS with 8G of
> memory (AWE enabled) with 4 HT processors (OS thinks that there are 8
> because of the HT). We are experiencing 1 - 3 days of very slow activity
> about the same time each month.

Well, it seems that you should be looking at what happening around
these days. Is there some process running on these days? Is lots of
data loaded in the beginning of this period, and then a maintenance
job handles reindexing a few days later?

One thing is for sure: it's not SQL Server that looks in the calender
and says "Oh, it's the 11th today, I take it soft for a couple of days".
If it is this regular there, is something in your environment. What is
of course hard to tell from a distance.

Not that I believe it matters, but I would recommend you to set "max
degree of parallelism" to 4, that is equal to the number of physical
processors.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp