Most of these options, you really shouldn't alter, but there are a few that you should know about. Here are a few SSAS properties that I think you should be aware of, and might want to change.
DataDir
This is where SSAS will, by default, put the SSAS database files. By default, this will be a sub directory of the "program files", which is not an ideal location. The SSAS databases should be on high performance storage. IO is very often a critical factor in large SSAS cubes.
ExternalCommandTimeout
As I understand it, this property is the number of seconds that SSAS will wait for the first row to return from an SQL query during processing. The default is 3600 seconds (1 hour) which is insufficient for many environments. I often change this to 36000 (10 hours) in larger environments, or environments with Oracle as an rdbms (just kidding).
ForceCommitTimeout
This is the amount of time (milliseconds) that SSAS will wait on queries while performing process updates. A process update cannot commit while a query is active. The default is 30 seconds, which I think is quite reasonable, but you might have a need to change this value.
QueryLogSampling
The default value is 10, so that 1 in 10 queries are recorded. In practice, I either need all queries or none. So what I do is change this value to 0 to minimise overhead. When I am running a performance tuning exercise, I will temporarily change this to 1.
LowMemoryLimit
If this number is between 1 and 100, it is the percentage of memory that SSAS can use, without concern. By default it is 65, which tends to be an appropriate value. You might wonder why your 100GB server never sees 65GB memory for SSAS, even though your cubes are much larger and the IO demands are huge. This is due to SSAS caching logic. Just because you have the memory and the cube is large doesn't mean that SSAS will hold the cube in memory. See my earlier blog http://richardlees.blogspot.com/2011/12/why-doesnt-ssas-cache-entire-cube.html
TotalMemoryLimit
You might see the LowMemoryLimit as a very soft limit, which can be exceeded when processing demands would benefit. The TotalMemoryLimit is a harder limit, but it can also be exceeded when necessary. One way that I look at these two options is the LowMemoryLimit is the memory limit during non processing times, and the TotalMemoryLimit is the limit when SSAS processing jobs are active. However, as I mentioned above, the more common concern is that SSAS isn't using the available memory, rather than it is using too much.
HardMemoryLimit
This is the real memory limit. Once SSAS reaches this value, it will start to cancel queries. I find that setting this value between TotalMemoryLimit and 100, will prevent one query (or several combined) from taking so much memory that SSAS over-commits the OS and causes the operating system to thrash so much it essentially stops servicing tasks. For example, an SSAS server might have TotalMemoryLimit set to 80 (that's 80% of available real memory) and I would set HardMemoryLimit to 82. That way, I know that if SSAS gets a rouge query that causes SSAS to overcommit virtual memory, it will cancel that query when SSAS reaches 82% of available memory. I would rather the query be cancelled than the OS die a slow death.
I haven't ever changed this property. Since 2005, SSAS hasn't offered a real drill-through. This is really a drill down option. If you want to support drill through see my blog http://richardlees.blogspot.com/2009/01/using-drill-through-in-analysis.html
Port
If you want to change the port that SSAS listens on (or you are running multiple instances) this is where you specify the port number. By default this value is 0, which denotes 2383.
ServerTimeout
This is the limit for SSAS queries in seconds. After this time (default 3600 or 1 hour) mdx queries will timeout. You may have reason to increase or decrease this value.
This comment has been removed by the author.
ReplyDelete