November 9, 2011 at 12:55 am
Hi All
Having a debate with a colleague at work regarding memory allocation for a server in production and was hoping to gather some general feedback!
The debate is over the below statement:
Should you match the amount of sql memory to the total size of databases you are storing on the server?
-So if you have 150gb on disk of databases on your sql box you should have atleast 150gb assigned to sql server memory!
I think this is a silly statement and completely disagree.
Thanks
Dom 😀
November 9, 2011 at 1:00 am
I'd call that a utopia state, but one that I've almost never seen.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 9, 2011 at 1:05 am
yes definitely nice to have but unrealistic.
performance would not be 200% better either its not like the raw table data sits in memory..
November 9, 2011 at 1:10 am
thedom4302 (11/9/2011)
performance would not be 200% better either its not like the raw table data sits in memory..
Err, that's exactly what does happen, whether you have more memory than data or less. The raw data pages are cached in the data cache.
200% better, highly unlikely. SQL prioritises caching for active data pages as much as it can, so with less memory than data the frequently used pages are usually the ones in memory
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 9, 2011 at 1:11 am
The debate is over the below statement:
Should you match the amount of sql memory to the total size of databases you are storing on the server?
-So if you have 150gb on disk of databases on your sql box you should have atleast 150gb assigned to sql server memory!
:w00t: Really :hehe:
November 9, 2011 at 1:12 am
didnt realise that one 😀
what would be a good percentage to have of memory to database size ?
November 9, 2011 at 1:19 am
what would be a good percentage to have of memory to database size ?
There is no straight formula for it (I wish to have it). It mostly depends on Data Volume & it's usage in SQL Server.
November 9, 2011 at 1:21 am
thanks for the feedback all
November 9, 2011 at 1:30 am
thedom4302 (11/9/2011)
what would be a good percentage to have of memory to database size ?
No hard and fast number there. Depends on the usage patterns, the amount of data that's frequently accessed, etc. One thing I can say thought - the more memory the better. As far as SQL Server is concerned, there's no such thing as too much memory.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply