SQL Server on Nutanix - Best pratices

The following taken from :

Microsoft SQL Server 2016 Nutanix Best Practices

Drive configuration:
For optimal performance several virtual disks should be used.  Nutanix suggest eight disks per SQL Server virtual machine spread across  4 SCSI controllers as laid out below:

 

Drive	Controller Type	Controller #
OS	LSI SAS	0
SQL Sever installation	LSI SAS	0
Backup	LSI SAS	0
SQL Datafiles 1	PVSCSI	1
SQL Datafiles 2	PVSCSI	1
TempDB Datafiles 1	PVSCSI	2
TempDB Datafiles 2	PVSCSI	2
TempDB Log Files	PVSCSI	3
Database Log Files	PVSCSI	3

Cluster size formatting of all drives as below:

SQL Data and log files  - 64KB NTFS

OS and SQL Server installation - 4KB NTFS - Server Team ??

Size for at least 20% free disk space on all drives.

Create drives of slightly different sizes to enable identification and correlation to OS level drive naming.

Don't use OS level volume managers.

Data File configuration:

Databases should be split into multiple files across multiple disks.  Nutanix general recommendation is one file per virtual CPU with the files split equally across the disks.  For write-intensive databases there should be a consideration to add extra disks so the files can have a better spread. Datafiles should be sized equally.

Smaller databases with lesser I/O do not necessarily need to follow the above and  can be set up with only one or two datafiles per database. Server team / DB Team

Autogrowth should be set at a value that is reasonable for the projected growth of the database and it is suggested that the data file groups are set to 'AUTOGROWTH_ALL FILES' so that all files grow equally.  With SQL Server 2016 this is already set by default for TempDB.

Do not use autoshrink as this can lead to fragmentation and reduced performance. - DBT Standards

Another Nutanix recommendation for Datafiles is to enable instant file initialisation.  When creating or extending datafiles instead of writing zeros to the entire file a sparse file is created writing data to only the required space.  This can be implemented at installation by checking the 'Grant Perform Volume Maintenance Task privilege to SQL Server Database Engine Service' box - DBT Standards

Log file configuration:

Nutanix recommendation is for a single log file per database.  To improve backup and restore performance log files should be initially set to either 4GB or 8GB and grown by the same amount.  This will ensure that the logs virtual log files will be sized optimally and keep the number of them low.

A recommendation is to size Log Files to the 'high water mark' before the next log truncation  DBT Standards

TempDB datafile configuration:

SQL Server automatically creates TempDB datafiles equal to the number of CPUs up to eight.  The Nutanix recommendation is that for a system with less than eight CPUs configure the number of TempDB datafiles to be equal to the number of CPUs.  For more than eight, eight should initially be created and then files should be added in increments of four at a time should there be any sign of contention for in-memory allocation.

All TempDB datafiles should be equaly sized and not set to autogrow.  Recommendation is for two TempDB database drives and one TempDB log drive. DBT Standards

Memory:

If supported it is beneficial to enable to reserve RAM for SQL Server VMs.  This ensures that the Nutanix Hypervisor does not swap SQL Server memory to disk.  When dealing with Platinum level databases it is recommended that all memory is reserved.  For other levels of support memory reservation could be considered but it might be better to let the VM manage memory allocation.

If available 'Hot Adding RAM' should be utilised to allow the addition of RAM without the need to power cycle the VM.

Although SQL Server handles memory allocation for itself and the underlying OS very well, Nutanix recommends setting a memory cap depending on the amount of memory available.

VM RAM	Max Memory
4 GB	2,048 KB
8 GB	5,120 KB
12 GB	8,192 KB
16 GB	12,288 KB
24 GB	20,480 KB
32 GB	28,672 KB
48 GB	44,032 KB
64 GB	59,392 KB

An additional recommendation is to enable locked pages in memory, this should be definitely  considered if RAM is being reserved.  This is set using the Windows Group Policy tool, the account that runs the SQL Server service should have the 'Lock pages in memory' policy added to it. Server Team / DB Team

CPUs:

Nutanix recommend to only allocate enough virtual CPUs to the SQL Server to handle the projected workload also that 'Hot CPU addition' should be disabled.  Both of these recommendations fits very well with our licensing model.

Server Team

SQL Server Compression:
Not listed as a suggestion in the documentation but just as a note to mention that Nutanix supports SQL Server compression.  It does mention that enabling compression can reduce I/O and also (due to the use of less space ) fit more data in the 'hot tier' SSD storage area.

Sizing:

Sizing the SSD tier to fit  as much, if not all of the database, in it is an optimal recommendation by Nutanix.  Also it is recommended to fit the entire database within the storage capacity of one node.

For heavy I/O databases, use a higher memory node model for additional buffer space and assign more memory to the VM. If possible, size the Nutanix host such that it has at least twice the memory of the largest VM, so that the VM can fit within a single NUMA node.

Nutanix specific technical recommendations:
Enable compression at the container level.

Do not use containe-level deduplication.

Use the fewest possible containers.

Disable shadow clones on AOS 4.0.2.1. and later.

Thin/Thick provisioning ??

Number of data copies ??

Testing of failed nodes ?? Relies on data copies (sync/async) ??

Performance and Scalability Recommendations :

Use multiple data file and drives

• Look for contention for in-memory allocation (PAGELATCH_XX), if

contention increase number of files

• Look for I/O subsystem contention (PAGEIOLATCH_XX), if

contention, spread the data files across multiple drives

Utilize fast file initialization

https://docs.microsoft.com/en-us/sql/relational-databases/databases/database-instant-file-initialization?view=sql-server-2017

Utilize a dedicated disk for Microsoft Page File

https://support.microsoft.com/en-gb/help/2860880/how-to-determine-the-appropriate-page-file-size-for-64-bit-versions-of

Nutanix Best Practices Links

http://stevenpoitras.com/2013/11/advanced-nutanix-sql-server-nutanix-best-practices-released/

 SQL Server on VM best practices