1

Is there a way of decreasing RAM usage of SQL Server 2012 without restarting it?

Setting up MAX Memory usage is not an option as it's quickly building up to limit I have set and then app based on this SQL Server instance is acting up, being not responding or working very slow.

Improve this question

3 Answers 3

Reset to default
6

App based on this SQL Instance is acting up, being Not Responding or working very slow

You have a performance issue that you need to investigate. Read How to analyse SQL Server performance for a starter.

Do not change random settings, like memory, in hope you fix the problem. Analyze, find the root cause and fix it.

You're probably missing an index or more.

For the record: DBCC FREESYSTEMCACHE('ALL')

Improve this answer
4

Database servers use a lot of memory by design, as the alternative is to continually pull data off disk - which is orders of magnitude slower than memory.

Changing the max memory setting would be the primary method of reducing the amount of memory allocated to SQL Server. You've said that you don't want to actually reduce the amount of memory because application performance will suffer - so the alternative is to try to tune the workload, or potentially add more RAM to the server.

If you're going to try to tune, this resource is as good as any. Tuning can be over-whelming if you've just been given a problem, and you're new to databases. If it's a vendor product, you might want to reach it to them for support.

http://www.brentozar.com/sql/sql-server-performance-tuning/

Improve this answer
1

Technically, you can decrease max memory in SQL Server without restarting services but it will take time to for the new setting to take effect. Longer if SQL is under memory pressure.

The best approach to solve this problem, would be to move the application to it's own server. Otherwise, you need to keep 10% (up to 4gb) of the memory free for the OS, then split the remaining available memory between the application and SQL.

Improve this answer
5
  • And it will invalidate all the plans which will result in poor performance. Why do you think decreasing max memory will help here ?
    – Kin Shah
    Nov 18, 2015 at 22:06
  • @kin But apart from that there is no other option to reduce memory consumption and OP wants that. Yes you are correct it will invalidate plans even same is suggested by Remus. I dont think this answer should be downvoted.
    – Shanky
    Nov 19, 2015 at 7:03
  • @shanky this is a sledge hammer approach and provokes bad practices. Remus's answer provides a methodology for troubleshooting the problem as opposed to telling max memory can be lowered. Read his answer again and you will find the gist. HTH.
    – Kin Shah
    Nov 19, 2015 at 13:03
  • @Kin OP asked if it was possible, and I answered that it is possible. Whether or not is the best approach is irrelevant. The best approach here would be to move the app off to it's own server.
    – SQLMac
    Nov 30, 2015 at 17:21
  • @Nyteshades Well a good answer contains all the details along with a disclaimer that doing such thing could have such affects. This is a Q&A site with high amount of visitors and we tend to keep the quality of answer high. You can edit your answer to add more details.
    – Kin Shah
    Nov 30, 2015 at 17:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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