CPU stuck between 30 and 50 %

I have following configuration:

1. active/passive cluster, Win2000, SQL2000
2. 4 CPUs in each node, 4GB RAM in each node
3. Disk arrays configured for optimal performance (different arrays for diff things, diff RAID levels etc.)

I have few periods during the day when CPU utilization is stuck between 30 and 50% without a single peak on any side. That can last for more than hour. All CPUs are behaving the same way. Response on the client side is very slow. The only process in that period of time that is using CPUs extensively is sqlservr.exe. I've checked deadlocks - nothing. I was running Profiler to check slow queries - nothing. Other perfmon counters seams to be OK (one does give me concern actually - buffer cache hit ratio is actually over 100, around 100.150 or similar?!). Without any performed action, this behaviour stops as sudden as it started and utilization on all CPUs is back on expected 0-100%!

Any ideas?

Appreciate your help!

Almost 1GB of RAM is still available, pages/sec on average < 20.

SQL Server is set to use memory dynamically.

quote:Originally posted by satya

How about status of Total Server Memory (KB), pages/sec counters?
And how about memory settings on SQL Server?

_________
Satya SKJ
Moderator
SQL-Server-Performance.Com

Disk usage is fine - avg. queue lenght is less than 0.1 and %disk time is 1.5%.

quote:Originally posted by LuisMartin

How about Disk usage during that time?



Luis Martin

...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
Bertrand Russell

Which counter do you actually have in mind?
Memory:Available Bytes is variable and is between 500MB and 1GB.

Thanks for help

quote:Originally posted by satya

Is that total server memory is constant or variable?

_________
Satya SKJ
Moderator
SQL-Server-Performance.Com

No, server is dedicated to SQL and sqlservr.exe is the only process that takes resources.
I haven't tried DBCC checks - what should I look for?

quote:Originally posted by satya

Any parallel jobs or process are running during this slow transition period?
How about DBCC checks?

_________
Satya SKJ
Moderator
SQL-Server-Performance.Com

0 allocation errors and 0 consistency errors

quote:Originally posted by LuisMartin

Why not DBCC DBcheck or checkalloc to see if there is some problem?



Luis Martin

...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
Bertrand Russell

automatic off

quote:Originally posted by LuisMartin

Database Grow, is set automatic 10%?.
If yes and Database is big, may be SQL has extra work to extend it.
In this case I suggest to leave automatic but with fix amount, less than 10%.



Luis Martin

...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
Bertrand Russell

7.5GB

quote:Originally posted by satya

What is the size of database?

_________
Satya SKJ
Moderator
SQL-Server-Performance.Com

indexes are recreated each night

quote:Originally posted by satya

Then DBCC DBREINDEX during less traffic on the database and check the performance.

_________
Satya SKJ
Moderator
SQL-Server-Performance.Com

automatic

quote:Originally posted by LuisMartin

How about Update Statistics?


Luis Martin

...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
Bertrand Russell

I have "Auto Update Statistics" database setting ON.
Are you sugessting something in addition to that? Can you be more precise please?
Thanks.

quote:Originally posted by LuisMartin

Automatic in database setting, I gess.
But I suggest to Upadate Statists full on night or non peek hours at least every week.



Luis Martin

...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
Bertrand Russell

What I'm doing each night is running Data Maintenance Plan with option Reorganize data and index pages. So statistics are updated.

quote:Originally posted by LuisMartin

Yes, in addition to that.
Schedule a job with sp_updatestats.




Luis Martin

...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
Bertrand Russell

No Auto_Close, no Auto_Shrink.

quote:Originally posted by satya

Do you have any other database settings like Auto_Close or Auto_Shrink?

_________
Satya SKJ
Moderator
SQL-Server-Performance.Com

When you choose Reorganize data and index pages then Update statistics option is actually disabled.

quote:Originally posted by LuisMartin

Maintenance plan defaul is update statistics 10%.
Do you change this value to 100%?



Luis Martin

...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
Bertrand Russell

I guess I had the same problem. I ran sp_updatestats (as suggested by people in here). This really works. When you first create a table, SQL Serve starts gathering statistics on it and uses it to answer the queries. SQL Server maintains this statistcis on each table and when user run queries, SQL Server uses this statistics to guess the number of rows to be retrieved by the query. Hence more accurate the statistics are, more fast the query execution is. This statistics get out-of-date gradually even if you set the autostatistic mode on (my personal experiance). So it's better to keep statistics updated from time to time.

Cheers...