When the SQL Server installation process is completed, adjust parameters at the SQL Server level. The following sections describe commonly used configurations that require adjustment for best SQL Server performance.

Set up SQL Server memory

Minimum and maximum memory settings determine the amount of memory that is assigned to the SQL Server instance. These instance level settings are based on the memory allocation. SQL Server segregates memories in different chunks, for example, buffer pool, log buffer, plan cache, and so on.

To change these configuration values, use the following script:

sp_configure 'show advanced options', 1;

GO

RECONFIGURE;

GO

sp_configure 'max server memory', 4096; -- value in MB

GO

RECONFIGURE;

GO

For details about SQL Server memory configuration options, see the Server memory configuration options.