SSAS: Query Performance Tuning Whitepaper

I suppose by now most of you have seen all the other posts about the Identifying and Resolving MDX Query Performance Bottlenecks in SQL Server 2005 Analysis Services whitepaper that was just released. I saw them all too and I was not planning to do another yet another "read this cool whitepaper" post. But, even thoufh I have only just started reading through it, I do have a couple of points I want to make.

1) The first is a shameless plug: BIDSHelper www.codeplex.com/bidshelper gets a mention on page 6 for the improved Aggregation Manager feature which we have included. The DeployMDXScript feature is also quite handy when you are making frequent changes to your MDX Script. Last time I checked the latest version of BIDSHelper had over 500 downloads. :)

2) There is a slight inaccuracy on p12 with the following statement (emphasis added)

We combined the preceding XMLA statement and MDX query into a single batch file (separated by a GO statement) that we execute by using the ASCMD sample command-prompt utility (you cannot combine these if you execute them by using SQL Server Management Studio).

Open up a blank MDX query window in SSMS and paste in the following code, ignore any red squiggles and execute it.

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

GO

SELECT {} ON 0
FROM [Adventure Works] 

It works doesn't it? I found this out at TechEd this year, that the MDX window will actually take certain snippets of XMLA. It appears that only certain elements are valid, if you omit the <Batch> tags for instance it will not work. But the GO statement works in SSMS. As noted previously it is SSMS splitting the two commands at the GO statement. SSAS itself only accepts one command at a time and knows nothing about the GO keyword.

For those of you with the assembly from the Analysis Services Stored Procedure project installed you can go one step further and use the following.

CALL ASSP.ClearCache("Adventure Works")
GO
SELECT {} ON 0
FROM [Adventure Works]

This actually only clears the cache for the name of the cube that is passed in as a parameter (if you don't pass the cube name in the cache is cleared for the entire database that you are currently connected to). Which might be handy if you have multiple cubes in your database and you only need to reset the cache for one of them. 

You could also do this in the XMLA version, provided you know the Cube ID (which may not be the same as its name) by adding the the CubeID tag to the ClearCache command. I like the ASSP version as it works with the Cube name which is readily discoverable, the following XMLA is essentially what the ASSP stored procedure is generating.

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

I might have a look at building these two statements into a single stored proc in ASSP so we could do the clearCache and execute the MDXScript in one call. Maybe I could call it something like ResetCache(<cube>).

[Update 20 Sep 2007] - Corrected code sample for point 2)

Print | posted on Thursday, August 30, 2007 1:06 PM

Comments on this post

No comments posted yet.

Your comment:

 (will show your gravatar)