By: Siddharth Mehta
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.
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.
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.
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.
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.
Additional Information
- For an exhaustive reference of SSAS Server Properties, consider reading this article on MSDN.
