September 6, 2017 at 11:21 pm
Hi ,
I am using SQL server 2014 with below processor configuration for my EDW environment.
4 physical processors with 15 cores in each processor with 30 logical processors.
SQL Server detected 4 sockets with 15 cores per socket and 30 logical processors per socket, 120 total logical processors; using 120 logical processors based on SQL Server licensing.
My server CPU never goes higher than 30 percent in when peak batch job loads are running. We decided reduce our CPU configuration and I ran perfmon for 1 week and Processor (% process time) maximum value 43.5 %.
Please suggest how to taken a decision now to reduce my CPU?
September 7, 2017 at 12:50 am
There is no such direct recommendation for CPU utilization but you can check the performance using a monitoring tool, PerfMon or other SQL monitoring tools.
What is the RAM allocated and usage.
What is the size of DB , connections count and usage pattern analyze allthese things and come to a decision, i hope this should have done before allcoating a server.
thanks
Regards
Durai Nagarajan
September 7, 2017 at 2:17 am
durai nagarajan - Thursday, September 7, 2017 12:50 AMThere is no such direct recommendation for CPU utilization but you can check the performance using a monitoring tool, PerfMon or other SQL monitoring tools.What is the RAM allocated and usage.
What is the size of DB , connections count and usage pattern analyze allthese things and come to a decision, i hope this should have done before allcoating a server.thanks
512 GB RAM allocated to the server and SQL max memory limit set to 422 GB and even we are testing this machine with lower memory limits.Total size of DB is 6 TB.and connections will be less than 50.
September 7, 2017 at 9:22 am
Low CPU usage (regardless of core count) is almost ALWAYS attributable to two things. Not enough RAM (common) and not enough IO bandwidth (almost ubiquitous in my 20+ years of consulting experience).
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
September 7, 2017 at 9:23 am
BTW, are you seriously saying you want your CPU utilization to be LOWER?? I sure hope I misread that.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
September 7, 2017 at 10:04 am
TheSQLGuru - Thursday, September 7, 2017 9:23 AMBTW, are you seriously saying you want your CPU utilization to be LOWER?? I sure hope I misread that.
it looks like the OP is saying they over-provisioned CPU for this server, and wanted recommendations for reducing the number of CPU cores they need to license. 60 physical cores does seem like a lot.
September 8, 2017 at 8:51 am
Chris Harshman - Thursday, September 7, 2017 10:04 AMTheSQLGuru - Thursday, September 7, 2017 9:23 AMBTW, are you seriously saying you want your CPU utilization to be LOWER?? I sure hope I misread that.it looks like the OP is saying they over-provisioned CPU for this server, and wanted recommendations for reducing the number of CPU cores they need to license. 60 physical cores does seem like a lot.
Yes we want to reduce the number of cores to half i.e some 30 cores or less than that because of licensing cost.
September 8, 2017 at 9:08 am
TheSQLGuru - Thursday, September 7, 2017 9:22 AMLow CPU usage (regardless of core count) is almost ALWAYS attributable to two things. Not enough RAM (common) and not enough IO bandwidth (almost ubiquitous in my 20+ years of consulting experience).
Hi Kevin,
We have 512 GB RAM and what ever we allocate(420 GB) to SQL it is consuming fully.I have captured several counters related to Memory and CPU using Perfmon for a week.
IO bandwidth issue we are facing since beginning as Data warehouse more related bulk reads and writes.We are using SSD disks.Can you guide me how to reduce this IO bottleneck in EDW environment(Formatted my disks with 64 KB too).
Also suggest what is the best mechanism to estimate sufficient memory for my current process.My management have been asked for Memory consumed by SQL and also they want me to reduce the memory along with CPU when we are planning to migrate to SQL Server 2016(We are planning to move from standalone to cluster and WIN 2008R2 to 2016 and SQL 2014 to SQL 2016).
I am keep reducing memory for SQL server and checking whether loads taking more time/failing due to any memory issues.
September 8, 2017 at 9:23 am
IIRC (and I NEVER give clients on-the-record advice about licensing due to the inherent liability of doing that) if a server has N cores you MUST license ALL of them even if you don't use them for SQL Server (by using affinity for example). This may vary depending on what type of license/agreement you have in place.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
September 8, 2017 at 9:59 am
Is this a virtual machine, or physical?
If it's virtual, then you can assign fewer cores to it in the VM configuration.
If it's physical though, short of removing (physically) some processors, I don't know of any way to reduce the core count, and SQL MUST be licensed for all visible cores.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 8, 2017 at 10:17 am
Wen we are moving to new server and planning to reduce no of cores as license is based on no of cores.
September 8, 2017 at 1:03 pm
prem.m38 - Friday, September 8, 2017 10:17 AMWen we are moving to new server and planning to reduce no of cores as license is based on no of cores.
If physical just check Glenn Berry's recommendations for what hardware to get for your SQL Server workload (including EXACT CPU MODELS!!) and buy a server with fewer cores. If virtual just provision one with fewer.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply