Set Memory Usage Percentage

One of the things I did recently was to set the max memory usage on all the servers, via script. We wanted to set it to 75% of the total available physical memory on the server, so the OS would have something left to run with. Here's how it was done:

use admin
go

--Create the scripts table, if it does not exist. 
if not exists 
  (select table_name 
  from admin.information_schema.tables 
  where table_name = 'scripts')
  begin
    create table scripts (
      lines nvarchar(4000) not null
    )
  end

--Clear the scripts table.
truncate table scripts 

--Insert the script.  This is a script I got off of MSDN at
--http://msdn2.microsoft.com/en-us/library/aa394587.aspx.  This
--bit here is my standard methodology for inserting outside 
--scripts into my T-SQL code.
insert into scripts 
select N'strComputer = "."' union all
select N'Set objWMIService = GetObject("winmgmts:" _' union all
select N'& "{impersonationLevel=impersonate}!\\" _' union all
select N'& strComputer & "\root\cimv2")' union all
select N'Set colSettings = objWMIService.ExecQuery _' union all
select N'("Select * from Win32_ComputerSystem")' union all
select N'For Each objComputer in colSettings ' union all
select N'Wscript.Echo "System Name: " & objComputer.Name' union all
select N'Wscript.Echo "Total Physical Memory: " & _' union all
select N'objComputer.TotalPhysicalMemory' union all
select N'Next' 

--Export the script to a file.
declare @cmd nvarchar(4000)
set @cmd = 'bcp admin.dbo.scripts out "C:\memory.vbs" -T -c'
exec master.dbo.xp_cmdshell @cmd

--Create a place to hold the results. 
create table #memresults (results nvarchar(4000) null)

--Run the script, inserting the results to the temp table.
insert into #memresults 
  exec master.dbo.xp_cmdshell 'cscript C:\memory.vbs'

--Pull the memory amount from the temp table, using a replace 
--for the line that holds the number we want.
declare @mem bigint
set @mem = 
  (select replace(results, 'Total Physical Memory: ', '') 
  from #memresults where results like 'Total Physical Memory: %')

--Compute the memory needed from the result, which is in bytes.
--(bytes/1024)/1024 = megabytes.  Multiply that by 0.75, or 75%
--for the max amount of RAM we want to give to SQL.
declare @maxmem bigint
set @maxmem = (((@mem/1024)/1024) * 0.75)

--Assign the memory to SQL.
--Turn advanced options on, just in case it isn't already.
exec master.dbo.sp_configure 'show advanced options', 1
reconfigure
--Set the minimum ram to 0.
exec master.dbo.sp_configure 
  @configname = 'min server memory (MB)', 
  @configvalue = 0
reconfigure with override
--Set the Maximum ram to the figure we just calculated.
exec master.dbo.sp_configure 
  @configname = 'max server memory (MB)', 
  @configvalue = @maxmem
reconfigure with override
go

--Clean up your mess.  Get rid of the script and the 
--temp table.
exec master.dbo.xp_cmdshell 'del C:\memory.vbs'
drop table #memresults
go

Now, why do this in a script, rather than just going into the SQL Enterprise Manager configuration panel and doing it? Imagine doing this on 300 servers, in 4 different time zones, in about 20 minutes. Yeah, that's why. Using my push script, which will be published here soon, this took around 20 minutes for all 300+ servers.

Moving on...

EDIT: 8/29, 9:00 AM = Changed the @mem variables from 'int' type to 'bigint'. Otherwise, any computer with more than 2GB of RAM will cause an overflow. :-)