askvity

How to Clear SQL Cache? (SQL Server)

Published in SQL Server Cache Management 3 mins read

To clear the SQL cache in SQL Server, you primarily use Database Console Commands (DBCC). The key commands are DBCC FREEPROCCACHE, DBCC FREESYSTEMCACHE, and DBCC ALTER DATABASE SCOPED CONFIGURATION.

Key Commands for Clearing Cache in SQL Server

Clearing cache in SQL Server can be necessary for various reasons, such as troubleshooting performance issues, testing query execution plans, or freeing up memory. Here are the main commands derived from the reference and commonly used:

  • DBCC FREEPROCCACHE: This command removes elements from the procedure cache. The procedure cache stores compiled query plans, which SQL Server uses to efficiently execute queries. Clearing it forces SQL Server to recompile plans for subsequent executions.
  • DBCC FREESYSTEMCACHE ('ALL'): This command clears various system-level caches, including object caches, security caches, and resource caches. Specifying 'ALL' clears all distinct cache types.
  • DBCC ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE: This command is available from SQL Server 2016 onwards. It clears the procedure cache for the current database only, rather than the entire instance like DBCC FREEPROCCACHE.

Here's a look at these commands:

-- Clear the entire procedure cache for the instance
DBCC FREEPROCCACHE;

-- Clear all system caches for the instance
DBCC FREESYSTEMCACHE ('ALL');

-- Clear the procedure cache for the current database (requires being connected to the specific database)
-- Available from SQL Server 2016+
-- USE YourDatabaseName; -- Replace YourDatabaseName
-- DBCC ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;

Required Permissions

As stated in the reference, to run DBCC FREEPROCCACHE and DBCC FREESYSTEMCACHE, you must have the following permissions:

  • VIEW SERVER STATE
  • ALTER SERVER STATE

These permissions are typically granted to administrators or highly privileged users. Using DBCC ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE requires ALTER ANY DATABASE SCOPED CONFIGURATION permission.

When Might You Clear Cache?

Clearing the SQL Server cache isn't a routine maintenance task and should be done cautiously, especially in production environments, as it can temporarily impact performance while query plans are recompiled. Common scenarios include:

  • Troubleshooting unexpected query performance issues.
  • Forcing recompilation of query plans after significant schema changes or statistics updates.
  • Benchmarking and performance testing to ensure fair comparisons without cached plans.
  • Freeing memory held by caches, although SQL Server typically manages memory efficiently.

In summary, use the appropriate DBCC commands like DBCC FREEPROCCACHE and DBCC FREESYSTEMCACHE to clear different types of caches in SQL Server, ensuring you have the necessary VIEWER SERVER STATE and ALTER SERVER STATE permissions. For database-specific procedure cache clearing (SQL Server 2016+), use DBCC ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE.

Related Articles