White Papers

Install and configure Microsoft SQL Server 2017 Enterprise Edition
22 Deploying the 385TB Data Warehouse Fast Track Reference Architecture for Microsoft SQL Server 2017 using
Dell EMC PowerEdge R940 and SC9000 | 4033-CD-SQL
7.3.3 Set SQL Server maximum memory
Set the SQL Server maximum memory to 3040 GB:
1. Click New Query on the toolbar.
2. Enter the following T-SQL commands:
EXECUTE sp_configure 'max server memory (MB)', '3112960'
GO
RECONFIGURE
GO
3. Click Execute on the toolbar.
7.3.4 Set the 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.
1. Click New Query on the toolbar.
2. Enter the following T-SQL commands to set the maximum degree of parallelism to 96:
EXECUTE sp_configure 'max degree of parallelism', '96'
GO
RECONFIGURE
GO
3. Click Execute on the toolbar.
7.3.5 Configure the resource governor
During rowstore I/O tests, resource governor was set to 5. For all other tests, it was set to 12.
1. Click New Query on the toolbar.
2. Enter the following T-SQL commands to configure the resource governor to limit memory grants to 12
percent:
ALTER WORKLOAD GROUP [default]
WITH( request_max_memory_grant_percent = 12 )
GO
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO
3. Click Execute on the toolbar.