VMware Cloud Community
Nakke201110141
Contributor
Contributor
Jump to solution

vCPU and MS SQL 2012

Hi,

Could someone tell me how should I set the vCPU settings.

I will install this machine in 3 virtual servers. 1. Win 2012 Server + MS SQL 2012 server which is the most important and toughest use. 2. Win 2008 DC (low stress). 3. Win 2008 FTP (low stress).

server.JPGmem.JPG

vcpu.JPG

I think, I will give servers 2. and 3. 1vCPU and 1core. And 4-8GB mem per server.

But how should I configure SQL Server to obtain the best performance?

JM

Reply
0 Kudos
1 Solution

Accepted Solutions
DavoudTeimouri
Virtuoso
Virtuoso
Jump to solution

Hi,

First, check you current configuration with new software and if the new software has poor performance on the VM, then add core to that.

About memory, try to start with minimum recommendation and then add more memory till performance is not good for you.

Finally about vCPUs, there is no different between 2/2 or 1/4 because ESXi 4 cores to your VM but it's different for guest OS because guest OS see sockets and cores.

You should consider it for your application licensing, for example some applications license is per socket and you need to buy license for each socket.

I recommend, add 1 socket and 4 cores to your new VM.

-------------------------------------------------------------------------------------
Davoud Teimouri - https://www.teimouri.net - Twitter: @davoud_teimouri Facebook: https://www.facebook.com/teimouri.net/

View solution in original post

Reply
0 Kudos
6 Replies
DavoudTeimouri
Virtuoso
Virtuoso
Jump to solution

Hi,

Assigning vCPU is depended to your available cores on your host, your VM workload and also installed software on your VM.

You have 6 physical cores and with hyper-threading 12 cores, so for best performance, you can assign 12 cores to VMs totally. (1:1 physical).

About SQL performance, you need to read this document from VMware: http://www.vmware.com/files/pdf/solutions/SQL_Server_on_VMware-Best_Practices_Guide.pdf

And also see this article:http://kb.vmware.com/selfservice/microsites/search.do?language=en_US&cmd=displayKC&externalId=100295...

Run SQL on a Virtual Machine with VMware Virtualization | United States

-------------------------------------------------------------------------------------
Davoud Teimouri - https://www.teimouri.net - Twitter: @davoud_teimouri Facebook: https://www.facebook.com/teimouri.net/
JPM300
Commander
Commander
Jump to solution

Hey Nakke,

Like DavoudTeimouri pointed out you have a 1 socket 6 core CPU on your ESXi host with hyper-threadding enabled.  This means the max CPU you can use on a host is 12vCPU.  Now I wouldn't go this far and like Davoud mentioned the number of vCPU all comes down to what kind of load you will be putting on this CPU.  Do you have any benchmarks or general load numbers maybe from a physical SQL server that your migrating from?

None the less I usally start low and work my way up as reclaiming CPU / Memory is harder then giving :smileysilly:  Plus if you are running Windows Server 2008 Ent or higher youc an hot add CPU / Memory if you enabled it on the VM.

With that said you I would probably start with 2-4vCPU with 8GB of memory and work your way up from there.  Keep in mind SQL will cache as much memory as it can get unless you cap it in SQL Studio Manager so expect this, however this doesn't mean SQL is actively using what is has cached, its just grabbing it incase it "needs" to use it.  You will need to look at other SQL metrics to see what its actively paging.


Another free tool you can use to benchmark your SQL server is Dells Benchmark Factory.  This has all the industry standard tests built into it and allows you to run stress tests and compare stats.  We did this recently with a SQL migration from physical to virtual to messure the stats on the physical host, then P2V'ed that exact system and put it back on its hardware once we installed ESXi 5.5 on it, then re-ran the tests.  The overall metrics we saw in that particular enviroment was about a 10% loss, and we noticed that 8vCPU got us the best performance.  However this was on a duel socket 8 core server with oodles of memory. 

For your low stress systems 1-2vCPU is fine.  For AD controllers I rarely ever add more then 1vCPU unless its servers 1000's of users.  FTP same deal, unless its server large amount of users or pinning the CPU I keep it low.  The less vCPU's you give all your VM's the easier the CPU schedulers job is in the hypervisor

Going back to the its hard to get resources back topic I created this little picture to help people understand :smileysilly:

First World Problems - VMadmin - Imgur

I hope this has helped.

vfk
Expert
Expert
Jump to solution

SQL can be resources intensive, don't be tempted to scale up as this will cause some headaches, where possible scale out, and be careful with your over commitment ratios and don't over commit memory too much because swapping will hit performance hard as SQL caches in memory.

--- If you found this or any other answer helpful, please consider the use of the Helpful or Correct buttons to award points. vfk Systems Manager / Technical Architect VCP5-DCV, VCAP5-DCA, vExpert, ITILv3, CCNA, MCP
Reply
0 Kudos
JPM300
Commander
Commander
Jump to solution

Much like vfk said, also when it comes to SQL, Exchange, TS servers, ect as a VM it is typically a better idea to scale out instead of up as it is much more cost effective.  So if you start hitting a performance wall on your SQL VM as far as vCPU and memory is concered it would probably be a better idea to build out another SQL VM and split the load out.  It;s not to say you can't scale up but it will cost a lot more in most cases as you have to isolate hosts, buy BIG hosts to allow for the CPU/Mem allocation ect.  Like vfk said Scaling out large workloads into smaller VM's is the way to go when virtualizing large workload systems like SQL, Exchange, TS, ect.

Reply
0 Kudos
Nakke201110141
Contributor
Contributor
Jump to solution

Thanks a lot for the answers.

Currently, we have a physical windows 2008 std server + SQL 2008:

T610.JPG

T610 mem.JPG

The majority of users are now on holiday and I do not see the actual load, but the server speed hasn't been excellent, but enough. Effective SQL/ERP users is only 16, but they use the system effectively. Database size is 34GB.

Next month we will have a new wider version of ERP and it also requires more performance. New system will be Windows Srv 2012R2 Standard + SQL 2012, so I can't hot add CPU?

ERP vendor says that the minimum memory is 16GB and the 32GB or more is recommendation. So probably the 32GB is ok?

I think that 4 vCPU is ok? But should I set virtual socket:1 and cores per socket:4, or 2/2 or 4/1?

Nakke

Reply
0 Kudos
DavoudTeimouri
Virtuoso
Virtuoso
Jump to solution

Hi,

First, check you current configuration with new software and if the new software has poor performance on the VM, then add core to that.

About memory, try to start with minimum recommendation and then add more memory till performance is not good for you.

Finally about vCPUs, there is no different between 2/2 or 1/4 because ESXi 4 cores to your VM but it's different for guest OS because guest OS see sockets and cores.

You should consider it for your application licensing, for example some applications license is per socket and you need to buy license for each socket.

I recommend, add 1 socket and 4 cores to your new VM.

-------------------------------------------------------------------------------------
Davoud Teimouri - https://www.teimouri.net - Twitter: @davoud_teimouri Facebook: https://www.facebook.com/teimouri.net/
Reply
0 Kudos