How to configure the number of CPU used by SQL Server

Question

I have a windows 2008 server. Now I need to install SQL server 2008 on this box. The box has 8 physical and 32 logical CPU. While installing SQL Server is there anyway I can configure that SQL Server will take maximum 7 physical and 28 logical CPU and widows will use rest of the CPU? Or will SQL server use all the physical and logical CPU available on the server? 

Answer 1

Hi

From Management Studio, connect to the SQL Server then right click over it and choose Propierties.

Uncheck "Automatically set . . ."  and then select the amount of logical CPUs you want to use 

---

Javier Villegas | @javier_vill | http://sql-javier-villegas.blogspot.com/

Please click "Propose As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you

Answer 2

Thanks for your reply. It is really helpful. Is there any T-SQL query which tells me how many CPU is used by SQL and how many CPU used by Windows? I found following query but i guess it shows just number of physical CPU used by SQL but not by both windows and SQL.

SELECT  cpu_count / hyperthread_ratio AS physical_cpu_sockets
FROM    sys.dm_os_sys_info

Answer 3

Following query will confirm how many CPUs SQL Server is using - 

select scheduler_id,cpu_id, status, is_online from sys.dm_os_schedulers where status='VISIBLE ONLINE'

Following query lists how many CPUs a particular instance of SQL Server can see - 

select cpu_count from sys.dm_os_sys_info

You should also understand parallelism and its configuration options.These settings should be set properly after analyzing the work load.


http://msdn.microsoft.com/en-us/library/ms181007.aspx

---

Anup | Database Consultant

Blog: www.sqlsailor.com Twitter: Follow me !

Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

Answer 4

In addition to the above, please do concentrate on the below links before doing on cpu configuration...

http://msdn.microsoft.com/en-us/library/ms143760(v=sql.100).aspx  -- to check number of processes suppoerted by sql server editions.

The below links are about NUMA.

http://msdn.microsoft.com/en-us/library/ms180954(v=sql.100).aspx

http://msdn.microsoft.com/en-us/library/ms178144(v=sql.100).aspx

http://msdn.microsoft.com/en-us/library/ms345357(v=sql.100).aspx

http://msdn.microsoft.com/en-us/library/ms180954.aspx

Thanks,

---

Thanks, Satish Kumar. Please mark as this post as answered if my anser helps you to resolves your issue :)

Answer 5

The questions are already answered by the experts but just to summarize,

1. First of all we need to clarify if we can use all the 8 physical processors. As per the article shared by Satish : - http://msdn.microsoft.com/en-us/library/ms143760(v=sql.100).aspx , if we have enterprise/developer edition then only we can make use of all the "physical" processors which the OS can see.

2. Once we are sure about the no. of processors which can be used, we can use affinity mask(http://msdn.microsoft.com/en-us/library/ms187104.aspx)  to setup the no. of processors which can be used by SQL server or we can follow the GUI as mentioned by Javier.

---

Kind regards| Harsh Chawla | Personal Blog:- SQL-blogs
|Team Blog:- Team Blog

Answer 6

What is your goal here? Are you suggesting that you want to ensure there is at least one physical CPU for use by the OS only?

Unless you have a specific proven need to do this I would suggest leaving the default configuration in place. SQL Server will not consume all CPU resource and starve the OS. A cooperative model is used I believe ensuring that the OS can always access CPU resource because SQL Server will concede to it.

---

John Sansom | SQL Server DBA Blog | @JohnSansom on Twitter