Troubleshooting SSAS: Configure Memory Dump Generation for Effective SQL Server Analysis

Table of Contents

This article provides a comprehensive guide on how to configure SQL Server Analysis Services (SSAS) to automatically generate memory dump files. Memory dumps are crucial for troubleshooting and diagnosing unexpected issues within SSAS, such as crashes, hangs, or performance degradation. By capturing the state of the SSAS process at the time of an error, these files offer invaluable insights for developers and administrators seeking to resolve underlying problems and ensure system stability. This document outlines the steps to configure automatic memory dump generation and explains how to manually create dump files when needed.

Introduction¶

Microsoft SQL Server Analysis Services (SSAS) 2012 and later versions are equipped with the capability to automatically generate different types of memory dump files when exceptions occur. This automated process is designed to aid in the diagnosis and resolution of errors that may arise during SSAS operations. These dump files act as snapshots of the SSAS process’s memory, capturing critical information that can be analyzed to understand the root cause of the exception. Furthermore, this article will detail the use of the Sqldumper.exe utility, a powerful tool that allows administrators to manually obtain memory dump files for the SQL Server Analysis Services process on demand. Understanding both automatic and manual dump generation techniques is essential for effective SSAS troubleshooting and maintenance.

More Information¶

By default, SQL Server Analysis Services is configured to automatically generate minidump files whenever an exception is encountered. These minidump files are designed to be relatively small in size, containing essential debugging information without capturing the entire memory space of the process. For a standard installation of SSAS, these minidump files are written to a specific default location, which varies depending on the version of SQL Server Analysis Services in use. Below is a table outlining the default location for minidump files for different SSAS versions:

Analysis Services version	Location
2019	%ProgramFiles%\\Microsoft SQL Server\\MSAS15.InstanceName\\OLAP\\log
2017	%ProgramFiles%\\Microsoft SQL Server\\MSAS14.InstanceName\\OLAP\\log
2016	%ProgramFiles%\\Microsoft SQL Server MSAS13.InstanceName\\OLAP\\log
2014	%ProgramFiles%\\Microsoft SQL Server MSAS12.InstanceName\\OLAP\\log
2012	%ProgramFiles%\\Microsoft SQL Server MSAS11.InstanceName\\OLAP\\log

Note: InstanceName is a placeholder that must be replaced with the actual instance name of your SQL Server Analysis Services installation.

It is important to note that the default log location for an SSAS instance can be changed after installation. To verify the current log location for your specific SSAS instance, you should examine the msmdsrv.ini configuration file. This file contains various settings for the SSAS instance, including the log directory.

To programmatically determine the log location, you can query the Windows Registry. The ImagePath registry key holds the path to the Config directory, which in turn contains the msmdsrv.ini file. The registry subkey to examine depends on whether you are using the default instance or a named instance of SSAS:

Analysis Services version	Registry subkey
Default Instance	HKEY_LOCAL_MACHINE\\SYSTEM\\CurrentControlSet\\Services\\MSSQLServerOLAPService\\ImagePath
Named instance	HKEY_LOCAL_MACHINE\\SYSTEM\\CurrentControlSet\\Services\\MSOLAP$InstanceName\\ImagePath

Minidump files, while smaller than full dumps, still contain valuable debugging information. Typically, a minidump will include the following data points:

• All thread stacks: This shows the call stack for each thread running in the SSAS process at the time of the dump. This is crucial for understanding what each thread was doing when the exception occurred.
• Second-order memory referenced by stack pointers: This includes memory that is directly pointed to by variables on the thread stacks, providing context to the stack information.
• Process Environment Block (PEB) information: The PEB contains vital information about the process itself, such as loaded modules, environment variables, and process heaps.
• Thread Environment Block (TEB) information: The TEB contains thread-specific information, such as thread-local storage and exception handling data.
• Information about recently unloaded modules: This can be helpful in identifying issues related to module loading and unloading.
• Thread state information: This includes the current state of each thread (e.g., running, waiting, blocked), which can be useful in diagnosing deadlocks or thread synchronization problems.

The generation of memory dump files is controlled by the Exception section within the Msmdsrv.ini file. This file is typically located in the %ProgramFiles%\\Microsoft SQL Server\\MSASxx.InstanceName\\OLAP\\Config folder, where MSASxx is a placeholder for the specific SSAS version. Opening this file in a text editor like Notepad will reveal an XML section similar to the example below:

