Today’s post about changing your Max Server Memory setting is to answer the call from Adam Machanic about Puzzling Situations .
A couple weeks before I headed out to the 2009 PASS Summit I encountered a puzzler of my own. One of our servers issued an alert that it had an extremely low Page Life Expectancy (PLE); like 16. All of the databases on this server had recently migrated from an older 32 bit server with 4 GB of RAM to thier current 64 bit server with 8 GB of RAM. As luck would have it, this was the source of my puzzling situation. When we migrated the dbs we inadvertantly transfered the memory configuration used in their old 32 bit server home. Who made this classic rookie error?? Yours truly. I was doing two server migrations at once and bobbled the checklists.
I rectified the situation by increasing the RAM settings from 3 GB to 6 GB:
sp_configure 'max server memory (MB)', '6144'
RECONFIGURE
Want to guess what happened when I ran the RECONFIGURE command?
I watched as the perfmon counters immediately went down! Indicating that memory usage had dropped rather than increased.
As it turns out in SQL Server 2005 (and in SQL Server 2008 as far as I know) when you run RECONFIGURE for anything you dump the procedure cache. So for example if you were to change the setting for ‘”Web Assistant Procedures” you would dump the procedure cache. If you were to change the setting for “fill factor (%)” you would dump the procedure cache.
So beware before you change a configuration setting in your Production environment and run RECONFIGURE.
So there’s my Puzzling Situations for T-SQL Tuesday #002
One Response