Best Practices: Microsoft SQL Server

From a pure performance perspective, storing all database files and transaction logs on the same volume will not impact performance. However, from a management perspective, it is recommended that:

• Backups reside on their own separate volume
• If user databases are to be cloned and refreshed, these should reside on their own volumes in that there is no value to be had from cloning refreshing system databases, also tempdb is re-created every time its associated SQL Server instance is re-started.
• Spreading a database across more volumes will increase the granularity at which data reduction can be observed.
• In the case of virtualized infrastructure with virtualized file systems (such as VMDK or VHD/VHDX), potential queuing issues at the virtual disk adapter level should be considered. In VMware, for example - multiple ParaVirtual SCSI adapters can help increase performance. It is recommended that workloads are tested in order to see if more or less virtual SCSI adapters will benefit performance. Better performance is more likely to be achieved by spreading database files across multiple VMDKs/RDMs. At a minimum, split database and log into different VMDKs/RDMs. A separate data store for things not subject to replication; tempdb, swap/page files for example is encouraged. The VMware hyper-visor aggregates IO at the virtual adapter level, as such it is recommended that for performance critical applications, latency sensitive files such as transaction logs should reside on their own virtual disks with dedicated virtual adapters.

Testing conducted by Pure Storage has demonstrated that compression can increase backup throughput by at least 25%, reaching 35% in some scenarios. The continued use of backup compression is recommended, with very few exceptions, one being CPU being adversely affected by the use of compression. 

Note that compressed backups will not be further reduced by the array, as high entropy data rarely reduces further. Assume a 1:1 data reduction ratio for compressed backups. Uncompressed backups will take longer to complete, but will de-dupe with the actual database files (since backups contain copies of all the database pages) shortly after the backups complete. Full de-duplication should not be expected. Data reduction rates will vary depending on how much your data changes.

By default, SQL Server will use jumbo sized IOs (typically 1MB) to perform backup and restore operations. These can adversely affect the IO latency for your array. Whenever possible, use MAXTRANSFERSIZE=65536 in BACKUP or RESTORE statements. This can make backups and restores take slightly longer, therefore test accordingly. If Transaction Log Shipping is being used, there might be no way to tune this, as sqllogship.exe doesn't offer a means to tune this parameter. If log shipping backups/restores are affecting performance, consider using scripts to perform log shipping manually. 

If good throughput from backup/restore operations is not achieved after using the MAXTRANSFERSIZE=65536, considering increasing the number of buffers available to the process by using the BUFFERCOUNT parameter. In testing, a 1.5TB database was backed up in 24 minutes, 38 seconds using default settings, and 25 minutes, 6 seconds with 64Kb IO block size and BUFFERCOUNT=2048.

Microsoft's recommendation of formatting volumes with an NTFS allocation unit size of 64 Kilobytes should be adhered to, unless there is a requirement to leverage NTFS compression, which mandates the use of 4 Kilobyte allocation unit sized-volumes. This doesn't mean that SQL Server will use an IO block size corresponding to the chosen allocation unit size, the database engine will issue IOs as small as a single sector (512 bytes) or as large as 8 Megabytes for column store indexes. We also recommend that you use the /L parameter of the FORMAT command for your database volumes (or the -UseLargeFRS parameter for the Format-Volume Powershell cmdlet), which will help avoid certain issues like CHECKDB failing with error 665. (Use /L to prevent NTFS from becoming fragmented by smaller files.) This creates NTFS volumes with large File Record Segments. Please note that the /L parameter is only available on Windows Server 2012 and above.

• When using VMware virtual disks (VMDKs) always use ParaVirtual SCSI adapters for the best performance on all data/log/tempdb/backup volumes.
• Disks should be created as "Eager Zeroed Thick" to prevent performance issues arising from in-flight allocations.

ESXi 6.0+: 

• For later versions of ESX, the use of RDM disks for SQL instances that intend on using VSS for application consistent snapshots is recommended.
• If ESX is used for back up and recovery, all disks should be thin provisioned. 
• Please note that vVols are thin provisioned. Thin provisioned vVols do not have the negative performance impacts of thin provisioned VMDKs as they leverage the VASA provider