DROPCLEANBUFFERS/FREEPROCCACHE

I've been doing some stored proc tuning and I want to ensure that after the
changes I make to the query the execution times are accurate. So basically,
if I run DROPCLEANBUFFERS and FREEPROCCACHE in an environment with other
users, am I clearing ALL buffers/proccache that SQL is using, or just the
ones tied to my connection?Everything for the server.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"mrdj" <mrdj@.discussions.microsoft.com> wrote in message
news:B94A13C3-2300-4EF7-A52A-80A60BBC7438@.microsoft.com...
> I've been doing some stored proc tuning and I want to ensure that after
the
> changes I make to the query the execution times are accurate. So
basically,
> if I run DROPCLEANBUFFERS and FREEPROCCACHE in an environment with other
> users, am I clearing ALL buffers/proccache that SQL is using, or just the
> ones tied to my connection?|||There is no need to issue FREEPROCCACHE as each time you edit the sp it will
invalidate the plan already in cache anyway. DropCleanBuffers is data
related and has nothing to do with the sp execution plan chosen.
--
Andrew J. Kelly SQL MVP
"mrdj" <mrdj@.discussions.microsoft.com> wrote in message
news:B94A13C3-2300-4EF7-A52A-80A60BBC7438@.microsoft.com...
> I've been doing some stored proc tuning and I want to ensure that after
> the
> changes I make to the query the execution times are accurate. So
> basically,
> if I run DROPCLEANBUFFERS and FREEPROCCACHE in an environment with other
> users, am I clearing ALL buffers/proccache that SQL is using, or just the
> ones tied to my connection?|||well, i thought so about DROPCLEANBUFFERS but the proc ran quicker (without
change) every time I ran it after the first time. So SQL must have been
acessing the data from the buffer? Thanks for your quick response.
"Andrew J. Kelly" wrote:
> There is no need to issue FREEPROCCACHE as each time you edit the sp it will
> invalidate the plan already in cache anyway. DropCleanBuffers is data
> related and has nothing to do with the sp execution plan chosen.
> --
> Andrew J. Kelly SQL MVP
>
> "mrdj" <mrdj@.discussions.microsoft.com> wrote in message
> news:B94A13C3-2300-4EF7-A52A-80A60BBC7438@.microsoft.com...
> > I've been doing some stored proc tuning and I want to ensure that after
> > the
> > changes I make to the query the execution times are accurate. So
> > basically,
> > if I run DROPCLEANBUFFERS and FREEPROCCACHE in an environment with other
> > users, am I clearing ALL buffers/proccache that SQL is using, or just the
> > ones tied to my connection?
>
>|||"mrdj" <mrdj@.discussions.microsoft.com> wrote in message
news:43E5BEE0-D6D4-4D78-A6E0-6F128AA7B961@.microsoft.com...
> well, i thought so about DROPCLEANBUFFERS but the proc ran quicker
(without
> change) every time I ran it after the first time. So SQL must have been
> acessing the data from the buffer? Thanks for your quick response.
Andrew may not agree with the method, but I personally always do a
CHECKPOINT then run DROPCLEANBUFFERS and FREEPROCCACHE when I'm testing
stored procedure changes for single-run performance -- I want to work with a
totally clean slate.
Of course, a single-run doesn't tell you much; you might instead want to
try running it 10 or 20 times and taking the average. I've found this gives
much more interesting results. Sometimes a single run can be skewed by
something else on the server running (or not running, thereby making you
think you've done better than you really have). If you run it several times
and take an average, that's less likely to affect your results.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--|||It is not that I don't agree with doing that but it is important to
understand what happens when you do that and how it may or may not affect
your results. But it's rare any more that I do a single test like that
since it rarely mimics real life conditions on busy systems. I can usually
tell more from the query plan than anything else. But That does not mean
this can not be useful information.
Andrew J. Kelly SQL MVP
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:uC9JouXMFHA.2252@.TK2MSFTNGP15.phx.gbl...
> "mrdj" <mrdj@.discussions.microsoft.com> wrote in message
> news:43E5BEE0-D6D4-4D78-A6E0-6F128AA7B961@.microsoft.com...
>> well, i thought so about DROPCLEANBUFFERS but the proc ran quicker
> (without
>> change) every time I ran it after the first time. So SQL must have been
>> acessing the data from the buffer? Thanks for your quick response.
> Andrew may not agree with the method, but I personally always do a
> CHECKPOINT then run DROPCLEANBUFFERS and FREEPROCCACHE when I'm testing
> stored procedure changes for single-run performance -- I want to work with
> a
> totally clean slate.
> Of course, a single-run doesn't tell you much; you might instead want
> to
> try running it 10 or 20 times and taking the average. I've found this
> gives
> much more interesting results. Sometimes a single run can be skewed by
> something else on the server running (or not running, thereby making you
> think you've done better than you really have). If you run it several
> times
> and take an average, that's less likely to affect your results.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>sql