SQL Server RAM increases when service called

[LeviRoberts]

Hello,

I've noticed that the RAM associated with our SQL Server increases each time a web service referencing data in the SQL Server is queried.  When we restart the machine SQL Server starts out only using a couple hundred mb's of RAM. Each query adds about 2-3 mb's to the RAM. Overtime this will cause SQL Server to consume gigs of RAM. This morning it was at 16 gb's of RAM. I have a few questions regarding this.

1. Is this a common issue?
2. What exactly is happening that causes the increase to the RAM?
3. What is the best practice in keeping the RAM down? Or do we just need to restart the machine regularly?

Thanks

[JoshuaBixby · Accepted solution]

This is not an ArcGIS issue, it is a SQL Server issue.  That said, it isn't really a SQL Server issue either.  SQL Server, like most DBMSes, will gobble up all the memory they can because the more a DBMS does in memory the quicker the response times.

If SQL Server is running on a dedicated host, which I strongly encourage, then let it do its thing and dynamically manage its memory usage.  If SQL Server is on a shared host, or you feel compelled to limit its memory, there is a system wide setting to limit its memory usage.

In terms of "keeping the RAM down," don't, outside of possibly setting an overall limit.  Microsoft and other DBMS vendors invest a lot of time and money into memory management, and the DBMS is constantly looking at all of the queries being performed and deciding what to keep in memory and what to page out.  There are few people that can manage memory better than the DBMS itself.

[LeviRoberts]

Thanks Josh. That makes a lot of sense. I had a feeling something like this might be the case as it never seemed to max out the resources. It just seemed to consume as much as possible. With this in mind we will probably just scale back the RAM as the machine is currently at overkill levels. Thanks for the informational response!

[GregCarlino2]

Greetings Levi and Joshua.  I just now came across this post, as I've recently started having the exact same issue.  At the moment, we have ArcGIS Server and SQL Server deployed on the same machine.  Unfortunately, as SQL memory usage creeps up, it eventually crosses a threshold at which ArcGIS Service queries start to time out, and web maps freeze up.  Yet, this threshold is only at about 75% memory usage.  It seems like there should still be plenty of remaining memory to keep things running smoothly (If usage were around 85-90% this would make more sense).  It is puzzling why the remaining 25% is apparently not be making any difference.  Is there a reason why so much free memory would be required?  Does setting the system-wide limit on SQL impact it's performance?