By default SQL Server does not limit the size of its in-memory cache. If you query enough cache-able data, SQL Server will use nearly all available memory on your server. You can limit the amount of memory by opening the Server Properties in SQL Server Management Studio (Right-click on the server in the Object Explorer and choose Properties:
SQL Server will use a bit more memory than what you specify since this setting only affects the size of the cache. However, the change will take effect within a few seconds, and you can see the total memory usage within the Task Manager in Windows to make further adjustments as needed.
How Much Memory Does SQL Server Need?
The optimal amount of memory for SQL Server is entirely dependent on the amount of data being stored and how that data is accessed. Most of the memory used by SQL Server is for in-memory caching to speed up queries.
A good way to determine whether your SQL Server instance is memory starved or overstuffed is to check the Page Life Expectancy counter. This counter tells you approximately how long a disk page will stay cached in SQL Server memory with no references. As a general rule, you should give SQL Server enough memory to keep this above 300 seconds. Below is a query to retrieve the current value:
Copy to clipboardSELECT [object_name], [counter_name], [cntr_value]
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%Manager%'
AND [counter_name] = 'Page life expectancy'
If you find that this counter is below 300 seconds, you should consider increasing the cache size in SQL Server (if it is currently limited) or adding more memory to the server if there is no available memory to give the SQL instance.
If you find that this counter is high (over 1000 seconds or more), then SQL Server may have more memory than it needs for the current workload. You may want to do some testing with restricting the cache size to see how much memory you really need. After reducing the cache size, check your application’s performance as well as the Page Life Expectancy counter to determine if the reduce cache size is acceptable.