In one my recent MDX classes, a student asked about automation of MDX queries for the purposes of testing query performance, so today I’ll answer that question and draw your attention to a few resources available.
Get the ASCMD Utility
If you’re running SQL Server 2008 Analysis Services or SQL Server 2008 R2 Analysis Services, you can download the ASCMD_StressTestingScripts from the Analysis Services Samples page on Codeplex to get the main prize, the compiled ASCMD executable. (This version should also work with SQL Server 2005 Analysis Services, but I haven’t tested it.)
This utility allows you to execute MDX or DMX queries or XMLA commands from the command line. This capability means that you can build out some complex batch files to automate your MDX query testing, along with other repetitive tasks such as database processing. You can view the 2005 version of the ReadMe file to see the syntax as well as various ways that you might you use this utility (if you scroll all the way to the bottom of the page).
The old ReadMe file doesn’t include all of the arguments that are in the latest version of ASCMD. You can download a more current ReadMe document that’s buried deep in the bowels of Codeplex to get more information.
Try a Simple Command
In theory, the stress testing scripts let you execute ASCMD as a single client with single query or multiple queries, or as multiple clients with single query, or as multiple clients with multiple queries. I wasn’t able to get that piece working as a file seems to be missing from the download, but you can create your own batch files to do similar things. You can get the basic idea by reviewing the RunASCMDParallelStressTest.cmd and RunASCMDSerialStressTest.cmd files that come in the download.
If you don’t want to wade through all that batch file stuff to figure out how to make ASCMD work, let me cut to the chase. You can execute asmcd /? to see the parameters available. For running an MDX query, assuming that you want to capture trace information to see the query duration and key trace events like QuerySubcubeVerbose and GetDataFromCache, you use the following syntax:
ascmd –S <server\instance> -d <database name> -I <mdx file> -o Output.xml -T <CSV file>
So, for example, to run on my local instance, using a query file found in the Queries subfolder of the download, I would run the following command:
ascmd -S localhost -d "Adventure Works DW 2008R2" -i Queries\Query1-3.mdx -o Output.xml -T Trace.csv
The Output.xml will contain the query results and a lot of metadata about the cube that you can probably ignore if your goal is to get performance testing data. The Trace file is a pipe-delimited CSV file that contains the same type of trace data that you get when you run a SQL Server Profiler trace for Analysis Services. The trace file gets overwritten on each execution, except as noted below.
You can adjust the level of detail in your trace file by adding the –Tl argument with one of the following values
- High (default) – captures everything.
- Medium – captures everything except ProgressReportCurrent and Notification events.
- Low – captures only events with “End” or “Error”.
- Duration – captures only execution duration and writes one line in the trace file with current time, duration, execution text, database, and server name. If you use this argument, and execute ASCMD multiple times, each execution appends new data to the file.
- Duration-result – captures the same results as when you use “duration” but includes an addition column to store the result of the execution. Each execution appends new data to the file.
Batch It
The ASCMD utility can run one file at a time, but you can put multiple queries into the file using a GO command between the queries. Or you can set up a batch process with a loop to call ASCMD multiple times.
You can leave out the –o argument but you’ll get the output stream on your screen, unless of course you put this into a batch file and use echo off and echo on after executing the ASCMD.
For performance testing, you should also include some additional steps before you run the MDX query to get true baseline performance measurements. Put these steps into an MDX or XMLA script file and use ASCMD to execute them in your batch file before running the MDX query that you’re testing:
- Clear the Analysis Services cache
<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"> <ClearCache> <Object> <DatabaseID>Adventure Works DW 2008R2</DatabaseID> </Object> </ClearCache> </Batch>
- Clear the file system cache.
- Execute the MDX script independently.
SELECT {} ON 0 FROM [Adventure Works]
Learn More About Query Performance Analysis
Now that you’ve captured trace event information, what do you do with it? Here are some resources to help you determine what it means:
- Identifying and Resolving MDX Query Performance Bottlenecks
- Analysis Services 2008 Performance Guide
Updates
I mirror my blog and received a few comments at SQLBlog.com about other tools for automating MDX query testing that I want to share here:
- Boyan Penev (twitter | blog) reminded me that you can also use the ClearAllCaches() method to clear both SSAS cache and the file system cache.
- Davide Mauri (twitter | blog) recommends BI.Quality for verifying that MDX results match expectations (and more!).
- Jason Thomas (twitter | blog) uses AS Performance Workbench for load testing MDX queries.