Thread: Perf Tuning question - gurus only

1. Registered User
Join Date
May 2003
Location
UK
Posts
220

Unanswered: Perf Tuning question - gurus only

Howdy

I have a server that has the following average readings :

No. CPUs = 1
% CPU = 2
System\CPU Queue Length = 2 to 4
SQL Server:Buffer Manager\Buffer Cache = 99.85%
RAM in the box = 1 GB
Memory\Pages/sec = 1 to 5
SQL memory in use ( using Task Manager ) = 250 MB
Average number of connection = 60

So...........all indicators are that the CPU is idling, there is way enough RAM but we still have a ( in theory ) a congested CPU as the queue length is over 2 consistantly. Thing is, I need to work out if the CPU isnt working hard as the queue is long, or whether we can put extra databases/load on the box.

As the max worker threads are greater than number of connections ( 60 vs 255 ) we could reduce these as the number of users doesnt seem to alter much. BUT.......would this make much difference as if the 255-60=195 worker threads arent doing anything much, they shouldnt put any load on the server, right?

Any thoughts much appreciated.

Cheers,

SG.

2. Window Washer
Join Date
Nov 2002
Location
Jersey
Posts
10,322
Hey...leaves me out....but a scan is a scan is a scan....

3. Registered User
Join Date
Oct 2001
Location
Naples, FL
Posts
273
Number of worker threads hardly ever matches number of user connections. You show the max number, what is your actual count of threads being used by SQLserver? You can get this by running perfmon, choosing process 'SQLServr' and looking at thread count.

4. Registered User
Join Date
May 2003
Location
UK
Posts
220
Howdy,

* Thread count ( from TM ) is 34
* Processes ( from within EM ) is 44

Currently the CPU queue length is about 2.3.
Now I did change the max worker threads from 230 to 200 on friday and when I can in first thing this morning the CPU queue length was about 4-5.

Odd, as I thought reducing the max worker threads should reduce the queue given the CPU is only 1% utilisation......

Any thoughts welcome...

Cheers,

SG.
Last edited by sqlguy7777; 02-22-04 at 18:01.

5. Registered User
Join Date
Oct 2001
Location
Naples, FL
Posts
273
The CPU queue length is the current value and does not represent an average. In your original post you have it as an average of 2-4, how long of a period was that and was that the same period where the average CPU% was that low?

http://www.sql-server-performance.com/q&a2.asp

http://www.sql-server-performance.co...r_counters.asp

And there is a new book out entitled "The guru's guide to SQL Server architecture and internals" by Ken Henderson. I am working my way through it right now and it is pretty darn good.

6. Registered User
Join Date
May 2003
Location
UK
Posts
220
Howdy

I looked at those articles you mentioned in your previous post.

Here are some values from Perfmon :

Counter min max average
% Processor Time 0.1 26 2.59
Processor Queue Length 1 9 2.6
Contect Switches/sec 476 4216 545
SP Recompiles/sec 0 1 0.1
CPU % Priv. time 0 26 0.5
Average Disk Queue Length 0 0.2 0.017
% Disk Time 0 5.6 0.6
SQL Target Server Memory 692MB 693MB 693MB
SQL Total Server Memory 605MB 693MB 693MB
Physical RAM (From TaskMan) - 1 GB -
Commit Charge(From TaskMan) - 846MB -
Sqlservr.exe memory usage - 713MB -
Number of user connections - 64 -
Also - the number CPUs in the box is one, but it can take 2.

Any suggestions? As you can see the Processor Queue is still high and the CPU is much the same although I was noticing the CPU was a little more active that before we lowered the max worker threads to 150.

Cheers,
Last edited by sqlguy7777; 02-23-04 at 22:40.

7. Registered User
Join Date
Oct 2001
Location
Naples, FL
Posts
273
Unless I'm missing something the only thing that is high is the CPU queue length but your % processor time isn't high. I think they usually say if both of those are high you have a CPU bottleneck.

8. Registered User
Join Date
May 2003
Location
UK
Posts
220
Howdy

I agree...this is what is doing my head in.....any suggestions?

Cheers,

SG

9. Registered User
Join Date
Jul 2003
Location
San Antonio, TX
Posts
3,662
I also think that you might be mistaking worker threads for number of threads in Task Manager (those two aren't the same!)

I'd leave the default worker threads alone. Their purpose in life is to process client requests. If you start arbitrarily reducing their number, you may get your server to a "thread starvation" state.

10. Registered User
Join Date
May 2003
Location
UK
Posts
220
Howdy

Good point.

Problem is though I still cant come up with an explanation as to why on such a lightly loaded server ( CPU = 1 % average ) would I have such a long CPU queue. Its a PIII 1.4 GHz machine.

Cheers,

SG.

11. Registered User
Join Date
Jul 2003
Location
San Antonio, TX
Posts
3,662
Another info I just found states that the threshold for CPU Queue Length is ">5 sustained over 30 mins" (check this site, though it's for NT 4.0)

12. Registered User
Join Date
May 2003
Location
UK
Posts
220
Howdy

Is there much difference between the NT4 & 2000 kernal architecture? If not, then your article would make sense then.....

Also, does anyone knwo where i can find good DIAGRAMS of how SQL manages memory, CPU queues etc?

Cheers,

SG

13. Registered User
Join Date
Oct 2001
Location
Naples, FL
Posts
273
A good book is "The guru's guide to SQL Server Architecture and Internals" by Ken Henderson - admittedly not through it all yet but it's very informative so far

14. Registered User
Join Date
May 2003
Location
UK
Posts
220
Howdy,

I bought it last night, great if you are a coder, but as a DBA who doesnt really touch DMO & XML ( although I dont know if I am supposed to - I always thought that was the coders thing to do...) it not helped me much for this situation......

It doesnt shed any light on how you have a PIII CPU queue that averages 2-4 with no disk activity, no CPU activity, piles of available RAM....its doing my head in.....maybe the CPU is duff.....is it possible the system idle process is exaggerating the CPU queue?

Any thoughts?

Cheers,

SG

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•