| Author |
Topic |
|
ranvir_2k
Posting Yak Master
180 Posts |
 Posted - 2011-05-11 : 16:48:29
|
| How does Max and Minimum memory work in SQL Server? For example if I set the minimum memory for SQL Server to 2GB and maximum memory to 10GB, does this mean that SQL Server will always take a minimum of 2GB of memory and if need be (due to the amount of processing) it can take up to 10GB? Or does it mean that SQL Server will always take up 10GB of memory?Thanks |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
ranvir_2k
Posting Yak Master
180 Posts |
Posted - 2011-05-11 : 16:55:41
|
| Thanks for the reply. How can I see exactly how much memory SQL Server is using? |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
ranvir_2k
Posting Yak Master
180 Posts |
Posted - 2011-05-11 : 17:39:47
|
| Do you know which counter I should use in PerfMon? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
ranvir_2k
Posting Yak Master
180 Posts |
Posted - 2011-05-11 : 18:25:12
|
| Many thanks for your help Tara, it's much appreciated. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-05-11 : 18:26:02
|
| The minimum is not the amount that SQL will allocate on startup. That was true in SQL 2000, not any longer. It's the amount that, once it has allocated, it will not drop below. So, if you set a min of 2GB, SQL has 4GB allocated and the OS says 'reduce your memory usage', SQL will reduce to a minimum of 2GB.Both numbers apply to the buffer pool alone, there is additional memory that SQL will take above and beyond the buffer pool.--Gail ShawSQL Server MVP |
|
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2011-05-11 : 19:22:25
|
| Another way to get the performance counters, instead of using perfmon: Select * ,dopc.cntr_value / 1024 As 'Total (MB)' From sys.dm_os_performance_counters dopc Where dopc.counter_name In ('Target Server Memory (KB)', 'Total Server Memory (KB)')Jeff |
|
|
|
ranvir_2k
Posting Yak Master
180 Posts |
Posted - 2011-05-12 : 05:33:12
|
| So SQL Server will always allocate the maximum configured memory when possible? |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-05-12 : 06:28:13
|
| No. SQL will allocate the memory it needs, up to the max memory setting. There's a good chance it'll allocate all it's allowed to, but not always.--Gail ShawSQL Server MVP |
|
|
|
ranvir_2k
Posting Yak Master
180 Posts |
Posted - 2011-05-12 : 06:41:14
|
| If the performance counter MSSQL$InstanceName\Memory Manager\Total Server Memoryis showing as 100% does that mean the maximum configured SQL Server memory has been allocated? |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-05-12 : 10:16:59
|
| Total server memory is not a %. It's the amount of memory. I believe it's measured in MB.--Gail ShawSQL Server MVP |
|
|
|
lionofdezert
Aged Yak Warrior
885 Posts |
Posted - 2011-05-12 : 10:39:23
|
| i think MSSQL$InstanceName\Memory Manager\Total Server Memory shows memory in KB with a scale 0.0001--------------------------http://connectsql.blogspot.com/ |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2011-05-12 : 11:39:45
|
Yes KB. It's actually in the name too, I just left it off: MSSQL$InstanceName\Memory Manager\Total Server Memory (KB)quote:
Originally posted by ranvir_2k
If the performance counter MSSQL$InstanceName\Memory Manager\Total Server Memoryis showing as 100% does that mean the maximum configured SQL Server memory has been allocated?
If you are seeing it use the maximum size in KB, then yes it is fully allocated. You'll almost always find SQL Server using the max. Right after a restart, it won't be, but it'll get there in a few hours usually.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
|
SQL Server max and min memory