MS-SQL-Server - max. Arbeitsspeicher begrenzen

How to use sp_configure to set some common Advanced Options

Turn on advanced options EXEC sp_configure 'Show Advanced Options', 1 GO RECONFIGURE GO

See what the current values are EXEC sp_configure

Turn on backup compression by default (new for SQL 2008) EXEC sp_configure 'backup compression default', 1 GO RECONFIGURE GO

Turn on optimize for ad-hoc workloads (new for SQL 2008) EXEC sp_configure 'optimize for ad hoc workloads', 1 GO RECONFIGURE GO

Set MAXDOP = 1 for the server (if you have an OLTP workload and you see CXPACKET waits) EXEC sp_configure 'max degree of parallelism', 1 GO RECONFIGURE GO

Set max server memory for the server (based on how much physical RAM you have) EXEC sp_configure 'max server memory (MB)', 6500 – This value = 6.5GBGO RECONFIGURE GO

Enable CLR (if you need it) EXEC sp_configure 'clr enabled', 1 GO RECONFIGURE GO