DROPCLEANBUFFERS doesn't seem to clear cache

I have a query that I'm trying to benchmark. The first time I run it,
it takes 40 seconds to run. The next time I run it, it returns in 0
seconds.
I execute the following:
CHECKPOINT
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
Then I run the query again and it takes 40 seconds, as expected.
Re-running the query causes it to go back down to 0 seconds, as
expected. However, from this point forward, even if I run the commands
above, the query stays at 0 seconds! I can't seem to clear the cache
anymore.
Is something being cached in spite of dropping the data and procedure
caches?Hello,
It should work. Can you set the Statistics I/O and Statistics time and see
if there is any change in values after and before clearing the cache.
Thanks
Hari
SQL Server MVP
"element533@.gmail.com" wrote:
> I have a query that I'm trying to benchmark. The first time I run it,
> it takes 40 seconds to run. The next time I run it, it returns in 0
> seconds.
> I execute the following:
> CHECKPOINT
> DBCC DROPCLEANBUFFERS
> DBCC FREEPROCCACHE
> Then I run the query again and it takes 40 seconds, as expected.
> Re-running the query causes it to go back down to 0 seconds, as
> expected. However, from this point forward, even if I run the commands
> above, the query stays at 0 seconds! I can't seem to clear the cache
> anymore.
> Is something being cached in spite of dropping the data and procedure
> caches?
>|||You might compare the query plans to make sure they are the same. It might
be that a large scan takes 40 seconds without cached data but runs
sub-second once data are cached. A plan change (e.g. index seek) could
cause the query to run sub-second with or without cached data.
Hope this helps.
Dan Guzman
SQL Server MVP
<element533@.gmail.com> wrote in message
news:1158086894.924338.242370@.i42g2000cwa.googlegroups.com...
>I have a query that I'm trying to benchmark. The first time I run it,
> it takes 40 seconds to run. The next time I run it, it returns in 0
> seconds.
> I execute the following:
> CHECKPOINT
> DBCC DROPCLEANBUFFERS
> DBCC FREEPROCCACHE
> Then I run the query again and it takes 40 seconds, as expected.
> Re-running the query causes it to go back down to 0 seconds, as
> expected. However, from this point forward, even if I run the commands
> above, the query stays at 0 seconds! I can't seem to clear the cache
> anymore.
> Is something being cached in spite of dropping the data and procedure
> caches?
>|||But why would the same SELECT statement result in two different query
plans on two different runs?
Dan Guzman wrote:
> You might compare the query plans to make sure they are the same. It might
> be that a large scan takes 40 seconds without cached data but runs
> sub-second once data are cached. A plan change (e.g. index seek) could
> cause the query to run sub-second with or without cached data.
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> <element533@.gmail.com> wrote in message
> news:1158086894.924338.242370@.i42g2000cwa.googlegroups.com...
> >I have a query that I'm trying to benchmark. The first time I run it,
> > it takes 40 seconds to run. The next time I run it, it returns in 0
> > seconds.
> >
> > I execute the following:
> >
> > CHECKPOINT
> > DBCC DROPCLEANBUFFERS
> > DBCC FREEPROCCACHE
> >
> > Then I run the query again and it takes 40 seconds, as expected.
> > Re-running the query causes it to go back down to 0 seconds, as
> > expected. However, from this point forward, even if I run the commands
> > above, the query stays at 0 seconds! I can't seem to clear the cache
> > anymore.
> >
> > Is something being cached in spite of dropping the data and procedure
> > caches?
> >|||> But why would the same SELECT statement result in two different query
> plans on two different runs?
You might get different plans due to differences in statistics.
--
Hope this helps.
Dan Guzman
SQL Server MVP
<element533@.gmail.com> wrote in message
news:1158182579.563636.167870@.i42g2000cwa.googlegroups.com...
> But why would the same SELECT statement result in two different query
> plans on two different runs?
> Dan Guzman wrote:
>> You might compare the query plans to make sure they are the same. It
>> might
>> be that a large scan takes 40 seconds without cached data but runs
>> sub-second once data are cached. A plan change (e.g. index seek) could
>> cause the query to run sub-second with or without cached data.
>>
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> <element533@.gmail.com> wrote in message
>> news:1158086894.924338.242370@.i42g2000cwa.googlegroups.com...
>> >I have a query that I'm trying to benchmark. The first time I run it,
>> > it takes 40 seconds to run. The next time I run it, it returns in 0
>> > seconds.
>> >
>> > I execute the following:
>> >
>> > CHECKPOINT
>> > DBCC DROPCLEANBUFFERS
>> > DBCC FREEPROCCACHE
>> >
>> > Then I run the query again and it takes 40 seconds, as expected.
>> > Re-running the query causes it to go back down to 0 seconds, as
>> > expected. However, from this point forward, even if I run the commands
>> > above, the query stays at 0 seconds! I can't seem to clear the cache
>> > anymore.
>> >
>> > Is something being cached in spite of dropping the data and procedure
>> > caches?
>> >
>|||On 12 Sep 2006 11:48:15 -0700, element533@.gmail.com wrote:
>I have a query that I'm trying to benchmark. The first time I run it,
>it takes 40 seconds to run. The next time I run it, it returns in 0
>seconds.
>I execute the following:
>CHECKPOINT
>DBCC DROPCLEANBUFFERS
>DBCC FREEPROCCACHE
>Then I run the query again and it takes 40 seconds, as expected.
>Re-running the query causes it to go back down to 0 seconds, as
>expected. However, from this point forward, even if I run the commands
>above, the query stays at 0 seconds! I can't seem to clear the cache
>anymore.
Pretty weird.
You haven't left any transactions open, have you?
You might turn on a few perfmon stats to see if you can get more info.
J.