TIL: SQL Server uses all available memory

13 Nov 2020 #todayilearned #sql

SQL Server tries to use all available memory. SQL Server allocates memory during its activity. And, it only releases it when Windows asks for it.

This is normal behavior. SQL Server caches data into memory to reduce access to disk. Remember, SQL Server caches data pages, not query results.

You can limit the amount of memory available by setting the option “Maximum Server Memory”. By default, it is a ridiculous huge number: 2,147,483,647 MB.

This is specially true, if you’re running SQL Server on your development machine.

For your Production instances, check BornSQL’s Max Server Memory Matrix to set the right amount of RAM your SQL Server needs.

Voilà! This is a true story of how SQL Server was eating my memory. We needed some limits to keep things running smoothly on my laptop.

For more SQL Server content, check Six SQL Server performance tuning tips and How to write dynamic SQL queries.

Source: Setting a fixed amount of memory for SQL Server