Teaser: SSAS Cache Warming with PowerShell

You may be thinking oh-no, not another one. Allan Mitchell recently posted an example of an SSIS package that would warm the SSAS cache which is an updated version with a slightly simpler SSIS data flow from something Chris Webb originally blogged. Being a PowerShell fan as I read Allan's post I realised that most of the tasks mapped to native PowerShell cmdlets and I already had PowerShell code to execute an MDX command, so all I was missing was someway of reading in the trace data.

What I ended up with was the following 3 line script which uses 2 native and 2 custom cmdlets to do the same thing as Allan's SSIS package.

add-PSSnapin powerSSAS
add-PSSnapin powerTrace

get-SqlTraceEvent "c:\data\cacheWarmTest.trc" `
    |Where-Object {$_.EventSubclass -eq 0 `
-and $_.EventClass -eq "Query End" `
-and $_.TextData.Trim().Length -gt 0 } ` |Sort-Object TextData -unique ` | % {Invoke-AsMdx localhost $_.TextData > null}

The first two lines are just plumbing code that load the snapins (dlls) that contain the get-SqlTrace and Invoke-AsMdx cmdlets. The following is an outline of how the script works:

  • The get-SqlTraceEvent cmdlet will read a .trc file and return a collection of custom objects that has a property for each of the columns that were capture in the trace.
  • The call to Where-Object does the same thing as the conditional split in the SSIS package and filters out any events that are not MDX queries. (you can also setup your trace to only capture MDX commands)
  • The Sort-Object call is the same as the sort task in the SSIS and extracts only unique queries (based on an exact string match).
  • Finally the invoke-AsMdx cmdlet executes the queries. I am redirecting the output to null as I am not really interested in actually doing anything with the results.

So, if this has all been done before why would I bother doing it again? - Because PowerShell scripts are so easy to work with. What happens if your trace file contains a lot of small, fast queries that you do not want to bother running? With the script you can easily edit it to add another condition to only process queries that took more than 100 milliseconds.

get-SqlTraceEvent "c:\data\cacheWarmTest.trc" `
   
|Where-Object {$_.EventSubclass -eq 0
`
              -and $_.EventClass -eq "Query End"
`
              -and $_.TextData.Trim().Length -gt 0
} `
              -and $_.Duration -gt 100 } `
    |Sort-Object TextData -unique `
  
 | % {Invoke-AsMdx localhost $_.TextData > null}

And that is just the tip of the proverbial iceberg, you could also do things like loop over a series of .trc files or you could possibly get more sophisticated in your analysis and possibly look for queries that actually had been hitting the cache.

You may be wondering why I have called this post a teaser, well that's because the bits for the key parts of it are only on my laptop at the moment. I currently have the tracing in a separate snapin because it has a dependency on the SMO library and I did not want to add that dependency to powerSSAS. Then there is the fact that this should work with .trc files from the relational engine too, so it does not seem to fit neatly with powerSSAS.

I also have a snapin for PerformancePoint Monitoring and some SSIS cmdlets, so another thought I had was to bundle all these projects and release some sort of BI PowerShell pack. I'm not sure which direction to take, I would be interested to hear if anyone has any opinions on this.

 

Technorati Tags: ,

Print | posted on Sunday, November 16, 2008 11:41 PM