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 one question about SQL Server memory usage.

When I start my SQL Server, it starts consuming memory and as the time goes (2-3 hours) sqlservr.exe process consumes all the memory, leaving just 100 MB free for windows operations.

I have seen in the perfmon, the counter "SQL Server:Memory counter" shows only 5% of usage. So why the sqlservr.exe consumes all the memory even if it is using only 5% of it?

What I want to do is sqlservr.exe to consume memory as per its need, not in advance. And I also don't want to put limit on "max server memory".

Thank you all

share|improve this question

migrated from stackoverflow.com Oct 11 '12 at 14:26

This question came from our site for professional and enthusiast programmers.

2  
SQL Server is designed predominantly to live on a server installation by itself - so it's designed to be greedy with memory. max server memory is about the only widget that MS provide to you to reign in this behaviour. –  Damien_The_Unbeliever Feb 18 '12 at 5:52
1  
SQL Server will use all the memory it can get - unless you limit it by specifying a max server memory setting. So either you put a limit in there, or you live with the fact it'll gulp up all the memory it can get. You cannot limit SQL Server without setting a max server memory (that's the exact REASON that this setting exists!) –  marc_s Feb 18 '12 at 9:46

1 Answer 1

SQL Server will cache esentially everything it can in order to avoid (slow) disk access. So, as your instance is used, SQL starts saving commonly used objects in the buffer, until the the allotted memory is full.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.