Have you seen this type of an informational message in your SQL errorlog before? If so, then I hope the timestamp associated with it was not during the middle of your peak processing time. This is a good indication that someone has been tinkering with some of the SQL configuration settings.
SQL Server offers a lot of wonderful configuration options; many of which can be changed dynamically using sp_configure without the need of restarting the SQL Server service. The downside to the dynamic changes is sometimes they can have a detrimental effect on performance, and that’s what this “informational” message is tell you. Let’s look at an example.
First, let’s get our starting numbers for the cachestores.
SELECT
COUNT(*) AS ‘Total Cached Plans’
FROM sys.dm_exec_cached_plans;
GO
SELECT
name AS ‘Cache Name’
,single_pages_kb
,multi_pages_kb
,entries_count
FROM sys.dm_os_memory_cache_counters
WHERE name IN (‘Object Plans’,‘SQL Plans’,‘Bound Trees’);
GO
As you can see, we have 160 total plans in cache using up the amount of single and multi-page allocations for each entry.
Now let’s say your system administrator came to you today and said he hot-added extra ram to the SQL box because of how much you had been complaining about memory shortages. You are so excited about this, you login to the server and execute the following query to bump SQL’s maximum memory up to 4GB.
EXEC sys.sp_configure N’max server memory (MB)’,N’4000′; GO RECONFIGURE; GO
Now you look in the SQL errorlog to valid the changes.
You are excited to see this message.
Configuration option ‘max server memory (MB)’ changed from 2000 to 4000. Run the RECONFIGURE statement to install.
And not so excited about these.
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.
Let’s run our cachestore query again and look at the results.
Ah proof that we just cleared the entire procedure cache. Having these cachestores cleared out means you just lost all compiled plans for stored procedures, functions, triggers, views, adhoc plans, prepared plans, etc. The next time any query runs, its plan will have to be compiled costing extra CPU cycles and potentially degraded performance.
As it turns out, for certain configuration settings when you execute the RECONFIGURE command it essentially does what amounts to DBCC FREEPROCCACHE.
The whole procedure cache is cleared if one of the following server options is changed by the RECONFIGURE statement:
- cross db ownership chaining
- index create memory (KB)
- remote query timeout (s)
- user options
- max text repl size (B)
- cost threshold for parallelism
- max degree of parallelism
- min memory per query (KB)
- query wait (s)
- min server memory (MB)
- max server memory (MB)
- query governor cost limit
This is a very good example of why you should NOT make configuration changes during peak processing times. You wouldn’t run DBCC FREEPROCCACHE during the middle of your peak processing, so why would you play around with configuration settings during that same time?
For further reading: http://support.microsoft.com/kb/917828
