Clearing SQL Server Analysis Services Cache

SSAS Query Processor optimises the evaluation of calculations through storing calculation results in query processor cache, which allows expensive calculation reused within a query, a user session, or global context scope. However, we often need to clear the cache when using SQL Server Profiler to analyse slow running MDX queries. As the figure shown below, we could not get actually MDX Query execution trace as SSAS Query Processor is fetching the result from cache.

Clearing-SSAS-Cache-01

To clear the cache, we could go to SSMS and execute the following Analysis Services XMLA query.
Clearing-SSAS-Cache-03
By the way, another very useful tool for edit and debug MDX query is MDX Studio http://www.sqlbi.com/tools/mdx-studio, which provides a handy query execution statistics panel with some very useful infos.

Clearing-SSAS-Cache-02

 

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s