3.4.3 MxDB for SQL Server Installation and Administration Guide

Chapter 2: Install SQL Server 38

Other Installation Tasks

Adjust the Maximum Memory Setting for SQL Server

PolyServe recommends that you reserve approximately 15% of the

system resources for the operating system and Matrix Server. For each

SQL instance installed, you should change the Maximum Memory

default setting for SQL Server from “all” to 85% (or less) of the total

physical RAM on the system.

The following script is a template to set the maximum RAM for a

particular SQL instance. If multiple instances are installed on the node,

you can divide 85% by the number of instances. The exact memory

allocation should be studied and defined for your particular

environment—some instances may require more memory than others.

--Set physical memory limit

exec sp_configure 'show advanced options',1

reconfigure with override

declare @percent int, @mem int

--Set the reserved %

set @percent=15

create table #tmp([Index] int,

[Name] sysname,

[Internal_Value] int null,

[Character_Value] sysname null)

insert #tmp

exec master..xp_msver

select @mem=(1-(@percent/100.))*Internal_Value

from #tmp

where [Name]='PhysicalMemory'

drop table #tmp

exec sp_configure 'max server memory',@mem

reconfigure with override