We have been having intermittent performance issues with our production sql server and I happened to be watching resource monitor while one happened and I noticed that the latency on the page file disk jumped to 700ms for about 5 minutes. I also noticed under "memory" that the "working set" for sqlservr.exe is only 1.6 GB even though we have 500 GB of RAM on the server. Why isn't SQL using more of the memory?
right click on sql server and select properties and then select Memory. What does it say?
It says max memory is 400 GB.
What specific version and edition of SQL Server? Some SQL versions/editions limit memory use by SQL.
Hi Scott,
It is SQL Server 2017 but it is currently running in 2016 compatibility mode.
Thanks.
You didn't specify the edition.
If you have Enterprise, SQL could use up to the 400GB then.
If it's only Standard, SQL could use 128GB.
If it's Express, it could only use ~1.4GB.
Oh sorry. Enterprise.
If the max memory setting is 400GB under Enterprise, if SQL needs memory, it should grab more until it reaches ~400GB.
Maybe something other than SQL caused the resource usage you noticed? Someone logged in and loaded a large spreadsheet or document, etc.?
What is the actual value?