DF051: The DBCC FREEPROCCACHE/DROPCLEANBUFFERS command is used.

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

Want to Find out More?

Overview

Overview

Take a quick tour to learn all about the key benefits delivered by dbForge Studio for SQL Server.
All Features

All features

Get acquainted with the rich features and capabilities of the Studio in less than 5 minutes.
Request a demo

Request a demo

If you consider employing the Studio for your business, request a demo to see it in action.
Ready to start using dbForge Studio for SQL Server?
Download a free 30-day trial