<Exception>
  <CreateAndSendCrashReports>1</CreateAndSendCrashReports>
  <CrashReportsFolder/>
  <SQLDumperFlagsOn>0x0</SQLDumperFlagsOn>
  <SQLDumperFlagsOff>0x0</SQLDumperFlagsOff>
  <MiniDumpFlagsOn>0x0</MiniDumpFlagsOn>
  <MiniDumpFlagsOff>0x0</MiniDumpFlagsOff>
  <MinidumpErrorList>0xC1000000, 0xC1000001, 0xC1000016, 0xC11D0005, 0xC102003F</MinidumpErrorList>
  <ExceptionHandlingMode>0</ExceptionHandlingMode>
  <CriticalErrorHandling>1</CriticalErrorHandling>
</Exception>

The settings within this <Exception> section govern the behavior of memory dump file generation. These settings can be modified directly in the Msmdsrv.ini file or through SQL Server Management Studio (SSMS) for a more user-friendly interface. For more detailed information about each setting, you can refer to the Log Properties documentation on the Microsoft Learn website.

Disable Automatic Memory Dump File Generation for Analysis Services¶

The CreateAndSendCrashReports setting is the primary control for enabling or disabling automatic memory dump file generation in SSAS. This setting accepts integer values that dictate the behavior of the dump generation process. The following table outlines the possible values and their corresponding descriptions:

| Value | Description ```

When the CreateAndSendCrashReports setting is set to 1 or 2, the other settings within the Exception section become relevant and can be used to further customize the type of memory dump file generated and the information included within it. These settings provide granular control over the dump generation process, allowing administrators to tailor the dumps to their specific troubleshooting needs.

Configure SQL Server Analysis Services to Generate a Full Dump File Automatically¶

To configure SQL Server Analysis Services to automatically generate a full dump file whenever an exception occurs, you need to modify the SQLDumperFlagsOn setting. Setting this value to 0x34 will instruct SSAS to create a full memory dump. Furthermore, if you require the full dump to include handle information, which can be beneficial in certain troubleshooting scenarios, you can set both SQLDumperFlagsOn to 0x34 and MiniDumpFlagsOn to 0x4. This combination of settings ensures a comprehensive dump file containing both full memory and handle details.

For example, the <Exception> section in your Msmdsrv.ini file might be configured as follows to achieve this:

<Exception>
  <CreateAndSendCrashReports>1</CreateAndSendCrashReports>
  <CrashReportsFolder/>
  <SQLDumperFlagsOn>0x34</SQLDumperFlagsOn>
  <SQLDumperFlagsOff>0x0</SQLDumperFlagsOff>
  <MiniDumpFlagsOn>0x4</MiniDumpFlagsOn>
  <MiniDumpFlagsOff>0x0</MiniDumpFlagsOff>
  <MinidumpErrorList>0xC1000000, 0xC1000001, 0xC1000016, 0xC11D0005, 0xC102003F</MinidumpErrorList>
  <ExceptionHandlingMode>0</ExceptionHandlingMode>
  <CriticalErrorHandling>1</CriticalErrorHandling>
</Exception>

Generate a Full Dump File that Includes Handle Information Manually¶

In situations where you are troubleshooting issues like an unresponsive server (hangs) or require a dump file at a specific moment in time, manually generating a full dump file, ideally one that includes handle information, is often the most effective approach. To accomplish this, you can utilize the Sqldumper.exe utility directly from the command prompt. The command syntax is as follows:

Sqldumper.exe PID 0 0x34:0x4 0 PathToDumpFile

Note:

• PID should be replaced with the Process ID of the SQL Server Analysis Services process you wish to dump. You can find the PID in Task Manager or using other system monitoring tools.
• PathToDumpFile should be replaced with the desired folder path where the dump file will be written. Ensure the directory exists and the user running the command has write permissions.

It’s crucial to execute this command from the Shared directory where your SSAS instance is installed. Alternatively, you can specify the full path to the Sqldumper.exe file in the command if you are running it from a different location. The Shared directory location varies depending on the SSAS version:

Analysis Services version	Location
2019	%ProgramFiles%\\Microsoft SQL Server\\150\\Shared
2017	%ProgramFiles%\\Microsoft SQL Server\\140\\Shared
2016	%ProgramFiles%\\Microsoft SQL Server\\130\\Shared
2014	%ProgramFiles%\\Microsoft SQL Server\\120\\Shared
2012	%ProgramFiles%\\Microsoft SQL Server\\110\\Shared

For example, if you are using SQL Server Analysis Services 2019, the default directory to run sqldumper.exe from is C:\\Program Files\\Microsoft SQL Server\\150\\Shared.

More Information¶

The SQLDumperFlagsOn setting allows you to fine-tune the behavior of the Sqldumper.exe utility by specifying various flags. These flags are bitmask values that control different aspects of the dump generation process. The following table details some of the key flags available:

Mnemonic name	Hexadecimal value	Description