Beware: Using sp_configure to change a Value Involves DBCC FREEPROCCACHE

I recently learned that when you call sp_configure to set a value for any option, SQL Server issues a DBCC FREEPROCCACHE command. This command invalidates all stored procedure plans currently cached and requires recompilation of new plans the next time the stored procedure runs. Sp_configure issues this DBCC command because changing some options that sp_configure supports can invalidate stored procedure plans. For example, if you change the max degree of parallelism option, SQL Server treats all parallel plans as invalid. Although issuing DBCC FREEPROCCACHE is a convenient, simple way to ensure that all plans stay consistent with the current settings of sp_configure, I think this behavior is overkill because sp_configure also lets you set options that don't require the invalidation of existing plans.

In a typical SQL Server implementation, sp_configure's execution of DBCC FREEPROCCACHE won't produce a noticeable effect. However, you might see performance degradation at high-end sites that perform tens of thousands of transactions per second. Because the effects of this command relate to how many procedures are running, avoid using sp_configure during peak production periods. Just be aware of this quirk, particularly if you conduct performance and scalability tests in which you manually issue a DBCC FREEPROCCACHE command as part of your testing protocol. Your results might vary dramatically if sp_configure has been making calls to DBCC FREEPROCCACHE that you didn't know about.