Max Server Memory

Question

Post reply

Max Server Memory

krypto69

SSChampion

Points: 13604
More actions
November 4, 2015 at 8:21 am

#306291

Hi,
Just inherited a new VMWare SQL2014 server.
I noticed the max server memory is set to 2147483647 MB. Which I realize now is the default.
I know from the sysadmin that the VM host has a total of 196 GIGs of memory - for all the servers.
I also know the amount of memory allocated for this SQL server is 64 Gigs.
This server is the primary in an Availability group.
Do I change the max or min settings..do I change it on both primary and secondary? The secondary server runs SSIS and SSRS.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Beatrix Kiddo SSC-Dedicated Points: 32407 More actions · Answer 1

Beatrix Kiddo

SSC-Dedicated

Points: 32407

November 4, 2015 at 8:44 am

#1837702

Yep, that's just the default value

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

You change it at the server level, availability groups are at the database level, they don't transfer server settings. Change it on all servers, it might not be set to the same value on the secondary since the secondary has SSRS

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

krypto69 SSChampion Points: 13604 More actions · Answer 3

Thanks Gail.

My understanding is that in most cases the max memory should be changed from the default.

Is this correct, and does the VM ware admin need to make changes if I change the max memory?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Always set max server memory. It's not the VMWare admin's job, it's the DBA's job, it's changed in SQL's server settings.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Perry Whittle SSC Guru Points: 234013 More actions · Answer 5

krypto69 (11/4/2015)
Hi,
Just inherited a new VMWare SQL2014 server.
I noticed the max server memory is set to 2147483647 MB. Which I realize now is the default.
I know from the sysadmin that the VM host has a total of 196 GIGs of memory - for all the servers.
I also know the amount of memory allocated for this SQL server is 64 Gigs.
This server is the primary in an Availability group.
Do I change the max or min settings..do I change it on both primary and secondary? The secondary server runs SSIS and SSRS.

Assigning over 1\4 of the host memory resources to the one VM really is an argument for making this a physical node in the first place

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Beatrix Kiddo SSC-Dedicated Points: 32407 More actions · Answer 6

As Gail's already said, it's the DBA's job to set the max server memory. It's usually one of the post-installation steps, and the value depends on various factors including how much RAM the machine has, and how many instances it's hosting.