DF051: The DBCC FREEPROCCACHE/DROPCLEANBUFFERS command is used.

Last modified: December 25, 2024

The topic describes the DF051 T-SQL code analysis rule.

Category

PERFORMANCE

Message

The DBCC FREEPROCCACHE/DROPCLEANBUFFERS command is used.

Description

It is not recommended to use DBCC FREEPROCCACHE/DROPCLEANBUFFERS because of the possibility of performance degradation.

Additional information

These commands are used for testing and troubleshooting issues. However, if you use them too much, they can lead to decreased performance by removing cached query plans and data pages from memory, forcing SQL Server to recompile queries and reload data pages, which can increase CPU and I/O overhead.

Be cautious when using DBCC FREEPROCCACHE to clear the plan cache. Doing so removes all plans stored in the cache, causing new query executions to compile fresh plans instead of reusing cached ones. This may temporarily reduce query performance, as more new compilations are required. In addition, each cleared cachestore in the plan cache will be logged.

Noncompliant code example

ALTER DATABASE demodb MODIFY FILEGROUP [filegroup1] READONLY;
GO
ALTER DATABASE demodb MODIFY FILEGROUP [filegroup1] READWRITE;
GO
ALTER DATABASE demodb SET TORN_PAGE_DETECTION ON;
GO

Compliant solution

ALTER DATABASE demodb MODIFY FILEGROUP [filegroup1] READ_ONLY;
GO
ALTER DATABASE demodb MODIFY FILEGROUP [filegroup1] READ_WRITE;
GO
ALTER DATABASE demodb SET PAGE_VERIFY CHECKSUM;
GO