MDX , AS 2005. Despite clearing cache, query runs faster 2nd time round - is there a secondary cache?

Question

Hi

I am trying to test the performance of some MDX queries.  The very first time i run a given query (from management studio) it executes in 25 seconds.  I then clear the cache:

<ClearCache xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Object>
<DatabaseID>Adventure Works DW</DatabaseID>
</Object>
</ClearCache>

and run it again.  This time it executes in around 8 seconds.  If i now run it again without clearing the cache it takes 1 second. Clearing again and it is back to 8 seconds. 

I can repeat this process if i write a new query, the first time it will take around 25 secs again. 

Is there a secondary cache of some sort that perhaps stores the compiled query or something similar? how can i be certain the cache is cleared???

Thanks

Guy

---

Guy

Answer 1

Hi,

with "running the first time" do you mean after a restart of AS?
this could be the reason why the first time it takes longer then after clearing the cache

do you have an expensive MDX-Script maybe?

the only "cache" i could think of that persist a ClearCache is the filesystem cache but i doubt that you will be able to clear it

you could also check in profiler what the difference between your scenarios is

hth,
gerhard

---

- www.pmOne.com -

Answer 2

     Part or all of this may be explained by the fact that you're hard drive or SAN also caches.  I don't believe those are cleared when the ClearCache query is run so subsequent queries could be taking advantage of that.

    
    

Answer 3

Can you share your queries since you are using the Adventure Works cube?

BR
Thomas Ivarsson

Answer 4

Hi,

I've observed the same behaviour and in order to avoid this situation, I updated some records in FACT,  Processing the cube (Full Process ) and addressing this issue. Though this workaround may not work all the time (especially when some one's partition process takes hours) but this works when you have fewer amount of data and I am able to overcome the cache issue when I am recording the MDX execution time.

thanks,
Ashok

Answer 5

You might try clearing the file system cache using this sproc in between tests. Please report back what you find:
http://asstoredprocedures.codeplex.com/wikipage?title=FileSystemCache&referringTitle=Home

Also, it's interesting to know that ClearCache like you did above doesn't actually clear everything out of the cache (just most everything important). One example is that indexes which have been hit by prior queries don't get cleared from cache until the cube is processed again. So you might run ProcessIndexes on the cube in between tests and see if that helps make the performance more consistent.

Also, if you've messed up your memory management settings on the server, SSAS could have been paged out. Common scenario here is that SQL is running on the same box as SSAS, and you haven't put a cap on the memory of SSAS. I would recommend setting the memory limit on each service on the box so they don't overlap or cause each other to page out.

---

http://artisconsulting.com/Blog/GregGalloway

Answer 6

Sure. We will use your suggestions and post our findings. Thanks for your suggestions.

Ashok