The Risks Of Clearing The Procedure Cache

Erin Stellato explains two downsides to running DBCC FREEPROCCACHE or anything else which clears query plans:

Ideally, you should remove only what’s absolutely necessary.  Using DBCC FREEPROCCACHE is a sledgehammer approach and typically creates a spike in CPU as all subsequent queries need to have their plans re-generated.  Glenn gives examples on how to use each statement (and others) in his post Eight Different Ways to Clear the SQL Server Plan Cache, and I want to show you one more thing that happens when you clear a plan (or all plans) from cache.

For this demo script, I recommend running it against a TEST/DEV/QA environment because I am removing plans from cache which can adversely affect performance.

There are reasons to run these commands, but ideally, you should be as precise as possible in clearing plans out of the cache.

Related Posts

Tracking Long-Running Queries

Ryan Booz walks us through tracking long-running queries with sp_whoisactive: This solution runs sp_WhoIsActive every minute and saves the output into a global temp table. From there, I look for any processes that have been running for more than the low threshold setting. Any of the processes that have not been identified and stored previously get logged, output […]

Read More

Restoration With Replacement

Joey D’Antoni tests whether RESTORE WITH REPLACE is functionally different from dropping a database and performing a restoration: I recently read something that said using the RESTORE WITH REPLACE command could be faster than dropping a database and then performing a RESTORE, because the shell of the file could be used and therefore skip file […]

Read More

Categories

July 2017
MTWTFSS
« Jun Aug »
 12
3456789
10111213141516
17181920212223
24252627282930
31