The Multifunctioning DBA

Dec 7 2010   9:14PM GMT

Setting Min and Max Memory settings in SQL with Powershell



Posted by: Colin Smith
Tags:
DBA
memory
Powershell
SMO
SQL Server

So today I was thinking about some of the things that are currently not set up properly on some of my servers. I got to thinking that a lot of them were never set up properly for Min and Max Server memory. Most of them, that were installed before my time, are set up with the defaults and as you all know, that is not usually the best practice.

I thought it would be nice to have a way to progamatically set those values based on how much memory the server has total. I started tooling around in Powershell using SMO and I found the settings that I was looking for. It is really pretty simple to change theses values as I have tested it on my own local SQL Server. Here is what you need to do.

[System.Reflection.Assembly]::LoadWithPartialName(‘Microsoft.SqlServer.SMO’) | out-null
$s = New-Object (‘Microsoft.SqlServer.Management.Smo.Server’) “ServerName”

$s.Configuration.MinServerMemory.ConfigValue = 1024
$s.Configuration.MaxServerMemory.ConfigValue = 1024
$s.Configuration.Alter()

The above code will set both the Min and Max Server memory settings to 1024 MB. This change will not be in affect until you restart sql server. But I now know that I can do this progamatically if I come up with a standard for these settings that is based on something that I can progamatically determine. My first thought is to do this based on the total Memory in the server and then figure out how to set both values. I will post more as I come up with it but I thought this was a nice little tidbit.

1  Comment on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • m60freeman
    You may also want to take into consideration how many instances may be running on one server. Two instances should get half the max memory value after your allowance for the OS and other processes, three instances should each get 1/3, etc. I have a development server with 6 instances running (I'd never do that in production!) so I have to be careful about this kind of thing.
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: