Dell PowerEdge R940 385TB Data Warehouse Fast Track Reference Architecture for - Page 21

SQL Server 2017 Enterprise Edition configuration 7 SQL Server 2017 Enterprise Edition configuration 7.1 7.2 7.3 7.4 7.5 7.6 Grant perform volume maintenance task privilege During installation of SQL Server 2017, the option to grant the SQL Server Database Engine Service the Perform Volume Maintenance Task privilege was selected. SQL Server maximum memory The maximum server memory for this reference architecture should be set to 3040GB which leaves 32GB for the operating system. If additional applications share the server, adjust the amount of memory left available to the operating system accordingly. Max degree of parallelism (MAXDOP) During rowstore tests, MAXDOP=16 was used for I/O testing and MAXDOP=72 was used for CPU testing. For columnstore tests, MAXDOP=96 was used. For more information, see the Microsoft article Configure the max degree of parallelism Server Configuration Option. Resource governor The resource governor was used to limit the maximum memory grant. During rowstore I/O tests, resource governor was set to 5, and for all other tests it was set to 12. For information about the resource governor, see the Microsoft article Resource Governor. Database configuration The data warehouse database was configured to use multiple file groups, each containing four files distributed evenly across the four data volumes. All files were allowed to grow automatically. The file groups were configured with the AUTOGROW_ALL_FILES option to help ensure that all files within a given file group remain the same size. Tempdb configuration The tempdb database was configured to use eight data files of equal size. The data files were evenly distributed across the two tempdb data volumes, with four files stored on each volume. The tempdb transaction log file was placed on the log volume. All files were expanded to the appropriate size and auto grow was enabled. 21 385TB Data Warehouse Fast Track Reference Architecture for Microsoft SQL Server 2017 using Dell EMC PowerEdge R940 and SC9000 | 4032-RA-SQL