Server not using full processor power

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Server not using full processor power

Author

Topic

Daniel_Buchholz
Starting Member

28 Posts

Posted - 2003-05-07 : 07:10:11

Hello all. Hope somebody can help me on this one:

We have a dedicated SQL Server 2000 Machine running a DB that occasionally gets pretty busy. The server has two processors and the server instance is configured to use both processors. No other settings regarding parallelism have been tweaked.

Currently we see the server not using the full machine power. SQL server pretty much only uses 50% of the processor time available but seems to switch between both processors. So both processors are used.

Is there anything we can do to boost the processor usage?

Thank

Daniel

SamC
White Water Yakist

3467 Posts

Posted - 2003-05-07 : 08:03:12

Could it be I/O bottlenecked at 50% CPU?

Sam

Daniel_Buchholz
Starting Member

28 Posts

Posted - 2003-05-07 : 08:10:39

I don´t think so. sqlserver.exe has 50% all the time during a long running batch. I checked I/O in performance monitor and it was at a maximum of about 6 write operations per second. Nothing that would bring that disc array down I think..

Daniel

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-05-07 : 12:23:39

Is there a performance problem? If not, then maybe SQL Server doesn't need anymore.

Tara

Daniel_Buchholz
Starting Member

28 Posts

Posted - 2003-05-07 : 13:09:22

I tested that.
What strikes me is that the server process is limited to max 50% CPU load. A pretty intensive job was running that way. I started another job in parallel and the server still was at 50%...

Daniel

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-05-07 : 13:16:26

Could you do a print screen on the processor screen for the SQL Server properties (I'm sure you already know how to do this: right click on the server in EM, go to properties, then to processor)? I'm sure that it is configured correctly already, but just want to make sure.

Tara

chadmat
The Chadinator

1974 Posts

Posted - 2003-05-07 : 14:22:00

A single query (Or Job) will only utilize more than one proc if a parallel plan is selected. So the job is probably not selecting a parallel plan, but it is pegging the one proc that it is using.

A Parallel plan is not typically selected by the optimizer unless there are very few connections (Less than the number of procs).

If you have 2 resource intensive jobs running on different connections, they MAY each use a different proc, and then you will see the CPU utilization go up.

-Chad

http://www.clrsoft.com

Software built for the Common Language Runtime.

Daniel_Buchholz
Starting Member

28 Posts

Posted - 2003-05-08 : 03:15:51

Here is the server config:

quote:


affinity mask	-2147483648	2147483647	0	0
allow updates	0	1	0	0
awe enabled	0	1	0	0
c2 audit mode	0	1	0	0
cost threshold for parallelism	0	32767	0	0
cursor threshold	-1	2147483647	-1	-1
default full-text language	0	2147483647	1031	1031
default language	0	9999	1	1
fill factor (%)	0	100	0	0
index create memory (KB)	704	2147483647	0	0
lightweight pooling	0	1	0	0
locks	5000	2147483647	0	0
max degree of parallelism	0	32	0	0
max server memory (MB)	4	2147483647	1118	1118
max text repl size (B)	0	2147483647	65536	65536
max worker threads	32	32767	300	300
media retention	0	365	0	0
min memory per query (KB)	512	2147483647	1024	1024
min server memory (MB)	0	2147483647	435	435
nested triggers	0	1	1	1
network packet size (B)	512	65536	4096	4096
open objects	0	2147483647	0	0
priority boost	0	1	1	1
query governor cost limit	0	2147483647	0	0
query wait (s)	-1	2147483647	-1	-1
recovery interval (min)	0	32767	0	0
remote access	0	1	1	1
remote login timeout (s)	0	2147483647	20	20
remote proc trans	0	1	0	0
remote query timeout (s)	0	2147483647	600	600
scan for startup procs	0	1	0	0
set working set size	0	1	0	0
show advanced options	0	1	1	1
two digit year cutoff	1753	9999	2049	2049
user connections	0	32767	0	0
user options	0	32767	0	0

@chadmat
There are a few people working on the database while the job is running and I have never seen the proc usage go over 50%...

Daniel

chadmat
The Chadinator

1974 Posts

Posted - 2003-05-08 : 11:49:51

But is each person doing something resource intensive that would "peg" the processor (At the same time)?

As a test, I would kick off the "Long running batch" you referred to above (Or something similarly resource intensive) on 2 seperate connections, and see what the CPU utilization goes to.

-Chad

http://www.clrsoft.com

Software built for the Common Language Runtime.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources