SQL Server on dual CPU

Question

SQL Server on dual CPU

tlitschko

Old Hand

Points: 351
More actions
April 27, 2005 at 5:33 am

#110374

Hello Everybody,
I am experiencing a problem with my SQL Server here.
First something for the background:
System: Win2003 Server
SQL: MSSQL2000 Standard Edition SP3
CPU: Dual Pentium 4 3000Mhz, HT activated
Problem: On certain actions my Server doesn´t perform as wanted. When I monitor the server´s task manager I see it working on 100% on one of it´s 4 virtual cpus (2 real CPUs in Hyperthreading Mode). All the others are quite lazy.
Could you please tell me, how I can get the Server to work with all its CPUs or at least with two of them?
The Prozessor-Settings of SQL Server are as follows:
All 4 CPUs are checked, the two boxes "higher priority" and "use Win NT Fibres" are unchecked, The Radiobutten "use all availlable CPUs" is set, minimum number of parallel questions is set to 5.
That is the default setting I think.
I would be very thankful if anybody could tell me what to change to get all my CPUs working.
Thanks in advance.
Greetings
msteffel.

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

philcart SSC-Forever Points: 47797 More actions · Answer 1

What "certain actions" cause the 100% usage?

Bear in mind that if the operation you're trying to run can't be split safely amongst the processors SQL Server will only use one processor. Read up on parallelism in Books Online.

--------------------
Colt 45 - the original point and click interface

msteffel SSC Rookie Points: 45 More actions · Answer 2

Sorry for posting with another username now, I was on my partner´s computer before and forgot to log off.

The "certain action" is a trigger updating a table with about 2000000 entries.

The problem is if the trigger fires it has to do a few thousand new entries at a time.

Sorry, but I cannot go into further details as I don´t know them.

Are the settings I described in my post above correct to have the server run on many cpus or did I miss out something?

Thanks again.

Greetings

msteffel.

DCPeterson SSCoach Points: 15266 More actions · Answer 3

Although SQL Server can split work in a single transaction over multiple processors, it is often more efficient NOT to. There is quite a bit of overhead incurred in coordinating work across processors. The good news is that while the one processor is pegged, the other is able to take any other work that might be requested.

I would check the logic of that trigger though and make sure it is absolutely necessary, and also that it is optimized. I've seen plenty of triggers that caused unintended problems...

/*****************

If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek

*****************/

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 4

You should be aware that the ability to use multiple processors is more limited in std edition.

You might want to monitor processes to see what is using cpu - I run with all multi-proc boxes and rarely ever see this type of thing from sql server, an extrenal process ( which usually shouldn't be running ) such as defrag manager or some such may skew cpu usuage.

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/