Dell PowerEdge R940 Deploying the 385TB Data Warehouse Fast Track Reference Ar - Page 22

Set SQL Server maximum memory, 7.3.4 Set the max degree of parallelism (MAXDOP)

Get Dell PowerEdge R940 PDF manuals and user guides View all Dell PowerEdge R940 manuals
Add to My Manuals
Save this manual to your list of manuals

Page 22 highlights

Install and configure Microsoft SQL Server 2017 Enterprise Edition 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. 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

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
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.