Skip to main content
Pure Technical Services

Best Practices: Microsoft SQL Server

Currently viewing public documentation. Please login to access the full scope of documentation.

In this guide, we detail our recommendations for achieving maximum performance while ensuring ease of management for Microsoft SQL Server databases hosted on Pure Storage arrays. These are general recommendations are not model specific, and apply to all generations of FlashArray.

These best practices apply to all versions of SQL Server unless otherwise noted.

Quick Reference: Best Practice

Setting Recommended Value
Maximum degree of parallelism Subject to any third party vendor recommendations, set the maximum degree of parallelism to be no more than the number of logical processors per NUMA node (CPU socket).
Cost threshold for parallelism Setting this depends on the type of SQL Server workload, set this to 15-20 and possibly higher for OLTP type workloads, the aim being to minimize parallelism and pursue greater concurrency. For DSS/OLAP/Data Warehousing type workloads, consider leaving this at its default and managing parallelism through MAXDOP if concurrency is a problem.
Number of tempdb files Use 8 tempdb data files (extensions .mdf, .ndf) as a starting point and increase this if PFS/GAM/SGAM is observed.  Only one tempdb log file (extension .ldf) is necessary. This recommendation applies to SQL Server instances irrespective of whether the underlying server is physical or virtualized and follows the advice outlined in this Microsoft support article.
Number of Data/Log Volumes

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.
Backup Compression

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.

Restore IO size

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.

Index Rebuilds When using array based replication, consider using SORT_IN_TEMPDB=ON for ALTER INDEX...REBUILD statements, along with a separate, non-replicated volume for tempdb database and transaction log files. This will help minimize the amount of data sent across the replication link, as intermediate sort results for the index rebuild should never need to be replicated.
Volume Formatting

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.

Virtual Disk Considerations ESXi 5.5: 
  • 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