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?
AXISHKAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.