Originally, a SQL Server installs with default settings that are not really optimized. Amazing? Not that much. Microsoft ensures that its DBMS software can be installed on a maximum of hardware and software configurations. And to be compatible with the greatest number, the optimizations are not applied with a standard installation by default. It will be necessary to be documented, to have experience, to follow a formation or to ask for an audit to find the good parameters which will be adapted to the use of the SQL server.
The following recommendations are fairly generic but are applicable to most situations where a SQL Server database is used by software, in production.
Microsoft SQL Server Default Configuration
1. Open the software Microsoft SQL Server Management Studio (SSMS).
2. To log in to a database server, local or remote.
3. Make a right click on the server name then Properties.
4. Click on the page « Memory to display the current SQL Server configuration. By default, we find:
- Minimum server memory: 0 MB
- Maximum server memory: 2147483647 MB (i.e. 2TB)
Recommendations for managing RAM with SQL Server
Unless you have more than 2TB of RAM on this server, it is not recommended to let SQL Server take all the RAM possible, this at the expense of the general performance of the operating system. The DBMS will use all the RAM available to stuff itself with fast memory, without taking into account the other uses of the server. If the machine is also used to host a business application (accounting, ERP, etc.), it is certainly reduced performance for other software. If the SQL server is installed on a workstation or a laptop, for example at a developer’s, a sales representative’s for demos or on a consultant’s PC, it is also essential to limit the maximum memory consumed by MSSQLSERVER. Two rules to define this value.
According to the size of the databases
Let’s assume that the SQL server hosts only one DB which represents a disk space of 3GB (size of the MDF file). There’s really no point in the SQL Server service taking up 7GB of RAM and bringing other active Windows processes to their knees. The recommendation is to limit the maximum memory to the size of the base. In the example of a database that weighs 3GB, the SQL Server memory will therefore be limited to 3GB, or 3072MB. The modifications are immediate, no need to restart the instance, the service or Windows.
Depending on the amount of server RAM
Another possibility is to limit the memory of the sqlserver.exe process according to the amount of RAM on the server. If the machine (virtual or physical) has 8GB of RAM and no other role is assigned to this server (no application hosting, website, other server role), we will leave between 1 and 2 GB of RAM available to the operating system Windows. Thus, we will limit the maximum memory of SQL Server to 6GB (6144MB) or 7GB (7168MB). MSSQL does not straddle GB and we can cut the pear in half and indicate 6500MB.
Of course, if the server in question houses a SQL Server but also other roles, it will be necessary to limit the memory according to these other uses so that everyone has their own memory space, without disturbing the proper functioning of neighboring programs.