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:[vbcol=seagreen]
> You might compare the query plans to make sure they are the same. It migh
t
> 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...|||> 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:
>|||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.

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.

Drill down matrix report slow performance

I have created OLAP cubes and we are creating reports on top of them. The reports take incredibly long to render (40 seconds to 5 minutes). The MDX for the reports runs in less than 15 seconds when executed as a query in Mgt. Studio. Browsing is also fast. The reports are drill down matrix-type reports and they do eventually display properly. Not that this should help with the mechanics of the issue but the report data source query will show how we drill across dimensions:

<code>

SELECT NON EMPTY { [Measures].[Number of Queries Run] } ON COLUMNS, NON EMPTY { ([ClientProjectDim].[Project ID].[Project ID].ALLMEMBERS * [UserDim].[UserName].[UserName].ALLMEMBERS * [QueryDim].[Status].[Status].ALLMEMBERS * [DateDim].[Date Hierarchy].[ClusteredHourOfDay].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [Ad Hoc] CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

</code>

The RS and security logs don't have anything meaningful. As the MDX works well when executed as a query but not in the reports I can't tell if it's a rendering issue somewhere. Has anyone experienced this issue and is there a resolution aside from restructuring the report?

I am experiencing similar issues but no answers, did you ever resolve youra?|||We did not resolve this, but rather restructured our reports. You might consider this. You can set up your reports with params for drill-through.|||Thanks, however I did parametize this report. I will try to restructure and do this long and laborious way. I noticed in the AS browser and MDX query designer they are using nice drill through tree for the parameters, but no drill through in the rendered form. Is that due to a property setting I am missing.