[SQL Server] server settings

-- 1 memory

-- Set to use awe to allocate memory
Exec SYS. sp_configure n 'awe enabled', n'1'
Go
Reconfigure with override

-- Set the minimum server memory to 50 MB
Exec SYS. sp_configure n 'min server memory (MB) ', N '50'
Go
Reconfigure with override
Go

-- Set the maximum server memory to 2147483647 MB
Exec SYS. sp_configure n 'max server memory (MB) ', n' 2147483647'
Go
Reconfigure with override
Go

-- Memory occupied by index creation
Exec SYS. sp_configure n 'index Create memory (Kb) ', N '0'
Go
Reconfigure with override
Go

-- Sets the minimum memory occupied by each query.
Exec SYS. sp_configure n 'min memory per query (Kb) ', n' 1024'
Go
Reconfigure with override
Go

-- 2 CPU

-- Set CPU Association
Exec SYS. sp_configure n 'affinity Mask', n'1'
Go
Reconfigure with override
Go

-- Set the I/O Association
Exec SYS. sp_configure n 'affinity I/O Mask', n'2'
Go
Reconfigure with override
Go

-- Set the maximum CPU thread
Exec SYS. sp_configure n 'max worker Threads', n' 128'
Go
Reconfigure with override
Go

-- Set the maximum CPU thread
Exec SYS. sp_configure n 'max worker Threads', n' 128'
Go
Reconfigure with override
Go

-- Improves SQL Server priority
Exec SYS. sp_configure n 'priority boost ', n'1'
Go
Reconfigure with override
Go

-- Security

-- Modify Server login authentication mode 1-Windows Authentication mode; 2-sql server and Windows Authentication Mode
Use [Master]
Go
Exec xp_instance_regwrite n 'HKEY _ LOCAL_MACHINE ', n' software/Microsoft/MSSQLServer', n' loginmode ', REG_DWORD, 1
Go

Follow-up