SQL Schedulers disabled

07 January,2014 by Tom Collins

When SQL Server uses a portion of the server processors , the is_online column on the sys.dm_os_schedulers will return 0.

 Basically , the scheduler will not be used for query processing. The situation is caused by either affinity masking or licensing restrictions

 Be careful if you attempt to change these number – either be confident you know how to use affinity masking or if Virtualization is used , speak to the VM administrator about apportioning more cores per virtual socket. In either case analyse the impact and test thoroughly

 Read more on Amount of sql server processors with sys.dm_os_schedulers

--how many CPUs is SQL Server actually using
select cpu_id,scheduler_id,is_online,status from sys.dm_os_schedulers where status = 'VISIBLE ONLINE'

--how many CPUs are not being used
select cpu_id,scheduler_id,is_online,status from sys.dm_os_schedulers where status = 'VISIBLE OFFLINE'

--how many CPUs an instance can see
select cpu_count from sys.dm_os_sys_info

Read More

SQL Server – SOS_SCHEDULER_YIELD and how to reduce it

Author: Tom Collins (http://www.sqlserver-dba.com)


Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.


Post a comment on SQL Schedulers disabled

sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer