Clearing the Cache

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:

DBCC DROPCLEANBUFFERS;

To clear execution plans from the cache globally, use the following command:

DBCC FREEPROCCACHE;

To clear execution plans of a particular database, use the following command:

DBCC FLUSHPROCINDB(<db_id>);

Note : DBCC FLUSHPROCINDB command is undocumented.

To clear execution plans of a particular cache store, use the following command:

DBCC FREESYSTEMCACHE(<cachestore>);

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.

Advertisements

About Ashish Jain

I am Ashish Jain, a software engineer by profession. My goal of creating this blog is to share my knowledge of SQL server with all other SQL enthusiasts and also to learn from them.
This entry was posted in T-SQL and tagged , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s