Apparently,setting up the Min and Max Memory setting for SQLServer should be definitely encouraged and no server should be left out with the default settings.But here is a quick word of caution around this -
Do you want to change these numbers on the fly randomly when there is good amount of load on the production system ?
The answer is ‘No’. You shouldn’t be changing these numbers on the fly for your random testing on a production box with good amount of load.
Changing these numbers using sp_configure and hardening the changes using a reconfigure or doing it via GUI will clear the plan cache, and that’s not something which you would like to happen on a busy production box.
Let me show a quick demo to explain this.
I will query the employee table which is available for the AdventureWorks2012 database
USE [AdventureWorks2012] GO SELECT * FROM [HumanResources].[Employee]
The above query will cause an adhoc plan to be stored in the cache. We can verify this by querying the plan cache
SELECT st.text , qp.query_plan , cp.cacheobjtype , cp.objtype , cp.plan_handle FROM sys.dm_exec_cached_plans cp CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp WHERE objtype = 'Adhoc';
I will change the Min Server Memory of the server using sp_configure
sp_configure 'Min Server Memory (MB)',501 RECONFIGURE
As soon as I run this command,I can check the sql server error log to see messages like
SQL Server has encountered 1 occurrence(s) of cachestore flush for the ‘Object Plans’ cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
SQL Server has encountered 1 occurrence(s) of cachestore flush for the ‘SQL Plans’ cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
SQL Server has encountered 1 occurrence(s) of cachestore flush for the ‘Bound Trees’ cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
Query the plan cache again to see that its cleared
Changing instance level settings needs to done with care and if possible these should be done during a proper change/maintenance window for production systems.
Thanks for reading and keep watching this space for more.