Automating SSAS Dump Collections using SQLDumper.exe
Last year I was involved with standing up an Analysis Services Scale-Out Query Server architecture to handle a heavy concurrent user load. Unfortunately, we didn’t get around to stress-testing the system until a few months later and we uncovered an issue requiring escalation to Microsoft. In this post, I’ll share a few details about the issue and then focus on a quick and dirty method for automating dump file generation.
This client has a multi-tenant data warehouse and a custom dashboard with 24 (and eventually 60+) metrics which correspond 1-to-1 with hand crafted MDX queries. Other than the issue we escalated to Microsoft, everything is pretty typical – perhaps the only exception being the MDX queries which are absolutely without a doubt the stuff of nightmares.
Anywho, the escalated issue arises only during prolonged stress-testing. Basically, we had a custom .NET application (very similar to AS Performance Workbench which is an amazing and FREE tool) that executes the MDX queries using a variety of parameters (based on tenant configuration). Everything starts off without a hitch and runs fine up until about the 1.5-2.5 hour mark (depending on the ServerTimeout configuration – default is 3600) which is when we see our first query “time-out”. After spending several days adjusting the SSAS-server configuration (e.g. memory, threads, etc) and combing through the Analysis Services Multidimensional Operations and Performance Guide and unable to get past this issue, we decided to open a case with Microsoft and get some help from engineering.
During the escalation process, the Microsoft engineer requested a full-dump followed by a mini-dump every minute after…up until the issue was reproduced. Do you see the problem yet?
Not sure about you, but I’m definitely not interested in manually executing the command to generate a dump file on a recurring 1 minute interval for up to 2.5 hours…and I’m certain the client isn’t interested in receiving an invoice for that type of work either. So I hacked together a dirty little batch script to handle that for me, but first…
What is SQLDumper?
SQLDumper is a handy little tool that can be used to generate a memory dump file of another application. This is really only necessary when there’s an issue requiring deep analysis, so hopefully you never have to use this tool – but since you are reading this, then I guess you were unfortunate enough to have to use it.
The tool is located in the Shared directory within the Microsoft SQL Server Program Files root directory depending on the version…so for example, with SQL Server 2012, you’ll find it in the following location with a default installation:
C:\Program Files\Microsoft SQL Server\110\Shared\
By default, SSAS is configured to create a “mini” dump (using SQLDumper) whenever there’s a memory exception…but you can change that behavior or even manually force a memory dump (more info)
Keep in mind, SQLDumper can be used to generate dumps for other windows applications as well (more info)
The following is a batch file that generates a SSAS application full-dump, and then enters a continuous loop executing a SSAS mini-dump every 60 seconds.
The script takes in 2 arguments…
- Process ID (i.e. 1716) of the Analysis Services process (msmdsrv.exe)
- Output Directory
@echo off IF %1.==. GOTO MissingArg1 IF %2.==. GOTO MissingArg2 REM ---------------------------------------- REM Change to director containing SQLDumper cd "C:\Program Files\Microsoft SQL Server\110\Shared" REM Take Full-Dump (0x34) SqlDumper.exe %1 0 0x34:0x4 0 %2 REM "goto" loop construct :loop REM Wait 60 seconds TIMEOUT /T 60 REM Take Mini-Dump (0x24) SqlDumper.exe %1 0 0x24:0x4 0 %2 REM cycle back up to start of loop goto loop REM ---------------------------------------- :MissingArg1 ECHO Missing PID argument GOTO End1 :MissingArg2 ECHO Missing output directory argument GOTO End1 :End1
After executing the script, you will start to see the dump files collecting in the output directory…
Then, once the issue has been reproduced, just hit Ctrl-C to kill the batch script.
…to run the batch script from an elevated command prompt (i.e. Run as Administrator)
…to specify an output directory with plenty of room to store the dump files (a full-dump is everything in memory for the specified process; mini-dumps are much smaller)
…to use the correct Process ID (i.e. 1716) of the Analysis Services process (msmdsrv.exe) which can be found via task manager or in the far right column of SQL Server configuration manager under SQL Server Services. Keep in mind that this Process ID changes every time the service is restarted.