September 19, 2017 at 7:28 am
Hello,
i am trying to set Max server memory on the SQL server memory settings, here is my current system spec:
Windows 2008 R2 64 bit
32 gigs memory
Intel Xeon Quad Core 2.50Ghz
Its a VM server.
SQL server 2012 Sp3, 64 bit standard edition
I run the following command:
exec sp_configure 'show advanced options', 1
reconfigure
exec sp_configure 'max server memory (MB)', 24000
reconfigure
exec sp_configure 'min server memory (MB)', 4096
reconfigure
however when i look at the performance on the task manager, it never goes any higher than 6 gigs... shouldnt it be at 24 gigs since i told SQL to use that much no matter what?
September 19, 2017 at 7:36 am
What you told SQL Server is that 24GB is the most memory it may use. If you don't have enough data to fill the buffers, or if you don't use enough of your data, then you'll never get to the upper limit (unless you set your minimum memory higher, but you should only do that if you know what you're doing).
John
September 19, 2017 at 7:38 am
Siten0308 - Tuesday, September 19, 2017 7:28 AMHello,i am trying to set Max server memory on the SQL server memory settings, here is my current system spec:
Windows 2008 R2 64 bit
32 gigs memory
Intel Xeon Quad Core 2.50Ghz
Its a VM server.
SQL server 2012 Sp3, 64 bit standard edition
I run the following command:
exec sp_configure 'show advanced options', 1
reconfigure
exec sp_configure 'max server memory (MB)', 24000
reconfigureexec sp_configure 'min server memory (MB)', 4096
reconfigurehowever when i look at the performance on the task manager, it never goes any higher than 6 gigs... shouldnt it be at 24 gigs since i told SQL to use that much no matter what?
SQL Server won't automatically take all memory based on the max memory setting - that's not what that setting does. It doesn't tell SQL Server to use 24 GB no matter what.
It will use memory as needed. If it needs more it will take it until hitting the 24 GB max memory setting.
Sue
September 19, 2017 at 7:42 am
ahh got ya, big thank you 🙂
September 19, 2017 at 11:57 am
John Mitchell-245523 - Tuesday, September 19, 2017 7:36 AM(unless you set your minimum memory higher, but you should only do that if you know what you're doing).
Nope.
Minimum is the amount which, once allocated, memory usage will not drop below. It is not, and has not been for several versions, the amount that will be allocated at startup.
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
September 20, 2017 at 6:44 am
Siten0308 - Tuesday, September 19, 2017 7:28 AMHello,i am trying to set Max server memory on the SQL server memory settings, here is my current system spec:
Windows 2008 R2 64 bit
32 gigs memory
Intel Xeon Quad Core 2.50Ghz
Its a VM server.
SQL server 2012 Sp3, 64 bit standard edition
I run the following command:
exec sp_configure 'show advanced options', 1
reconfigure
exec sp_configure 'max server memory (MB)', 24000
reconfigureexec sp_configure 'min server memory (MB)', 4096
reconfigurehowever when i look at the performance on the task manager, it never goes any higher than 6 gigs... shouldnt it be at 24 gigs since i told SQL to use that much no matter what?
Two things, the task manager does not tell correctly the amount of memory used by SQL Server and the second thing is why setting the min memory limit?
😎
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply