[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 175
  • Last Modified:

Clean up memory for SQL Server

CHECKPOINT;
DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;

Is there any impact if I issues the above three command on my SQL server where there are several running query running on the SQL server?
0
AXISHK
Asked:
AXISHK
  • 3
  • 3
  • 2
2 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
no impact on the running processes.
0
 
Scott PletcherSenior DBACommented:
It almost certainly will.  For example:

Clearing the procedure cache will force queries to be recompiled.
Clearing buffers forces existing queries to re-read pre-cached/"read-ahead" data.

These are not commands that should generally ever be used in a production environment except in extreme circumstances.

CHECKPOINT is OK, as long as it's not overdone.  SQL itself takes frequent CHECKPOINTs of all dbs (unless you've explicitly changed that setting).
0
 
AXISHKAuthor Commented:
Seem like the slow performance issue can be solved when we issues the command. Providing we have sufficient memory, what does it indicate ?

Tks
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
if you have a slow performance, and running those commands helps, then you have not enough memory on your box...
0
 
AXISHKAuthor Commented:
Tks
0
 
Scott PletcherSenior DBACommented:
I'd be curious to know why you feel that those commands would have "no impact on the running processes".  Am I overlooking something here??
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
no impact in the send of the command "working"
sure it will impact on the performance, but not on the fact if they actually run;
and running the commands will, if any, have only a "short impact" on the commands in a production environment.
0
 
Scott PletcherSenior DBACommented:
>> and running the commands will, if any, have only a "short impact" on the commands in a production environment. <<

Probably, if the environment is not already constrained and/or it's a moderate size.  If already constrained, though, this could cause quite a delay as the buffers are reloaded.  Similarly, if you had 96GB of buffers allocated, it could take quite a while to refill those buffers!
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now