Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Analyzer
Posting Yak Master
115 Posts |
 Posted - 2015-04-27 : 10:01:24
|
| Been told need to drop the no. physical core (CPU) count on 48 CPU ss2012 server box due to licensing costs.Apart from Scale-In techniques to improve workload efficiencies are there other techniques that can be used to maintain performance and remove some CPUs. E.g. would buffer cache extensions help on a ss2014 box to remove some CPU cores. NB: New box a still awaiting to baseline the workload so at time of writing undetermined the what the CPU utilisation will even be. Thanks in advance |
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2015-04-27 : 14:01:27
|
| Do you know the current pressure points? Are the CPus being overused etc? Have you analysed the highest impact queries use this process?http://www.sqlserver-dba.com/2012/11/sql-server-find-high-impact-queries-with-sysdm_exec_query_stats.htmlQuite often high CPU can be a symptom of other issues i.e waiting for other resources etcJack Vamvas--------------------http://www.sqlserver-dba.com |
 |
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2015-04-27 : 14:13:36
|
| 1. Resource governor https://technet.microsoft.com/en-us/library/bb933866%28v=sql.105%29.aspxor2. stop the machine, remove the extra CPUs, restart the machine. |
|
|
|
Analyzer
Posting Yak Master
115 Posts |
Posted - 2015-04-28 : 05:53:57
|
| Thanks for the responses.Predecessor has spec'd up a 48 CPU sql server and was unaware this could not be license by the (MSDN/Cal)leaving the management with a colossal bill. Been asked to reduce the CPU count although we have not started to baseline the workloads on this new ODS machine. So my question was more theoretical from an engineering perspective. Take away 'costly' CPU's, can we boost through-put else where from a hardware perspective. Otherwise all I can do is tune the hell out the workloads to run across fewer CPUs'. Scale-In, Scale-up, Scale-Out. |
|
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2015-04-29 : 01:34:15
|
| @Analyzer - Over specifying the cpu count is a common problem as licensing moves towards core based licensing. A few potential ideas include: 1)If the hardware is a host for virtual guests - you could assign less cores to the VM and declare sub capacity2)Distribute onto servers with less cores.Otherwise as you say - tune \ redistribute the workloads. If you move SQL Servers onto smaller hosts - then you may need to think about scaling techniques - i.e how do you deal with growth . Some ideas : http://www.sqlserver-dba.com/2011/01/sql-server-scaling-techniques.htmlJack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
|
ScottPletcher
Aged Yak Warrior
550 Posts |
Posted - 2015-04-29 : 18:06:27
|
| You can force SQL to use only certain processor numbers using either ALTER SERVER CONFIGURATION (the newer way) or sp_configure (the older way). Afaik there is no other way to prevent SQL from using all available processors. |
|
|
|
Analyzer
Posting Yak Master
115 Posts |
Posted - 2015-05-07 : 08:52:28
|
| Thanks for everyones reply. Additional strategies for reducing the license costs overheads are:1\ If you have SA (software assurance) you can upgrade a SQL instance and be limited to 20 cores max (I have 48 to consider)2\ Buy fewer/less cores but faster CPUs and clock speeds3\ Tune the workload to comfortably run on less CPUs4\ Migrate from bare-metal to VMs. 5\ Defer the SA costThanks |
|
|
|
Analyzer
Posting Yak Master
115 Posts |
Posted - 2015-05-08 : 02:55:12
|
| Thanks for everyones reply. Additional strategies for reducing the license costs overheads are:1\ If you have SA (software assurance) you can upgrade a SQL instance and be limited to 20 cores max (I have 48 to consider)2\ Buy fewer/less cores but faster CPUs and clock speeds3\ Tune the workload to comfortably run on less CPUs4\ Migrate from bare-metal to VMs. 5\ Defer the SA costThanks |
|
|
|
|
|
|
Methods to Reduce CPU Count (48) on Server