Sql-server – Is it recommended to clear the query plan cache

performancequery-performancesql serversql-server-2008

Pretty much as the title describes. I have just checked a SQL Server and noticed there is a lot of query plan bloat that can be fixed by proper parametrisation.

After making the changes to the code, is it recommended that we clear the query plan cache and let it rebuild itself? Or will it clear itself up in time?

NB: Out of the top 50 biggest queries, 47 of them were the same but with different parameters.

Best Answer

I would recommend against this. You have done optimization on probably a small subset of queries, and over time you will see the improvement and performance gain. But if you just do a clean refresh of the query plan cache you will blow all of the execution plans out of there, affecting more than just your optimizations.

I'd be willing to say the immediate performance degradation isn't worth the smaller gain of using your optimized plans right away. But again, this is another "it depends".