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.

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.

[SQL]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

[/SQL]

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.