sql server cpu schedulers

Question

sql server cpu schedulers

PearlJammer1

SSCrazy Eights

Points: 8477
More actions
November 28, 2014 at 5:12 am

#313412

Hi
We have 4 Physical cpu's, Which are QUAD 2.4GHZ e7450on on an sql box. When I run the below query it returns the results listed:
SELECT cpu_count AS [Logical CPU Count], hyperthread_ratio AS Hyperthread_Ratio,
cpu_count/hyperthread_ratio AS Physical_CPU_Count,
physical_memory_in_bytes/1048576 AS Physical_Memory_in_MB,
sqlserver_start_time, affinity_type_desc -- (affinity_type_desc is only in 2008 R2)
FROM sys.dm_os_sys_info
Logical CPU Count = 4
HyperThread_ratio = 1
Physical_CPU_Count = 4
PhysicalMemory_in_MB = 8191
START TIME = 2014-11-24
Affinity_type_desc = Manual
When I check the schedulers :
select * from sys.dm_os_schedulers
it returns that I have 4 scheduler_id as VISISBLE ONLINE ( 0 - 3 - these also have a corresponding cpu_id with the same numbers ie 0 through 3 )
What I am also seeing is are other sceduler id's but with larger values for the scheduler_id. For example:
scheduler_id = 1048578, cpu_id = 0, HIDDEN ONLINE
scheduler_id = 1048579, cpu_id = 1, HIDDEN ONLINE
Without listing them all here, there are a total of 16 schedulers, 4 showing VISIBLE ONLINE 0 thoough 3, then the rest all HIDDEN ONLINE with one for the DAC as VISIBLE ONLINE.
Do I need to enable the other schedulers to make them available for the cpu to handle queries ? If I have a quad core cpu system with 4 physical cpu's surley i should have 16 logical cpus.
The other reason for this question is that I currently only have 1 TEMPDB file and I need to increase the number to reduce TEMPDB contention - and I believe that the marker for the amount required starts with the logical cpu count.

Viewing 9 posts - 1 through 8 (of 8 total)

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

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

The Hidden Online schedulers are not for query processing. They're for internal stuff or reserved for the DAC or other stuff like that.

What edition of SQL is that?

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

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

PearlJammer1 (11/28/2014)
The other reason for this question is that I currently only have 1 TEMPDB file and I need to increase the number to reduce TEMPDB contention - and I believe that the marker for the amount required starts with the logical cpu count.

It does not.

That it does is a myth based on a not-great Microsoft recommendation from about 10 years ago.

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

PearlJammer1 SSCrazy Eights Points: 8477 More actions · Answer 3

So I should be ok adding 3 more tempdb files of equal size to reduce the tempdb contention issue ?

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

Are you seeing allocation contention (PAGELATCH_UP waits on 2:1:1, 2:1:2 and/or 2:1:3)? Or IO contention (slow IOs in TempDB, PageIOLatch waits in database 2?

What edition of SQL is this?

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

PearlJammer1 SSCrazy Eights Points: 8477 More actions · Answer 5

yes I am seeing the following during our 'slowdown' period :

(20ms)PAGELATCH_UP:tempdb:1(PFS) when I run sp_whoisactive

Also when I query the servers top waits, PAGELATCH_UP accounts for the top wait at 22% of the servers waits stats.

We are running sql2008 standard edition.

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

Add three files to TempDB, make sure they're all the same size. If you still see the latch waits after that, add another 4 files.

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

PearlJammer1 SSCrazy Eights Points: 8477 More actions · Answer 7

thanks a lot Gail - I will make the changes - hopefully it will help

🙂

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 8

You might also want to look into Trace Flags 1117 and 1118.

http://www.brentozar.com/archive/2014/06/trace-flags-1117-1118-tempdb-configuration/

http://www.sqlskills.com/blogs/paul/misconceptions-around-tf-1118/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)