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...
We already know that using DBCC FREEPROCCACHE will have the affect of flushing all the cache with immediate affect and also DBCC FREESYSTEMCACHE (new in SQL 2005) has similar affect but will flush the plans asynchronously that are marked as unused anymore. Also you would know that when a database is...
I don't believe you would need to reset the cache on a production server, it is not a best practice to perform in this case. Most of the times in development or test environments for the purpose of benchmarking you would need to reset the cache for optimum results. You may know that in this case...
We all know that the cached query plans are good to go to attain the performance and no doubt that inefficient query plan will cause more distress to performance with an increased spike in CPU consumption. At the same occurrence of recompilation of a stored procedure is not a good show either. So how...