July 3, 2017 at 1:11 am
Hi All,
How do we know how much memory does SQL server Really need ?
We have 12 GB memory in a server and we set the limit 8GB . From Performance counter ,Total server memory shows 8 GB . As long as I know sql will consume as muchmemory as it can eat …. Can I go lower than 8 GB ?
Atthe same time VMware shows very low ram utilisation, implying sql server may beusing too much ?
Moreover we have report server there .
How much memory that we should limit this report server ?
as it will also consume memory apart from SQL server itself
July 3, 2017 at 1:41 am
WhiteLotus - Monday, July 3, 2017 1:11 AMHi All,
How do we know how much memory does SQL server Really need ?
All of it.
12 GB memory's pretty low for a server, my laptop's got more.
You can go lower than 8GB, all you're doing is degrading performance. The memory is used as a cache to avoid having to make expensive calls to disk. Less memory, more reads have to be from disk, worse performance will be.
By 'report server', do you mean SSRS, or another SQL instance?
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
July 3, 2017 at 8:21 am
12GB is low, unless this is a small database and few users. I give instances 4GB often just for me.
What is target server memory saying (Perf mon)?
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
July 3, 2017 at 3:46 pm
12 GB is lower than any SQL Server I've ever worked with since SQL 2000.
It depends on your workload, but I wouldn't want to buy one with less that 64 GB. I mean that's a minimum. I've heard of instances with more than 1 TB of memory, but haven't had the pleasure of working with one.
What Gail said about the buffer pool says it all. High memory use is a feature of SQL Server, not a problem with it.
July 3, 2017 at 6:43 pm
GilaMonster - Monday, July 3, 2017 1:41 AMWhiteLotus - Monday, July 3, 2017 1:11 AMHi All,
How do we know how much memory does SQL server Really need ?
All of it.
12 GB memory's pretty low for a server, my laptop's got more.
You can go lower than 8GB, all you're doing is degrading performance. The memory is used as a cache to avoid having to make expensive calls to disk. Less memory, more reads have to be from disk, worse performance will be.By 'report server', do you mean SSRS, or another SQL instance?
Thanks for the reply Gail
July 3, 2017 at 6:50 pm
Steve Jones - SSC Editor - Monday, July 3, 2017 8:21 AM12GB is low, unless this is a small database and few users. I give instances 4GB often just for me.What is target server memory saying (Perf mon)?
Thanks for the reply !
This morning I just checked again
Total server memory = 7392 MB = 7.3 GB
Target server memory = 7700 MB = 7.6 GB
July 3, 2017 at 6:51 pm
Guys I am still wondering why At the same time VMware shows very low ram utilisation ? That's why it is hard to get the memory increment to that server ...
July 3, 2017 at 7:40 pm
Exactly what metrics are you using?
I'm guessing either active or usage (which is directly based on active).
A "low" value for those is NOT a good reason not to add memory to a SQL Server VM.
Those are basically tracking the number of pages of memory touched recently (the length of the sampling interval for your view, 20 seconds in real-time view, for example), and even then it's just an estimate (actually tracking it all is too expensive).
There is some use to knowing, say, that you touched 10% of the VM's memory in the last minute, but it tells you very little about what memory you could use for SQL Server.
For SQL Server, it's not how much you've touched of the memory you already have that's important, but how much physical IO you're driving that could be prevented with additional memory.
The active and usage metrics are thus almost the opposite of what you should care about.
As others have said, you almost certainly could use more memory given how little you have.
Track PLE and IO stats. If you're churning through your buffer pool frequently and pushing a decent amount of physical IO, you could use more memory.
There are more detailed analyses you can do, but that should be a good start.
Cheers!
July 3, 2017 at 9:54 pm
Jacob Wilkins - Monday, July 3, 2017 7:40 PMExactly what metrics are you using?I'm guessing either active or usage (which is directly based on active).
A "low" value for those is NOT a good reason not to add memory to a SQL Server VM.
Those are basically tracking the number of pages of memory touched recently (the length of the sampling interval for your view, 20 seconds in real-time view, for example), and even then it's just an estimate (actually tracking it all is too expensive).
There is some use to knowing, say, that you touched 10% of the VM's memory in the last minute, but it tells you very little about what memory you could use for SQL Server.
For SQL Server, it's not how much you've touched of the memory you already have that's important, but how much physical IO you're driving that could be prevented with additional memory.
The active and usage metrics are thus almost the opposite of what you should care about.
As others have said, you almost certainly could use more memory given how little you have.
Track PLE and IO stats. If you're churning through your buffer pool frequently and pushing a decent amount of physical IO, you could use more memory.
There are more detailed analyses you can do, but that should be a good start.
Cheers!
Thanks for the reply
The Life page expectancy is around 11000
What do you think ?
July 10, 2017 at 4:47 am
WhiteLotus - Monday, July 3, 2017 9:54 PMJacob Wilkins - Monday, July 3, 2017 7:40 PMExactly what metrics are you using?I'm guessing either active or usage (which is directly based on active).
A "low" value for those is NOT a good reason not to add memory to a SQL Server VM.
Those are basically tracking the number of pages of memory touched recently (the length of the sampling interval for your view, 20 seconds in real-time view, for example), and even then it's just an estimate (actually tracking it all is too expensive).
There is some use to knowing, say, that you touched 10% of the VM's memory in the last minute, but it tells you very little about what memory you could use for SQL Server.
For SQL Server, it's not how much you've touched of the memory you already have that's important, but how much physical IO you're driving that could be prevented with additional memory.
The active and usage metrics are thus almost the opposite of what you should care about.
As others have said, you almost certainly could use more memory given how little you have.
Track PLE and IO stats. If you're churning through your buffer pool frequently and pushing a decent amount of physical IO, you could use more memory.
There are more detailed analyses you can do, but that should be a good start.
Cheers!
Thanks for the reply
The Life page expectancy is around 11000
What do you think ?
While that seems great, I've found PLE itself usually a useless indicator.
I feel you should be tracking SQL's memory usage, this varies dramatically throughout normal working loads - it can use 1gb one minute, nearly the whole amount the next. This would give you the proof needed to show the box is / is not under pressure.
July 10, 2017 at 10:20 pm
JaybeeSQL - Monday, July 10, 2017 4:47 AMWhiteLotus - Monday, July 3, 2017 9:54 PMJacob Wilkins - Monday, July 3, 2017 7:40 PMExactly what metrics are you using?I'm guessing either active or usage (which is directly based on active).
A "low" value for those is NOT a good reason not to add memory to a SQL Server VM.
Those are basically tracking the number of pages of memory touched recently (the length of the sampling interval for your view, 20 seconds in real-time view, for example), and even then it's just an estimate (actually tracking it all is too expensive).
There is some use to knowing, say, that you touched 10% of the VM's memory in the last minute, but it tells you very little about what memory you could use for SQL Server.
For SQL Server, it's not how much you've touched of the memory you already have that's important, but how much physical IO you're driving that could be prevented with additional memory.
The active and usage metrics are thus almost the opposite of what you should care about.
As others have said, you almost certainly could use more memory given how little you have.
Track PLE and IO stats. If you're churning through your buffer pool frequently and pushing a decent amount of physical IO, you could use more memory.
There are more detailed analyses you can do, but that should be a good start.
Cheers!
Thanks for the reply
The Life page expectancy is around 11000
What do you think ?While that seems great, I've found PLE itself usually a useless indicator.
I feel you should be tracking SQL's memory usage, this varies dramatically throughout normal working loads - it can use 1gb one minute, nearly the whole amount the next. This would give you the proof needed to show the box is / is not under pressure.
For physical servers, I would set the SQL memory at 8 GB. (Min : 2 GB Max 8 GB)
For VMs, I would set the following : (Min : 4 GB Max 8 GB) -- VMs tend to be slower.
I would monitor the performance indicator to see if you need additional memory.
Cheers
July 10, 2017 at 10:24 pm
cyrusbaratt - Monday, July 10, 2017 10:20 PMJaybeeSQL - Monday, July 10, 2017 4:47 AMWhiteLotus - Monday, July 3, 2017 9:54 PMJacob Wilkins - Monday, July 3, 2017 7:40 PMExactly what metrics are you using?I'm guessing either active or usage (which is directly based on active).
A "low" value for those is NOT a good reason not to add memory to a SQL Server VM.
Those are basically tracking the number of pages of memory touched recently (the length of the sampling interval for your view, 20 seconds in real-time view, for example), and even then it's just an estimate (actually tracking it all is too expensive).
There is some use to knowing, say, that you touched 10% of the VM's memory in the last minute, but it tells you very little about what memory you could use for SQL Server.
For SQL Server, it's not how much you've touched of the memory you already have that's important, but how much physical IO you're driving that could be prevented with additional memory.
The active and usage metrics are thus almost the opposite of what you should care about.
As others have said, you almost certainly could use more memory given how little you have.
Track PLE and IO stats. If you're churning through your buffer pool frequently and pushing a decent amount of physical IO, you could use more memory.
There are more detailed analyses you can do, but that should be a good start.
Cheers!
Thanks for the reply
The Life page expectancy is around 11000
What do you think ?While that seems great, I've found PLE itself usually a useless indicator.
I feel you should be tracking SQL's memory usage, this varies dramatically throughout normal working loads - it can use 1gb one minute, nearly the whole amount the next. This would give you the proof needed to show the box is / is not under pressure.
For physical servers, I would set the SQL memory at 8 GB. (Min : 2 GB Max 8 GB)
For VMs, I would set the following : (Min : 4 GB Max 8 GB) -- VMs tend to be slower.I would monitor the performance indicator to see if you need additional memory.
Cheers
Thanks Mate
July 11, 2017 at 8:33 am
WhiteLotus - Monday, July 3, 2017 1:11 AMHi All,
How do we know how much memory does SQL server Really need ?
We have 12 GB memory in a server and we set the limit 8GB . From Performance counter ,Total server memory shows 8 GB . As long as I know sql will consume as muchmemory as it can eat …. Can I go lower than 8 GB ?
Atthe same time VMware shows very low ram utilisation, implying sql server may beusing too much ?
Moreover we have report server there .
How much memory that we should limit this report server ?
as it will also consume memory apart from SQL server itself
So you're leaving just 4GB of RAM for the OS and the SSRS instance, that's quite low.
What sort of size is the sql server database on the database engine instance
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 11, 2017 at 8:37 am
Hi,
are you experiencing slow response time with the current configuration ?
You reported : 6 main users plus some business users, so the load on both engines should be quite low.
My suggestion is to slightly upgrade the memory and set the following configuration :
-- Physical RAM Max Server Memory Setting
-- 16GB 11400
and, as you suggested, 2 GB for SSRS.
But, if the server is working good and you can't enlarge the memory, you could keep the config you set.
Alex.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply