We have a SQL Server instance (2008) running our software with what we consider a small number of users (about 30 concurrent connected to the server at any given time)
As a vendor, we're not SQL DBAs and only really offer a basic skill-set for these sorts of issues. Most clients have their own SQL DBA to fill this role for them, but sometimes we have to step in and assist if the client is without adequate support.
Although they do use this server for more than just SQL and that is an issue that has to be dealt with separately, we recently noticed that this client's sqlservr.exe process is using a lot more RAM than we expect to see for our software.
First, let me start by mentioning that I understand the SQL Maximum Server Memory default is basically set to use everything a server has available. We changed that value to 10GB as we expect them to only need about 4-6GB at any given time considering their size and portions of the software they use.
Some bits of information that may or may not be relevant to my question:
- They have the databases for our software set to FULL recovery model and the logs have been growing and growing without maintenance for about 3 years.
- When we restart the SQL Services, the users come back into the software and for a period of about 24hrs, they use the amount of RAM we expect to see them use (about 4-6 of the allocated 10GB)
- We have been running Perfmon to compare some of the counter results with a period of time where they weren't having this issue, but haven't noticed any significant differences between the logs
- Checked event viewer for any obvious memory issues
My only guess at this time is that this has something to do with the enormous logs/recovery model, or some SQL setting that relates to caching. Maybe this is normal behavior that a SQL Expert would plan to see, but in our limited experience running our software in SQL Server, this is abnormal for an installation of this size.
So to summarize it all down to a single TLDR:
Does the SQL Server 2008 Recovery Model and Log Size impact memory usage for the sqlservr.exe process? If the answer is yes, is my description of our "problem" normal for SQL and we just haven't experienced it before? If the answer is no, what are some other things you would suggest investigating to solve my issue?