Reset cached plan - how to and what to?
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 you can take help of Performance Dashboard ticker to get the reports. Same way you can obtain such information using Dynamic Management Views (DMV - DBA's friend). For your information the lifetime of DMV information or data is purely depends upon the plan in cache, as it depends upon the availabel memory on the server. Also the information about resouces consumed on cache you can take help of another DMV sys.dm_exec_query_plan here. Similarly you can also force a query plan when you are creating a plan guide by using the sp_create_plan_guide system stored procedure. Plan guides are used to apply query hints to queries in deployed applications when you cannot or do not want to change the application directly. In this case take your time to read Optimizing Queries in Deployed Applications by Using Plan Guides & Query Parameterization behaviour articles too.
So for the benchmarking purpose you might need to clear the cache and for optimum results of using DMV also this is required, bear in mind do not attempt this on a production server that is already stressed out on a resourcess such as memory or disk issues. Further reading Batch Compilation, Recompilation, and Plan Caching Issues article & similar subject blog would help to understand how best you can get such statistical information. You might know that using DBCC FREEPROCCACHE or DBCC FREESYSTEMCACHE (new in SQL 2005) will clear the plan cache on that particular SQL Server instance. Bear in mind clearing the plan cache with this process causes a recompilation of all subsequent execution plans and cause a sudden, temporary excessive CPU usage and decrease in query performance.
DBCC FREESYSTEMCACHE is new in SQL Server 2005 that will clear the unused cache entries from all the caches. You can use 'MARK_IN_USE_FOR_REMOVAL' clause in this DBCC statement to free up the currently used plans from the cache as and when they become unused. This is useful in order to remove them manually even though SQL 2005 Database Engine proactively cleans up unused cache entries in the background to make memory available for current entries. Have a reference through SQL Development team's blog on Useful Queries on DMV’s to understand Plan Cache Behavior entry.
**__________________________________**
SQL Server MVP, Sr. DBA & industry expert.
-
Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it. It is also a power and you will gain by sharing it.