x

Sql server memory

DBCC FREEPROCCACHE,DBCC DROPCLEANBUFFERS

is this statements increase the performance of stored procedure in sql server.
more ▼

asked Sep 26, 2012 at 05:06 AM in Default

Parthasarathy gravatar image

Parthasarathy
60 4 6 8

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

It depends. If you have inefficent plans cached then forcing new plans to be created may help the stored procedure the next time it runs, but then that plan might not always be the most effient as well. It is a vicous cycle. I have had situations where freeing the proc cache was the answer, but those are more the exception than the rule. Is your instance setup for optimize for adhoc workload? Is it a reporting server, data warehouse or OLTP?

I am sure others will weigh in on this. It is late and I am working on a production issue so I can't give this the attention it deserves right now.
more ▼

answered Sep 26, 2012 at 05:13 AM

Tim gravatar image

Tim
36.4k 39 41 139

(comments are locked)
10|1200 characters needed characters left

Those two statements are a method of cleaning out the memory on your system. In general, this doesn't speed things up. Instead it slows things down since any pages that have been read off of disk and are now residing in memory have to be re-read from disk. It also slows things down since all execution plans in cache have to be recompiled. Neither of these things is considered to be a way to speed things up.

But, if you have plans that are based off of parameter values that are from skewed data, those plans might be off and recompiling them can speed things up. But, dropping all plans for the entire system isn't the way to solve that issue. Instead you should identify those plans that are problematic and then use DBCC FREEPROCCACHE() to drop the individual problematic plans. You don't want to drop everything.

I do use these statements when I'm testing memory configurations or new queries and I'm curious about the disk reads as well as the compile time and compiled plans. But I never run them on a production system as a way to speed things up or fix things. I would consider it a very poor practice.
more ▼

answered Sep 26, 2012 at 09:09 AM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
103k 19 21 74

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x348
x59
x47

asked: Sep 26, 2012 at 05:06 AM

Seen: 653 times

Last Updated: Sep 26, 2012 at 09:24 AM