Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question

closed as off-topic by Ward, mdpc, Tom O'Connor Aug 25 '13 at 22:43

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Questions must be relevant to professional system administration. Server Fault is a site dedicated to professionals; novice questions are off-topic. Please see the Help Center for more information on topicality. The best advice we can give you is to hire a professional to help you out." – Ward, mdpc, Tom O'Connor
If this question can be reworded to fit the rules in the help center, please edit the question.

1  
The SQL server is causing memory exhaustion on connected clients? Then the answer should be "fix your application." If you can't do that then tell people to exit the application any time they're not actively using it. You haven't provided any further details that could help us diagnose this any more deeply. –  Ryan Ries Aug 21 '13 at 12:47
add comment

1 Answer

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.

share|improve this answer
add comment

Not the answer you're looking for? Browse other questions tagged or ask your own question.