Is sql server using all cpus?

Question

Is sql server using all cpus?

forsqlserver

SSCoach

Points: 18902
More actions
March 19, 2012 at 7:19 am

#270132

In sql server 2008 R2 features supported list Enterprise edition is only supported 8 CPU:
http://msdn.microsoft.com/en-us/library/cc645993.aspx
but in my environment sql server has 16 cpu.
Is sql server using all cpus?
Thanks

Viewing 14 posts - 1 through 13 (of 13 total)

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

Grant Fritchey SSC Guru Points: 398872 More actions · Answer 1

It's only going to use 8 at a time. The OS may point it to different ones at different times, but SQL Server won't hit all 16.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

forsqlserver (3/19/2012)
but in my environment sql server has 16 cpu.

You have a server with 16 physical CPU sockets on the motherboard? That's not a core limitation, it's a processor limitation (and it's just a licensing one, like with Standard and 4 sockets).

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

forsqlserver SSCoach Points: 18902 More actions · Answer 3

forsqlserver

SSCoach

Points: 18902

March 19, 2012 at 10:35 pm

#1461438

Thanx.

Thanks

HowardW One Orange Chip Points: 29109 More actions · Answer 4

I think Gail's right here, there's virtually no off the shelf hardware that supports 16 sockets. Are you sure you don't mean you have 16 cores? E.g. 4 sockets with quad core processors, or even 2 sockets with quad core processors with hyper-threading enabled?

forsqlserver SSCoach Points: 18902 More actions · Answer 5

How to check this?

2 sockets with quad core processors with hyper-threading enabled

Thanks

HowardW One Orange Chip Points: 29109 More actions · Answer 6

The basic information can be found from right clicking on Computer/My Computer and going to properties, then reading the Processor information.

You can get more detailed information from something like CPU-Z if you need.

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 7

Using WMI oyu can check this. When I do this manually I like to use an app called WMI Explorer.

If you look at the class Win32_Processor it will list one item for each physical processor, so two entries = 2 processors. Within each entry there are two properties of interest they are: NumberOfCores and NumberOfLogicalProcessors.

This together tells you physical processor count, cores for that processor, and number of logical procs (hyper threads).

So take this, 2 entries, number of cores: 2 (in each entry), number of logical processors 4 (in each entry). We can say you have a dual processor each with dual cores with hyperthreading.

CEWII

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 8

yet another way to find out in 2008 R2

---

--- show_logical_and_physical_cpus.sql

---

use master

go

---

select cpu_count / hyperthread_ratio as 'Sockets',

cpu_count as 'Logical CPUs'

from sys.dm_os_sys_info

---

select count(*) as 'Logical CPUs configured online'

from sys.dm_os_schedulers

where status = 'VISIBLE ONLINE'

---

select count(*) as 'Logical CPUs configured offline'

from sys.dm_os_schedulers

where status = 'VISIBLE OFFLINE'

---

--- end of show_logical_and_physical_cpus.sql

---

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

eforta.it SSC-Addicted Points: 454 More actions · Answer 9

forsqlserver (3/20/2012)
How to check this?
2 sockets with quad core processors with hyper-threading enabled

What you've described would show as 16 threads in the task manager - hyper threading enabled does that.

So to answer your question:

a) You only have 2 physical processors. So you aren't hitting the CPU restriction you've described.

b) SQL Server will have access to the 16 threads.

Thanks

Michael

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 10

b) SQL Server will have access to the 16 threads.

is not necessarily correct ... unless 'threads' are are construed to mean 'logical CPUs' or schedulers assigned by SQL Server to Logical CPUs ...

it may be correct if the SQL Server is configured for MAXDOP=1 (Maximum Degree of Parallelism) or a query hint for MAXDOP is used ...

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 11

rudy komacsar - Doctor "X" (3/22/2012)
b) SQL Server will have access to the 16 threads.
is not necessarily correct ... unless 'threads' are are construed to mean 'logical CPUs' or schedulers assigned by SQL Server to Logical CPUs ...
it may be correct if the SQL Server is configured for MAXDOP=1 (Maximum Degree of Parallelism) or a query hint for MAXDOP is used ...

SQL still has acces to all 16, it just may not use all of them in a single query.

CEWII

eforta.it SSC-Addicted Points: 454 More actions · Answer 12

rudy komacsar - Doctor "X" (3/22/2012)
b) SQL Server will have access to the 16 threads.
is not necessarily correct ... unless 'threads' are are construed to mean 'logical CPUs' or schedulers assigned by SQL Server to Logical CPUs ...
it may be correct if the SQL Server is configured for MAXDOP=1 (Maximum Degree of Parallelism) or a query hint for MAXDOP is used ...

Threads appear as logical CPUs in Task Manager which is what the OP was querying.

MAXDOP is about using parallel processing.... You're muddying the waters by playing with semantics!

My point was, if you have 2 physical CPUs, with 4 cores which have Hyper Threading enabled you end up with 16 threads (or logical CPUs if you don't understand what threads are in CPU terms!). SQL has ACCESS to them. And as Elliot rightly pointed out (and which I never implied - you assumed!) - it may not be using them all.

Thanks

Michael

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 13

eforta.it (3/22/2012)
rudy komacsar - Doctor "X" (3/22/2012)
b) SQL Server will have access to the 16 threads.
is not necessarily correct ... unless 'threads' are are construed to mean 'logical CPUs' or schedulers assigned by SQL Server to Logical CPUs ...
it may be correct if the SQL Server is configured for MAXDOP=1 (Maximum Degree of Parallelism) or a query hint for MAXDOP is used ...
Threads appear as logical CPUs in Task Manager which is what the OP was querying.
MAXDOP is about using parallel processing.... You're muddying the waters by playing with semantics!
My point was, if you have 2 physical CPUs, with 4 cores which have Hyper Threading enabled you end up with 16 threads (or logical CPUs if you don't understand what threads are in CPU terms!). SQL has ACCESS to them. And as Elliot rightly pointed out (and which I never implied - you assumed!) - it may not be using them all.

Agreed, MAXDOP just muddies the waters here. The edition being discussed has ACCESS to all the threads of execution the processors have to offer. Whether it may chose to use them all in a particular query is an entirely separate question.

CEWII