martes, 13 de marzo de 2012 16:52I 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?
Todas las respuestas
martes, 13 de marzo de 2012 17:32
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
martes, 13 de marzo de 2012 17:46
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
martes, 13 de marzo de 2012 18:55Following 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.
miércoles, 14 de marzo de 2012 2:08
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.
Thanks, Satish Kumar. Please mark as this post as answered if my anser helps you to resolves your issue :)
miércoles, 14 de marzo de 2012 8:06
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.
miércoles, 14 de marzo de 2012 8:52
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.