SQL Server Analysis Services Administration

Configuring SQL Server Analysis Services Server Properties

This document provides an in-depth guide to configuring the server properties for SQL Server Analysis Services (SSAS). Proper configuration is crucial for performance, security, and stability of your Analysis Services environment.

Accessing Server Properties

You can access and modify SSAS server properties using SQL Server Management Studio (SSMS). Connect to your SSAS instance, right-click the instance name in Object Explorer, and select 'Properties'.

Key Configuration Areas

General Settings

The General tab in the SSAS server properties dialog allows you to configure fundamental aspects of your server:

• Data Directories: Specify the default locations for database files, log files, and temporary files. It is recommended to place these on separate drives for performance and manageability.
• TempDB Directory: Crucial for query processing. Ensure this location has sufficient space and high I/O throughput.
• Max Concurrent Connections: Limits the number of simultaneous client connections. Adjust based on your hardware and expected user load.
• Query Timeout: Sets the maximum time (in seconds) a query can run before being terminated.

Memory Properties

Memory configuration is one of the most critical aspects of SSAS performance. The Memory tab allows you to:

• Allocate Memory: Define how much physical memory SSAS can use. It's often recommended to leave a portion of RAM for the operating system and other applications.
• Working Set Size: Control the minimum and maximum memory allocated to the SSAS process.
• Enable Large Memory Addresses: (For 32-bit systems) Allows the SSAS process to access more than 2GB of memory.

Processing Settings

These settings impact how data is processed and aggregated within your SSAS databases:

• Max Parallelism: Controls the maximum number of threads that can be used concurrently for processing operations.
• Default Processing Mode: Set the default mode for processing new or altered objects (e.g., Default, Full, Incremental).

Network Settings

Configure network-related properties, though these are often managed by default network configurations unless specific requirements exist.

Security Properties

While most security is managed at the database and object level, some server-level settings can be influenced here, such as authentication modes.

Configuration Best Practices

• Separate Drives: Store data files, log files, and temporary files on different physical or logical drives, ideally on fast storage like SSDs.
• Monitor Resource Usage: Continuously monitor CPU, memory, and I/O performance using tools like Performance Monitor.
• Tune Memory Settings: Start with recommended settings and adjust incrementally based on workload and observed performance.
• Optimize Query Timeout: Set a reasonable query timeout to prevent runaway queries from impacting server stability.
• Regular Backups: Ensure a robust backup and restore strategy is in place.

Example Configuration Snippet (XMLA)

While you typically use SSMS, server properties can also be managed programmatically using XML for Analysis (XMLA). Below is an illustrative snippet:

<Alter Method="Merge" ObjectExpansion="ObjectProperties" xmlns="http://schemas.microsoft.com/analysisservices/2003/xmla">
  <Object>
    <ServerID>YourServerName</ServerID>
  </Object>
  <ObjectDefinition>
    <Server xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
      <Memory>
        <MaxMemoryPercentage>70</MaxMemoryPercentage>
        <TargetServerMemoryPercent>75</TargetServerMemoryPercent>
      </Memory>
      <LogFiles>
        <LogDirectory>D:\SSAS\Logs</LogDirectory>
      </LogFiles>
    </Server>
  </ObjectDefinition>
</Alter>
            

For more advanced configurations, refer to the official Microsoft documentation on SQL Server Analysis Services server properties.