Checking for MIN and MAX RAM on VMWare-based Servers

6

November 26, 2013 by Mike Hillwig

I had an incident come across my desk the other day that was rather odd. We had an application that was running slow, and my Windows team noticed that SQL Server was using all of the available RAM on the server.  Before I gave my Windows guru the Brent Ozar lecture on Task Manager being a filthy liar, I wanted to give it a look myself first. My instinct told me that we had a configuration problem because it’s very rare for my boxes to page to disk. We’re pretty conservative when setting MAX RAM. That’s when I found the culprit on the Memory tab of the server’s configuration.

ram_config

 

Yes, we had a configuration problem. I immediately set MIN and MAX RAM to 16 GB, which is our best practice for a server with 32 GB running this particular application. Within a few seconds, my Windows guru asked how I fixed it. It really was that quick of a fix. Great. But how do we prevent this from happening in the future. My Grandma Hillwig used to say that an ounce of prevention is worth a pound of cure. She was a pretty smart woman.  The first thing I did was have my team check the build documentation to make sure that this is set during server setup. I also had them check the peer review checklist to make sure this gets checked. Checklists are good and all, but I had this little itch to automate the check. This little script took me less than an hour.

set nocount on

DECLARE @v_max_server_memory int
DECLARE @v_min_server_memory int

CREATE TABLE #config
(name varchar(128),
minimum int,
maximum int,
config_value int,
run_value int)

INSERT #config
exec sp_configure

SELECT @v_max_server_memory = config_value FROM #config WHERE name = 'max server memory (MB)'
SELECT @v_min_server_memory = config_value FROM #config WHERE name = 'min server memory (MB)'

drop table #config

if @v_max_server_memory > 262144 and @v_min_server_memory < 512 and PATINDEX('%Hypervisor%',@@version) > 0
begin

DECLARE @v_recipient varchar(128)
DECLARE @v_subject varchar (128)
DECLARE @v_body varchar(2000)
SELECT @v_recipient = 'mhillwig@mycompany.com'
SELECT @v_subject = 'SQL Server Best Practices Alert'
SELECT @v_body = 'SQL Server instance ' + @@servername + ' has failed a best practices check. This server is a VM and has MIN RAM set to ' + cast(@v_min_server_memory as varchar) + ' and MAX RAM set to ' + cast(@v_max_server_memory as varchar)+'.' +
char(10) + char(10) + 'Setting MIN RAM too low will cause the VMWare balloon driver to force SQL Server to give up RAM. Setting MAX RAM too high will allow this server to start to page to disk.'

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'dbmail',
@recipients=@v_recipient,
@body=@v_body,
@subject=@v_subject ;

end

Now I would argue that one should set MIN and MAX RAM on all instances, not just those in virtual environments. However, it’s absolutely critical to do this in virtual environments. Notice that I’m looking for the word “Hypervisor” when using the @@version variable. And I’m looking for servers that have MAX RAM set higher than 256 GB of RAM. None of the instances in my environment are nearly this big. Your mileage will vary and you’ll need to modify that value.

Now that I have a script, I just need to deploy it. I tested this by running it against all of the instances in my environment with the CMS. After that, we’ll deploy it as a SQL Agent MSX job that runs on every instance once a week. The next step is to add our code segment that will send it to our service desk software and have it parse out the right configuration items.

  • 說道:我遇到的問題是 ..請問這個問題會影響甚麼嗎?是否無法讓我正常進入wordpress安裝呢??這問題困擾我好久了 感謝大大!!Your domain is not yet pnitniog to our servers, so services such as FTP, Website Builder or File Manager may not work. You need to update nameservers to ns01.000webhost.com, ns02.000webhost.com for your domain. This warning will be removed once your domain will start resolving to our servers (24-48 hours after the nameserver update). How to update the nameservers?阿湯說:意思是域名還的dns還沒對應或解析吧

  • ja johanna , het is ook een beetje krom he? een veiligheidsgordel aandoen en dan zitten breien in de auto, ik zal het niet meer doen, bedankt voor je goede zorgen, liefs marion

  • miquel – I love the dress shot early in the blog! you pay such great attention to detail!! I also love the fact that the beautiful couple met & were wed at St Bone! awesome!! 🙂

  • Novo visual, parabéns.Quanto ao marocas do marfim, já sabemos o que a casa gasta, umas vezes porque está chéché, outras porque (quase) ninguém o ouve, lá vai mandando uma bocas para iludir os ingénuos. Abraço

  • An impressive share, I just given this onto a colleague who was doing a little analysis on this. And he in reality purchased me breakfast because I discovered it for him.. smile. So let me reword that: Thnx for the deal with! But yeah Thnkx for spending the time to debate this, I really feel strongly about it and love reading more on this topic. If doable, as you turn into expertise, would you thoughts updating your blog with extra details? It’s extremely useful for me. Massive thumb up for this blog submit!

  • The mass of high quality content on this website has indeed made me realize the authority your site contains. Mind blowing posts and articles in every corner. Carry on with it.