I have a application that uses SQL Server 2008 R2. I have a problem of memory consumption on the client side. Whenever the client runs the application, the SQL server starts consuming memory even if the client is just on stand by and not using any of the applications function. This ultimately makes it slow for him to work on the PC after long time on stand by.
The issue is why the SQL Server is consuming memory even if its not on work and how can i resolve it.
Please help.
Like Ryan Ries said in comments, if the SQL Server is running on a different host than the client and the client is having memory issues then the application needs fixed. If this is the case then we don't have anything to go on to help with that. Tuning an app requires a lot of information about the app and environment.
Regarding general SQL Server memory consumption, that's by design. SQL Server will load as much into memory as it can so that it's working with data in memory and deferring expensive disk I/O operations until absolutely necessary. If your databases are larger than your physical memory or you have other things running on the same host then you can quickly experience memory contention. They default setting for SQL Server is to use as much memory as it can.
You can set a maximum memory value for SQL Server so that it consumes no more than that amount of memory. Usually if SQL Server is the only thing on the server I will leave 2-4GB of memory available for the OS and let SQL Server have the rest. So for a box with 64 GB of memory I'll set SQL Servers max memory to 60 GB.
Here's how:
sp_configure 'max server memory (MB)',60000
GO
reconfigure
GO
(This sets max memory to 60GB. Of course set the value to fit your server's specs.)
This setting is dynamic so there's no need to restart the SQL Server instance for this to take effect. It will take affect when the reconigure statement is executed.
