In the content below, topics are divided into 3 sections:
Storage is critical to the performance of a business intelligence solution. Not only is the hardware selection important, how our storage is configured is also very important. When planning for your storage needs, AIM can assist in gathering current storage usage amounts and related performance metrics. As a gesture of good will, AIM has provided a list of "best practices" in regards to storage and SQL Server.
NTFS Allocation Unit (AU)
Block Size = 64k, Alignment = 1024k | Default is 4k, Use /L with Format on Windows 2012 and above.
Max Degree of Parallelism
MAXDOP | Set to Number of Cores in a Single CPU Socket
DB Auto Growth
Set very high for performance. (100MB to 100s GB)
Cost Threshold for Parallelism
For OLTP where we seek to minimize Parallelism and offer more concurrency then Use 15-20.
Up to 50 with modern CPUs. For DSS, OLAP, Data Warehouse, and test environments
Consider leaving at default and Managing Parallelism with MAXDOP if concurrency is a problem.
1:2, or 1:4 Ratio (TempDB Data Files to Cores). 1:1 Ratio for large systems. Pre SQL Server 2016:
Use Trace Flags T1117 and T1118 to enable consistent AutoGrowth.
On Flash Arrays enable SORT_IN_TEMPDB Index Build option to prevent index rebuilds.
Separate Data / Log Volumes
Tier 1. Test to determine for Tier 2 Flash Arrays. Multiple Volumes per File Group to Reduce Latch Contention.
4-8 Files per File Group.
3 Volumes | TempDB, Data / Log Files, and Backups for Fast Flash (Under 1ms Response Times)
Max Server Memory
90% of Available Server Memory
Enable Instant File Initialization
Windows Server Setting: Perform Volume Maintenance Tasks needs to be Set Under Local Policies and User Rights Assignments.
We offer services to size and scale a solution's infrastructure:
We offer services to stand up a network from scratch including: