May 4, 2018 at 5:07 am
Hello,
I have two SQL 2016 Always On nodes - (VMware Virtual Machines). Each node has 250GB RAM, 46 vCPU @2.5GHz. Each of the MS SQL VM is dedicated to 1 ESXi Host so there is no resource contention.
The application that would connect to the Database is expected to be: 70% WRITE and 30% READ.
I need advise on the best way to:
(1.) Configure MS SQL nodes to use the 250GB RAM, 46 vCPU efficiently and optimally.
(2.) Perform Write and Read as fast as possible using all the hardware resources.
(3.) I have configured Always On Read Routing, how can I test it?
Thanks.
May 4, 2018 at 5:38 am
Hi,
use min max memory settings to use much ram as possible. But let some ram for the os , I think you shold set the max memory to 230 GB.
How are your hdd settings in the vm. Are they using ssd, sata or what kind of storage. Is it possible, to use more than one data file?
Are you working with a lot of transactions? LDF files are sequential, so you need very fast disks, and you are not able, to use more the one logfile to get more speed.
Best regards,
Andreas
May 4, 2018 at 5:52 am
I stored everthing on one drive (system files, database files and log files). The storage is a SAN (EMC VNX 5800 series).
The application is .NET-based. DotNetNuke (DNN v9) to be specific.
May 4, 2018 at 6:29 am
More than one drive and more than one drive controller would be better. SQL Server is IO intensive.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 4, 2018 at 7:16 am
dayorsumm - Friday, May 4, 2018 5:07 AMHello,I have two SQL 2016 Always On nodes - (VMware Virtual Machines). Each node has 250GB RAM, 46 vCPU @2.5GHz. Each of the MS SQL VM is dedicated to 1 ESXi Host
You've dedicated a whole ESX host to running a sql server virtual machine, not once but twice?
dayorsumm - Friday, May 4, 2018 5:07 AM
so there is no resource contention.
So where do the datastores sit that house your VM virtual disks?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
May 4, 2018 at 7:24 am
I have two SQL 2016 Always On nodes - (VMware Virtual Machines). Each node has 250GB RAM, 46 vCPU @2.5GHz. Each of the MS SQL VM is dedicated to it's own ESXi Host.
Meaning that I have two ESXi hosts, each having one VM.
Host Resources
RAM = 256 GB
CPU = 48
VM Resources
RAM = 250 GB
CPU = 46
May 16, 2018 at 8:43 am
As mentioned in earlier post, having multiple disk controllers is better for SQL Server. It is always good practice to separate your OS files, SQL Data files, Log files, TempDB and any local backups.
As an example:
C:\ - OS Files
D:\ - SQL DATA Files
L:\ - SQL Log Files
T:\ - SQL TempDB Files (both data and log files)
X:\ - Backup files (You need this if you are taking local backups of database)
May 17, 2018 at 8:18 am
1) You're really not gaining much by putting a SQL Server of this size on a VM. Since you've got the hardware and you're already dedicating the Hosts to the Servers, then what is the point of using a hypervisor? It seems unlikely that you're going to be moving these Servers around to lots of different Hosts. Especially since you'd have to move every Guest off that Host to balance the resources. Might just as well get rid of the abstraction layer.
2) You should Never build a Database Server with all of the files on a single drive. As stated by others before, split things into different drives Your disk contention will kill your performance for sure. While it is not necessary, I even split off the SQL Program Binaries onto their own drive.
For example:
C:\ - OS and shared components
P:\ - Program Files
D:\ - User Databases
L:\ - User Logs
S:\ - System Databases (Master, MSDB, etc.)
T:\ - TempDB Data
X:\ - TempDB Logs
Z:\ - Backups
The Drive letters are not all that important, really. You can use whatever makes the most sense to you.
3) If your SAN supports it and can be configured with different RAID configurations, you should ensure that your User Database and User Logs are on the fastest capable RAID. For Data I would recommend/utilize a RAID 10 configuration and for Logs I would recommend/utilize a RAID 1. That will ensure that you have the fastest read/write capabilities. I'm no SAN expert, but I've not always bought into the idea that the read/write cache can make up the difference between the different RAID options.
4) As someone else stated above, tune your minimum and maximum memory for SQL Server. The maximum setting should leave enough "head space" for anything else that runs on this Server, particularly the OS. In addition, if you are running any SSIS Packages, DTEXEC.exe will chew up memory resources. Better to leave set a slightly lower maximum memory setting if that is the case. With 250 GB RAM, I'd start off with a max setting of 230 GB for SQL Server and a minimum setting of 200 GB. If there are SSIS Packages make the max setting 215 GB. In all cases, watch how SQL Server performs and adjust as necessary.
5) People often overlook the Max Degree of Parallelism (MaxDOP). The default value is "0" meaning that SQL Server manages this configuration, which might be fine. However, I'd rather limit it to the max core count in a single NUMA Node to ensure that processes are not needlessly spanning memory or in the case of a single NUMA Node it should be 1/2 (half) the number of processors. Since your server is virtual you'll have to look at the underlying configuration to make this setting. If I have a 2 socket 4 vCPU configuration the MaxDOP setting would be 4.
Those are the settings I would check.
Regards, Irish
May 18, 2018 at 11:55 am
Jeffrey Irish - Thursday, May 17, 2018 8:18 AM1) You're really not gaining much by putting a SQL Server of this size on a VM. Since you've got the hardware and you're already dedicating the Hosts to the Servers, then what is the point of using a hypervisor? It seems unlikely that you're going to be moving these Servers around to lots of different Hosts. Especially since you'd have to move every Guest off that Host to balance the resources. Might just as well get rid of the abstraction layer.2) You should Never build a Database Server with all of the files on a single drive. As stated by others before, split things into different drives Your disk contention will kill your performance for sure. While it is not necessary, I even split off the SQL Program Binaries onto their own drive.
For example:
C:\ - OS and shared components
P:\ - Program Files
D:\ - User Databases
L:\ - User Logs
S:\ - System Databases (Master, MSDB, etc.)
T:\ - TempDB Data
X:\ - TempDB Logs
Z:\ - Backups
The Drive letters are not all that important, really. You can use whatever makes the most sense to you.3) If your SAN supports it and can be configured with different RAID configurations, you should ensure that your User Database and User Logs are on the fastest capable RAID. For Data I would recommend/utilize a RAID 10 configuration and for Logs I would recommend/utilize a RAID 1. That will ensure that you have the fastest read/write capabilities. I'm no SAN expert, but I've not always bought into the idea that the read/write cache can make up the difference between the different RAID options.
4) As someone else stated above, tune your minimum and maximum memory for SQL Server. The maximum setting should leave enough "head space" for anything else that runs on this Server, particularly the OS. In addition, if you are running any SSIS Packages, DTEXEC.exe will chew up memory resources. Better to leave set a slightly lower maximum memory setting if that is the case. With 250 GB RAM, I'd start off with a max setting of 230 GB for SQL Server and a minimum setting of 200 GB. If there are SSIS Packages make the max setting 215 GB. In all cases, watch how SQL Server performs and adjust as necessary.
5) People often overlook the Max Degree of Parallelism (MaxDOP). The default value is "0" meaning that SQL Server manages this configuration, which might be fine. However, I'd rather limit it to the max core count in a single NUMA Node to ensure that processes are not needlessly spanning memory or in the case of a single NUMA Node it should be 1/2 (half) the number of processors. Since your server is virtual you'll have to look at the underlying configuration to make this setting. If I have a 2 socket 4 vCPU configuration the MaxDOP setting would be 4.
Those are the settings I would check.
Thanks for the information.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply