Sometimes you need to clear the cache, for example when you want to analyze query performance. SQL Server provide some tools to clear both data and execution plans from the cache. Here I am explaining those tools.
To clear data from the cache globally, use the following command:
To clear execution plans from the cache globally, use the following command:
To clear execution plans of a particular database, use the following command:
Note : DBCC FLUSHPROCINDB command is undocumented.
To clear execution plans of a particular cache store, use the following command:
You can specify the following values as input of FREESYSTEMCACHE function : ‘ALL’, pool_name, ‘Object Plans’, ‘SQL Plans’, ‘Bound Trees’. Note that the last three options are undocumented.
- The ‘ALL’ option indicates that you want to clear all supported caches.
- The pool_name value indicates the name of a Resource Governor pool cache that you want to clear.
- Specify ‘Object Plans’ to clear object plans (plans for stored procedures, triggers, and user-defined functions).
- Specify ‘SQL Plans’ to clear plans for ad-hoc statements, including prepared statements. Specify ‘Bound Trees’ to clear plans for views, constraints, and defaults.
NOTE : Consider carefully before using these commands in production environments. Obviously, clearing the cache has a performance impact on the system. After clearing the data cache, SQL Server needs to physically read pages accessed for the first time from disk. After clearing execution plans from the cache, SQL Server needs to generate new execution plans for queries.