Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Different Ways to Flush or Clear SQL Server Cache


By:   |   Read Comments (2)   |   Related Tips: More > Performance Tuning

Attend a SQL Server Conference for FREE >> click to learn more


Problem

Sometimes there are issues due to what SQL Server has stored in its cache. Here are some possible reasons which may create caching performance issues.

  • Ad-hoc Query workload issues due to cache bloat
  • Excessive use of dynamic T-SQL code
  • Server has insufficient memory or not properly assigned to SQL instances
  • Memory pressure generated due to heavy long running transactions
  • Server has frequent recompilation events

When issues such as these are found you may need to flush the plan cache or buffer cache. So in this tip we look at different ways to flush the SQL Server cache.

Solution

I will explain different commands that you can use to manage what is in the cache.

DBCC FREEPROCCACHE

This command allows you to clear the plan cache, a specific plan or a SQL Server resource pool.

Syntax

DBCC FREEPROCCACHE [ ( { plan_handle | sql_handle | pool_name } ) ] [ WITH NO_INFOMSGS ] 
  • plan handle uniquely identifies a query plan for a batch that has executed and whose plan resides in the plan cache.
  • sql_handle is the SQL handle of the batch to be cleared. sql_handle is varbinary(64).
  • pool_name is the name of a Resource Governor resource pool.

Examples

Flush the entire plan cache for a SQL Server instance.

DBCC FREEPROCCACHE

Flush the plan cached for an entire instance, but suppress the output messages.

DBCC FREEPROCCACHE WITH NO_INFOMSGS;

To flush a specific resource pool, we can use this command to see how much memory is being used for each resource pool.

SELECT name AS 'Pool Name', 
cache_memory_kb/1024.0 AS [cache_memory_MB], 
used_memory_kb/1024.0 AS [used_memory_MB] 
FROM sys.dm_resource_governor_resource_pools;

Then with the output above, we can specify the specific resource pool to flush as follows.

DBCC FREEPROCCACHE ('LimitedIOPool');

We can also flush a single query plan. To do this we need to first get the plan_handle from the plan cache as follows:

SELECT cp.plan_handle 
FROM sys.dm_exec_cached_plans AS cp 
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st 
WHERE OBJECT_NAME (st.objectid) LIKE '%TestProcedure%'

Then we can use the plan_handle as follows to flush that one query plan.

DBCC FREEPROCCACHE (0x040011004A2CC30E204881F30200000001000000000000000000000000000000000000000000000000000000);'

DBCC FLUSHPROCINDB

This allows you to clear the plan cache for a specific database.

Syntax

DBCC FLUSHPROCINDB(DatabaseID)

Example

Flush the database plan cache for database MyDB.

Use MyDB; 
Declare @dbid int = db_ID() 
DBCC FLUSHPROCINDB (@dbId)

DBCC FREESYSTEMCACHE

Releases all unused cache entries from all caches. You can use this command to manually remove unused entries from all caches or from a specific Resource Governor pool.

Syntax

DBCC FREESYSTEMCACHE( 'ALL' [, pool_name ] ) [WITH { [ MARK_IN_USE_FOR_REMOVAL ] , [ NO_INFOMSGS ]]

Examples

The following example uses the MARK_IN_USE_FOR_REMOVAL clause to release entries from all current caches once the entries become unused.

DBCC FREESYSTEMCACHE ('ALL') WITH MARK_IN_USE_FOR_REMOVAL

Flush the ad hoc and prepared plan cache for the entire server instance.

DBCC FREESYSTEMCACHE ('SQL Plans')

Clear all table variables and temp tables cached.

DBCC FREESYSTEMCACHE ('Temporary Tables & Table Variables')

Clear for a specific user database.

DBCC FREESYSTEMCACHE ('userdatabase')

Remove the tempdb cache.

DBCC FREESYSTEMCACHE ('tempdb')

DBCC FREESESSIONCACHE

Flushes the distributed query connection cache used by distributed queries against an instance of SQL Server.

Syntax

DBCC FREESESSIONCACHE [ WITH NO_INFOMSGS ]

Example

DBCC FREESESSIONCACHE

DBCC FLUSHAUTHCACHE

DBCC FLUSHAUTHCACHE flushes the database authentication cache maintained information regarding login and firewall rules for the current user database.  This command cannot run in the master database, because the master database maintains the physical storage information regarding login and firewall rules.

Syntax

DBCC FLUSHAUTHCACHE [ ; ]

Using sp_recompile

For specific objects that are cached, we can pass a procedure name, trigger, table, view, function in the current database and it will be recompiled next time it is run.

Syntax

EXEC sp_recompile N'Object';

Example

EXEC sp_recompile N'myprocedure';
EXEC sp_recompile N'myprocedure';
EXEC sp_recompile N'mytable';

Using ALTER DATABASE

You can also clear the plan cache for the current database using ALTER DATABASE as shown below. This is new in SQL Server 2016.

Syntax

ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE

DBCC DROPCLEANBUFFERS

Use DBCC DROPCLEANBUFFERS to test queries with a cold buffer cache without shutting down and restarting the server.

Syntax

DBCC DROPCLEANBUFFERS [ WITH NO_INFOMSGS ] 
DBCC DROPCLEANBUFFERS ( COMPUTE | ALL ) [ WITH NO_INFOMSGS ]
  • WITH NO_INFOMSGS - Suppresses all informational messages. Informational messages are always suppressed on SQL Data Warehouse and Parallel Data Warehouse.
  • COMPUTE - Purge the query plan cache from each Compute node.
  • ALL - Purge the query plan cache from each Compute node and from the Control node. This is the default if you do not specify a value.
Next Steps
  • Check out these other performance related tips Performance Tips.
  • Reference Link used for more clarification about these command.
  • Please be careful while flushing the cache. This can impact overall performance and it is best to first test with a development or QA environment before running in production.


Last Update:


signup button

next tip button



About the author
MSSQLTips author Bhavesh Patel Bhavesh Patel has been working with SQL Server since 2007 as a Senior DBA.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Monday, April 03, 2017 - 1:23:41 AM - Nirav Back To Top

Good Job Bhavesh, One more point I have to add on this. Please do not on production environment.

 


Sunday, April 02, 2017 - 7:48:05 AM - K.B Patel Back To Top

Awesome Tips This is really helpful in practical sense.

 

.


Learn more about SQL Server tools