Configure SQL Instance Memory Properties
| Previous Configure SQL Instance General Properties | Manual Configuration | Configure SQL Instance Processor Properties Next |
|---|
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.
The SQL Instance Memory Properties configuration relates to Process Id 5BD and is controlled by the parameters below:
| SQL Version | Parameter | FULL Build | WORKSTATION Build | CLIENT Build |
|---|---|---|---|---|
| SQL2019 | /SetupSQLInst: | Yes | Yes | N/A |
| SQL2017 | /SetupSQLInst: | Yes | Yes | N/A |
| SQL2016 | /SetupSQLInst: | Yes | Yes | N/A |
| SQL2014 | /SetupSQLInst: | Yes | Yes | N/A |
| SQL2012 | /SetupSQLInst: | Yes | Yes | N/A |
| SQL2008R2 | /SetupSQLInst: | Yes | Yes | N/A |
| SQL2008 | /SetupSQLInst: | Yes | Yes | N/A |
| SQL2005 | /SetupSQLInst: | Yes | Yes | 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 |
|---|---|---|
| /SQLMaxMemory: | See Memory Calculation | Maximum memory to assign to SQL Server |
| /SQLMinMemory: | 0 | Minimum memory to assign to SQL Server |
-
Check Server Memory
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
For 32-bit servers the Use AWE to allocate memory should be set. See https://techcommunity.microsoft.com/t5/sql-server-support/fun-with-locked-pages-awe-task-manager-and-the-working-set-8230/ba-p/315836 for more details of the use of AWE
-
SQL Server Maximum Memory
SQL FineBuild will always set a value for /SQLMaxMemory: to maximise the performance of SQL Server. This value will be set to take into account:
- The total physical memory assigned to the Windows instance hosting SQL Server
- The version of Windows being used by the server
- Other SQL Server components being installed at the same time
The value calculated by SQL FineBuild does not take into account the memory needed by multiple instances of SQL Server running on the same Windows instance.
In all situations the value calculated by SQL FineBuild should be taken as a starting point for further memory tuning. The calculations used by SQL FineBuild can be found in the routine SetMemoryLimits in the FBConfigBuild.vbs module. Further advice on how to establish the optimum upper bound for SQL Server memory can be found in BOL and by searching on the internet
-
SQL Server Minimum Memory
Minimum memory should normally be left at zero because:
- A difference between minimum and maximum memory is needed to allow SQL Server memory management to work effectively
- 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
- A minimum memory value of zero allows the system to degrade gracefully if for any reason the memory values are too high for system stability
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.
-
Set the appropriate values on the Memory settings
Option Value Notes Use AWE to allocate memory Checked (32-bit only) See SQL Server AWE Property Minimum memory /SQLMinMemory: value Minimum memory to assign to SQL Server Maximum memory See Memory Calculation Maximum memory to assign to SQL Server 
-
Click OK to save any changes made
Copyright FineBuild Team © 2013 - 2021. License and Acknowledgements
| Previous Configure SQL Instance General Properties | Top | Configure SQL Instance Processor Properties Next |
|---|