This project has moved. For the latest updates, please go here.

Previous Configure SQL Instance General Properties Manual Configuration Configure SQL Instance Processor Properties Next

Configure SQL Instance Memory Properties

FineBuild can Configure the Memory Properties available in SSMS for the SQL Server Instance.

The Memory Properties for SQL Server need to be set to maximise SQL Server performance. It is recommended that you understand the aspects described in SQL Server Memory Management before setting SQL Server Memory Properties.

FineBuild Configure SQL Instance Memory Properties

The SQL Instance Memory Properties configuration relates to Process Id 5BD and is controlled by the parameters below:

Parameter Build SQL2005 SQL2008 SQL2008 R2 SQL2012 SQL2014
SetupSQLInst FULL Yes Yes Yes Yes Yes
SetupSQLInst WORKSTATION Yes Yes Yes Yes Yes
SetupSQLInst CLIENT N/A N/A N/A N/A N/A

In order to maintain compatibility with older versions of SQL FineBuild, the parameter ConfigSQLInst can also be used.

FineBuild also uses the following parameters to help configure SQL Instance Properties:
Parameter Default Value Description
spConfigureMaxServerMemory See Memory Calculation Maximum memory to assign to SQL Server

Top

Memory Calculation

The SQL Maximum memory property should always be set in order to maximise the performance of SQL Server. The calculated maximum memory is based on the tables below:

Available Memory SQL Maximum Memory
< 3 GB 70% of Available Memory
> 3 GB 80% of Available Memory

The Maximum memory should be adjusted to take the following into account:

a) The calculated maximum value assumes a single instance of SQL Server is running and that minimal use is made of Full-Text indexing, Analysis Services and Reporting Services. If any of these features are used extensively, or more than 1 instance of SQL Server is installed, or if significant other processing occurs on the server, a lower value will be needed .

b) The total server memory can be obtained by right-click on My Computer and select Properties. This will show the true value for a 64-bit server, but will only show the memory available below the 4 GB line for a 32-bit server.
System Properties.png
Minimum memory should always be left at zero because:

a) A difference between minimum and maximum memory is needed to allow SQL Server memory management to work effectively.

b) Some very infrequent tasks, such as rebuilding a Full Text catalogue, require large amounts of memory outside of SQL Server, and may cause stability issues if SQL Server is prevented from reducing the memory it uses.

c) A minimum memory value of zero allows the system to degrade more gracefully if for any reason the memory values are too high for system stability.

See http://blogs.msdn.com/psssql/archive/2009/09/11/fun-with-locked-pages-awe-task-manager-and-the-working-set.aspx for more details of the use of AWE. Advice on how to establish the optimum upper bound for SQL Server memory can be found in BOL and by searching on the internet.
Top

Manual Configure SQL Instance Memory Properties

The following steps show what you would have to do for manual SQL Instance Memory Properties configuration. FineBuild does all of this work for you automatically.

1) Set the appropriate values on the Memory settings
Option Value Notes
Use AWE to allocate memory Checked See SQL Server AWE Property
Minimum memory 0 Minimum memory to assign to SQL Server
Maximum memory See Memory Calculation Maximum memory to assign to SQL Server

Memory.png
2) Click OK to save any changes made.

Copyright © 2013 - 2014 Edward Vassie. License and Acknowledgements
Previous Configure SQL Instance General Properties Top Configure SQL Instance Processor Properties Next

Last edited Dec 22, 2014 at 4:49 PM by EdVassie, version 5