Using Sp_configure To Change a Value Will Issue DBCC FREEPROCCACHE

Many people don't know that using sp_configure to change a configuration value causes SQL Server to issue a DBCC FREEPROCCACHE statement. DBCC FREEPROCCACHE will invalidate all stored procedure plans that the optimizer has cached in memory and force SQL Server to compile new plans the next time those procedures are run. To be honest, this is unlikely to have a noticeable effect on a typical SQL Server site. Yes, there will be some performance overhead as SQL Server recompiles all the plans, but it's no more overhead than you'd get from restarting SQL Server. In fact, you'd have considerably less overhead using sp_configure because a restart would clear the data cache in addition to the procedure cache. However, some high-end sites that perform tens of thousands of transactions per second could easily see performance slow while SQL Server generates new plans for the procedures. The total effect would be tied to how many procedures your applications use. A site using 10 procedures will see less overhead than a site using 5000 procedures. But even for large sites, this overhead would be manageable. However, you certainly want to avoid issuing a DBCC FREEPROCCACHE during a period of peak production usage if you can avoid it.

You might wonder why sp_configure calls DBCC FREEPROCCACHE. You can invalidate procedure plans by changing options that sp_configure supports. For example, if you change the max degree of parallelism option, SQL Server has to treat all procedure plans as invalid. Issuing DBCC FREEPROCCACHE is a convenient and simple way for SQL Server to ensure that all plans are consistent with sp_configure's current settings. You can see how and when the procedure issues DBCC FREEPROCCACHE by reading the T-SQL text associated with sp_configure.

The same behavior will occur if you use a GUI such as SQL Server Enterprise Manager that issues an sp_configure call that changes a parameter. For example, many of the options available from the Properties dialog of Enterprise Manager actually call sp_configure behind the scenes. I'm not suggesting that you become paranoid about using this procedure. SQL Server 2000 has been out for years and this behavior has never been much of a problem. But it's a good thing to be aware of—especially for high-volume online transaction processing (OLTP) sites that use many complex stored procedures. This behavior is also important to consider if you're doing controlled performance testing. Your final results could be skewed if you issue sp_configure statements during the test and don't realize that DBCC FREEPROCCACHE is executing behind the scenes.