Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Tuesday, March 13, 2012 4:52 PM
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?
Tuesday, March 13, 2012 6:55 PM ✅Answered | 1 vote
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.
Wednesday, March 14, 2012 8:52 AM ✅Answered
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
Tuesday, March 13, 2012 5:32 PM
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
Tuesday, March 13, 2012 5:46 PM
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
Wednesday, March 14, 2012 2:08 AM
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 :)
Wednesday, March 14, 2012 8:06 AM
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