fiftynine.io
Best Practice Reference
| Area | Applicable | Practice | Description | Reference |
|---|---|---|---|---|
| OS | Partition the Disk volumes hosting SQL databases (Data and log) with 64 KB allocation unit size: | The atomic unit of storage in SQL Server is a page which is 8KB in size. Extents are groups of eight 8 KB pages that are physically contiguous to each other for a total of 64 KB. SQL Server uses extents to store data. Hence, on a SQL Server machine the NTFS Allocation unit size hosting SQL database files (Including tempdb files) should be 64K. | https://blogs.msdn.microsoft.com/docast/2018/02/01/operating-system-best-practice-configurations-for-sql-server/ | |
| OS | Instant file initialization | https://blogs.msdn.microsoft.com/docast/2018/02/01/operating-system-best-practice-configurations-for-sql-server/ | ||
| OS | OS Power Saving setting to High Performance | https://blogs.msdn.microsoft.com/docast/2018/02/01/operating-system-best-practice-configurations-for-sql-server/ | ||
| OS | All | Antivirus exclusion on SQL files | .mdf, .ndf, .ldf, .bak, .trn files and sqlservr.exe added to the on-access scanning exclusion list | https://blogs.msdn.microsoft.com/docast/2018/02/01/operating-system-best-practice-configurations-for-sql-server/ |
| OS | > Windows Server 2008 | Lock Pages In memory | Lock pages in memory privilege to SQL is recommended in Windows Server 2008 or above only when there are signs of paging. Locking pages in memory may boost performance when paging memory to disk is expected. | https://blogs.msdn.microsoft.com/docast/2018/02/01/operating-system-best-practice-configurations-for-sql-server/ |
| OS | Setting appropriate Page file size | https://blogs.msdn.microsoft.com/docast/2018/02/01/operating-system-best-practice-configurations-for-sql-server/ | ||
| OS | Windows security policy and permissions | https://blogs.msdn.microsoft.com/docast/2018/02/01/operating-system-best-practice-configurations-for-sql-server/ | ||
| OS | > Windows Server 2012 Cluster | Dynamic Quorum Configuration in Windows 2012 and 2012R2 cluster | https://blogs.msdn.microsoft.com/docast/2018/02/01/operating-system-best-practice-configurations-for-sql-server/ | |
| Full-Text | All | To use all CPU processors or cores to the maximum, set sp_configure ’max full-text crawl range’ to the number of CPUs on the system | Using this option, you can specify the number of partitions that Microsoft SQL Server should use during a full index crawl. For example, if there are many CPUs and their utilization is not optimal, you can increase the maximum value of this option. In addition to this option, SQL Server uses a number of other factors, such as the number of rows in the table and the number of CPUs, to determine the actual number of partitions used. | https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/max-full-text-crawl-range-server-configuration-option?view=sql-server-2017 |
| SQL | Maximum degree of parallelism | As a rule of thumb, use the number of cores in a NUMA node - this is subject to any third party vendor recommendations, of course. | https://support.purestorage.com/Solutions/Microsoft_Platform_Guide/Microsoft_SQL_Server/001_Microsoft_SQL_Server_Quick_Reference | |
| SQL | Cost threshold for parallelism | This will vary depending on your workload requirements. If your system has an OLTP-like workload, set this to 15-20 and possibly higher, as you seek to minimize parallelism and offer more concurrency. On a DSS/OLAP/Data Warehousing-like workload, consider leaving at default and managing parallelism through tweaking of MAXDOP if concurrency is a problem. | https://support.purestorage.com/Solutions/Microsoft_Platform_Guide/Microsoft_SQL_Server/001_Microsoft_SQL_Server_Quick_Reference | |
| SQL | Number of tempdb files | Typically we recommend that you use no less than 8 tempdb files as a starting point, and increase if you observe PFS/GAM/SGAM contention on your system. This recommendation applies to virtualized systems as well. | https://support.purestorage.com/Solutions/Microsoft_Platform_Guide/Microsoft_SQL_Server/001_Microsoft_SQL_Server_Quick_Reference | |
| SQL | Backup Compression | In our testing, we’ve shown that compression increases backup throughput by at least 25%, reaching 35% on some scenarios. We recommend you continue to use backup compression, with very few exceptions (one being CPU being adversely affected by the use of compression, for example). | https://support.purestorage.com/Solutions/Microsoft_Platform_Guide/Microsoft_SQL_Server/001_Microsoft_SQL_Server_Quick_Reference |