Setting up the Min and Max Memory options on the fly – Word of caution !


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.

Step 1

I will query the employee table which is available for the AdventureWorks2012 database

USE [AdventureWorks2012]
GO
SELECT * FROM [HumanResources].[Employee]

Step 2 

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';

Min Max Cache Relation1

Step 3

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.

Step 4

Query the plan cache again to see that its cleared

Min Max Cache Relation2

 

Conclusion

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s