DBCC CHECKDB flusing the cache!
Well another cache related blog in a row since last few days! 
I have had weird issues on one of the SQL Server 2005 SP1 instance that, whenever DBCC CHECKDB is executed the performance of particular stored procedure (complexed calculations) has gone down to sea bed!
To ensure my findings using DMVs and the cache relevant blog post here I was able to find this particular stored procedure is always on the top whenever the weekly database housekeeping tasks that includes CHECKDB process task is completed. Though no issues reported on the aspect of consistency of this database with our weekly checks. So where is the trouble!
As always and for a first hand reference I have looked at the process sequence of CHECKDB from BOL topics. Bingo! here is the entry on the updated BOL topic for CHECKDB:
In versions of SQL Server 2005 before SP2, executing DBCC CHECKDB clears the plan cache for the instance of SQL Server. Clearing the plan cache causes recompilation of all later execution plans and may cause a sudden, temporary decrease in query performance. In SP2, executing DBCC CHECKDB does not clear the plan cache.
So the moral for this event is to better check the documentation before or even after the service pack updates have been performed. Further this was missed on the testing environment as we never gave a thought to run CHECKDB over there, as it was meant for application functionality testing only.
