Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Analyzing Memory Requirements for SQL Server


Analyzing Memory Requirements for SQL Server

Author
Message
Arindam Banerjee
Arindam Banerjee
Valued Member
Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)

Group: General Forum Members
Points: 53 Visits: 1
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/aBanerjee/analyzingmemoryrequirementsforsqlserver.asp
Vladan
Vladan
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2133 Visits: 754

I am a bit surprised by some things in this article... for example:

"Generally, the rule of thumb is to have as much as RAM as your data file is."

Is it really so? Isn't there some limit from where this rule does not apply? Our data file has over 200 GB, but I have never seen a server with that much memory. Obviously, it would be terribly expensive... I don't think databases around this size are rare, so what is your opinion about their memory requirements? We are running this DB on a box with 8GB RAM; I suppose it would work better with more memory, but the performance (with 20-40 concurrent users) is quite good.





Ravi Prashanth Lobo-275382
Ravi Prashanth Lobo-275382
SSC Rookie
SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)

Group: General Forum Members
Points: 48 Visits: 1
Good article! Could learn some new things.
Arindam Banerjee
Arindam Banerjee
Valued Member
Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)

Group: General Forum Members
Points: 53 Visits: 1

Hi Vladan,

The general idea is to get the data in the cache whenever the need arises. It certainly does not mean that if I have a TB of data file, then I would have to have that much of RAM, but when I have a database where I am getting frequent performance problem and I want to troubleshoot the issue, then I would certainly look into the rule of thumb. If I have a 8GB data file and 8GB of memory, I would assume that the memory is OK, and jump into the other aspects, like CPU, Network or Disk for troubleshooting.

On the other side, I have given some perfmon counters which will help you to identify if you really require additional memory. The rule of thumb is just to give you a hint to look for when you do not have adequete time for investigation.

Hope this clarifies the issue.

Thanks for taking time to read and give your valuable feedback.

~Arindam.


Arindam Banerjee
Arindam Banerjee
Valued Member
Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)

Group: General Forum Members
Points: 53 Visits: 1

Hi Ravi,

Thanks for your time. Glad to know that it increased your knowledge. Hope to write more on capacity management. Please check those too.

Thanks,

~Arindam.


Ravi Prashanth Lobo-275382
Ravi Prashanth Lobo-275382
SSC Rookie
SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)

Group: General Forum Members
Points: 48 Visits: 1

I have read both your articles. second one is more useful for me. Will be waiting for future articles from you.

Thank you.


sergio furtado-343184
sergio furtado-343184
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 Visits: 42
Ravi, thks for the time in writing this. However I would need some more data for the article to really hit the spot to be honest. For instance, how much does the sql server kernel actually take in memory (just the core functionality disregarding any user db). And how much do instances take? I know it's wrong to assume that 2 instances of the with the exact same data and load will take 2x the memory. And would you be able to quantify how much improvement (if any) can be expected in running sql server as a background process in an OLTP scenario - i really don't see a correlation here you might be assuming something I am not.

Totally agree with you on the collecting data for baselines,
thanks for posting article,
you're a braver man than me,
SNMF
Ravi Prashanth Lobo-275382
Ravi Prashanth Lobo-275382
SSC Rookie
SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)

Group: General Forum Members
Points: 48 Visits: 1

>>Ravi, thks for the time in writing this. However I would

Sergio, I think you are talking to the wrong guy! It should be ...Arindam....


Arindam Banerjee
Arindam Banerjee
Valued Member
Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)

Group: General Forum Members
Points: 53 Visits: 1

Hi Sergio,

Thanks for reading this article. The first question which you have is really a tricky one. I may not be correct on commenting in respect to this question because I did not find any dependable resource, but for my server, it takes 80-150mb approx for the kernel to run. However, you can adjust the MIN SERVER MEMORY and MAX SERVER MEMORY and see how much memory allocation will be optimum for you requirement. Once again, this comment is solely based on my observation and may be different than actual.

For the second question, every connection will take 64bytes of memory. It is not wrong, to assume that 2 instances connected to the same server and database will consume 2x memory! Actually, if you have n instances open for any server, the server will reserver 64n bytes of memory. Now, this brings us to the important consideration for the connection memory utilised and we do have a counter for that too in our favourite perfmon! :-)

The answer for the third qustion is to liverage your operating system architecture. In Windows operating system, we can run an application as a foreground process or a background process. If we have an application which is not accessed by foreground, then it will be convenient for the OS and the App to run in background. If you want to run any foreground process on that server, other that SQL apps, the OS will be more efficient on managing the memory. Once again, I am not in the place to comment on the actual benefit it would cause, but can certainly say that it would be apt for the process and you would be getting the benefits in certain scenario as discussed above.

Hope this clarifies some part of your doubts. Please do write if you have more questions.

Sincerely,

~Arindam (not Ravi) :-)


Ken Shapley
Ken Shapley
SSC Rookie
SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)

Group: General Forum Members
Points: 44 Visits: 173

"Generally, the rule of thumb is to have as much as RAM as your data file is."

Did you come up with this rule yourself? How many databases have you worked with in your career?


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search