SQL Centre of Excellence

Load Testing SSAS in Oslo

by Bob Duffy 30. August 2014 09:47

Thanks for everyone who made it to my SQL Saturday Session in Olso today on load testing Analysis Services.

If you are interested in getting started to building a load test harness in visual studio, I have attached the source code I used for the demos and sample database for AdventureWorks.

image

Happy Load Testing!

When you need to do load testing for Analysis Services its common to need to capture and “replay” MDX queries. I’ll go into replay tools later – the first step is to capture the queries. You will need

  • An XMLA trace definition file capturing at a minimum the QueryBegin and QueryEnd events (Events 9 and 10)
  • AN XMLA command to end the trace
  • The ASCMD.EXE utility to automate the starting/stopping of the trace. The utility is available here
    http://msftdbprodsamples.codeplex.com/downloads/get/384553

Here’s a quick video demonstrating the process of capturing a trace and importing the results into a data table.

More details on managing server side traces are also provided on this link here

http://blogs.msdn.com/b/pfebi/archive/2013/02/12/managing-server-side-analysis-services-profiler-trace.aspx

Its hard to do capacity planning for MOLAP cubes and a common question asked is if we have enough memory or why the server is showing zero bytes free memory wise.

While the msmdsrv.exe process can consume a lot of memory between the storage engine cache and formula cache, we need to be super careful to ALSO leave space for the FileSystemCache which appears as the “Standby” are within resource manager.

Its perfectly possible that SSAS has enough memory, but the FileSystemCache is starved of memory so any large queries will go to physical disk rather than to the cache which is “considerably” slower.

The screenshot below shows a Server with lots of memory after a load test of 100,000 queries. Note the FilesystemCache is > than the msmdsrv.exe memory!

image

You may find that SSAS has plenty of memory free but your server still has zero bytes free due to a massive “Standby” cache. You have two options:

a) Add more memory

b) Limit the FileSystemCache by using the LimitSystemFileCacheSizeMB setting in the ini file.

If you limit the cache be aware that IOPS from the FileSystemCache are super fast compared to disk, so you may get a performance hit.

 

Two lessons for me

1. Don't just use the commit size for the msmdsrv.exe when looking at memory usage for analysis services.

2. Maybe for cache warming we should look to warm up the FileSystemCache first. This would be considerably faster if we could leverage large block sizes and sequential IO rather than the small random IO that the SSAS storage engine does. Hmmm going to think about how to do this now…

Page List

Page List