SQL Server 2014 express edition is published as having a memory limit of 1 GB.
What do the SQL Instance Memory Properties do with respect to this limit? For example, I can set the minimum and maximum memory limits within the properties to 4GB and according to the task manager that instance will 'use' 4GB of system memory.
Am I better off limiting the memory properties to 1 GB or is there advantage in allocating >1 GB memory here.
The 1GB limit is only for the buffer pool, SQL Server uses other memory pools as well so it is possible to see memory usage over 1GB.
Quoting from Memory configuration and sizing considerations in SQL Server 2012 and later versions
Starting with SQL Server 2012, these memory limits are enforced only for the database cache (buffer pool). The rest of the caches in the SQL Server memory manager can use much more memory than is specified by these edition limits. For example, a SQL Server 2012 Express edition can use only a maximum size of 1.4 GB for its database cache. Other caches (such as the procedure cache, the metadata cache, and so on) can consume memory up to the size specified by the "max server memory" configuration.
For SQL Server 2016 SP1 onward, Express can also use an additional 352MB for columnstore segments per instance and another 352MB for in-memory OLTP (Hekaton) objects per database.
The maximum buffer pool size was also changed in the documentation from 1GB to 1410MB for SQL Server 2016, though this probably applies from SQL Server 2012 (where the big memory management changes were made).
Only the buffer pool, columnstore cache, and in-memory OLTP cache sizes are restricted by quota. Other cache stores and memory consumers in Express Edition can push overall memory usage up to the configured limit in the max server memory configuration setting.
See Also:
