— Focus on these settings:
— backup compression default (should be 1 in most cases)
— clr enabled (only enable if it is needed)
— cost threshold for parallelism (depends on your workload) 50 is good
— lightweight pooling (should be zero)
— max degree of parallelism (depends on your workload) 4-8
— max server memory (MB) (set to an appropriate value, not the default)
— optimize for ad hoc workloads (should be 1)
— priority boost (should be zero)
— remote admin connections (should be 1)
–ALTER SERVER CONFIGURATION SET PROCESS AFFINITY CPU = 0,1
–sp_configure ‘affinity mask’, 3;
–RECONFIGURE;
–GO

USE [master];
GO
EXEC xp_instance_regwrite N’HKEY_LOCAL_MACHINE’, N’Software\Microsoft\MSSQLServer\MSSQLServer’, N’NumErrorLogs’, REG_DWORD, 31;
GO
EXEC sp_configure ‘show advanced options’, 1;
RECONFIGURE;
EXEC sp_configure ‘optimize for ad hoc workloads’, 1;
RECONFIGURE;
EXEC sp_configure ‘backup compression default’, 1;
RECONFIGURE;
EXEC sp_configure ‘cost threshold for parallelism’, 50;
RECONFIGURE;
EXEC sp_configure ‘fill factor’, 90;
RECONFIGURE;
EXEC sp_configure ‘remote admin connections’, 1;
RECONFIGURE;
EXEC sp_configure ‘xp_cmdshell’, 1;
RECONFIGURE;

–exec sp_configure ‘max degree of parallelism’,4
–reconfigure;

DECLARE @maxmem INT;
SELECT @maxmem = CASE
WHEN total_physical_memory_kb / 1024 < = 4000 THEN(total_physical_memory_kb / 1024) - 800 WHEN total_physical_memory_kb / 1024 > 4000
AND total_physical_memory_kb / 1024 < = 8000 THEN(total_physical_memory_kb / 1024) - 2000 WHEN total_physical_memory_kb / 1024 > 8000
AND total_physical_memory_kb / 1024 < = 16000 THEN(total_physical_memory_kb / 1024) - 3000 WHEN total_physical_memory_kb / 1024 > 16000
AND total_physical_memory_kb / 1024 < = 32000 THEN(total_physical_memory_kb / 1024) - 4000 WHEN total_physical_memory_kb / 1024 > 32000
AND total_physical_memory_kb / 1024 < = 64000 THEN(total_physical_memory_kb / 1024) - 6000 WHEN total_physical_memory_kb / 1024 > 64000
AND total_physical_memory_kb / 1024 < = 96000 THEN(total_physical_memory_kb / 1024) - 8000 WHEN total_physical_memory_kb / 1024 > 96000
AND total_physical_memory_kb / 1024 < = 128000 THEN(total_physical_memory_kb / 1024) - 12000 WHEN total_physical_memory_kb / 1024 > 128000
THEN(total_physical_memory_kb / 1024) – 32000
END
FROM sys.dm_os_sys_memory;
EXEC sp_configure ‘max server memory (MB)’, @maxmem;
RECONFIGURE;
–EXEC sys.sp_configure N’show advanced options’, N’0′;
–RECONFIGURE WITH OVERRIDE;
GO