SQL Server Analysis Services Server Properties


By:
Overview

After installing SQL Server Analysis Services (SSAS) and gaining a understanding of some of the basic best practices, the next logical step is to configure and fine tune some of the key server properties. These server properties can be accessed by right-clicking on the SSAS server from SQL Server Management Studio (SSMS) and selecting Properties. These properties can be classified into different categories. We are going to look at some of the most relevant / important properties and outline the significance of each.

Explanation

1) Memory - The first category of server properties that an administrator may want to look into are related to system resources i.e. memory and network. Among the properties falling in the memory category, are LowMemoryLimit and TotalMemoryLimit.

  • LowMemoryLimit property indicates the threshold percentage beyond which the server starts the garbage collection process to free up unused resources. Based on server usage statistics, if you feel that the server tends to have high memory usage during certain loads and then it frees up memory, and if you do not want the garbage collector to run during those intervals, you can decrease the value of this property accordingly.
  • TotalMemoryLimit property indicates the total percentage of memory that should be made available to the SSAS server, which by default it 80. So if you find the SSAS server processes holding memory even if no significant workload then this property would be the reason for the situation.
SQL Server Analysis Services Memory Properties

2) Network - The network related group of properties are generally best left with default values. Most significant of these properties are related to IPv4 or IPv6 support. Based on the OS version, the appropriate value should be selected. But depending upon your network, a SSAS Administrator has the option to override the setting.

SQL Server Analysis Services Network Properties

3) Security - Properties in this category address how the server manages authentication and permissions. The default value for most of the properties are best practices and need not be changed. One property that you may want to check is the BuiltInAdminsAreServerAdmins. Generally in a production environment, network teams are admins of the servers.  But they need not be admins on the SSAS instance which might contain sensitive and/or confidential information. So if you do not want the local system admin to be admin on the SSAS instance implicitly, you should set this property to False.

SQL Server Analysis Services Security Properties

4) OLAP - Properties in this category focus on processing or query performance. Typically we start with the default value of the properties if the server usage is not known beforehand, which generally happens in case of a new server setup. Based on usage statistics and query performance log analysis, the values of these properties can be fine tuned to meet the performance needs.

SQL Server Analysis Services OLAP Properties

5) Log - Logging related properties are for query logging as well as exception logging. These properties need a dedicated and detailed explanation, so these would be covered in the upcoming chapters of this tutorial. For now you can take it as a category of properties that addresses logging.

SQL Server Analysis Services Log Properties
Additional Information
  • For an exhaustive reference of SSAS Server Properties, consider reading this article on MSDN.





Comments For This Article




Saturday, May 16, 2020 - 12:58:52 AM - parag Back To Top (85679)

Hello team,

I think LowMemoryLimit  option description is not correct.

LowMemoryLimit property indicates the threshold percentage beyond which the server starts the garbage collection process to free up unused resources. Based on server usage statistics, if you feel that the server tends to have high memory usage during certain loads and then it frees up memory, and if you do not want the garbage collector to run during those intervals, you can decrease the value of this property accordingly.

If value is lowered then garbage collector will start running when memory hits that value e.g. 55% of total memory etc.

I think we need to increase this value to make sure garbage collector does not run frequently.

Regards,

Parag















get free sql tips
agree to terms