Home > Analysis Services, Configuration, Tips, Utilities > SSAS Deployment essentials

SSAS Deployment essentials

Over the recent years i have enjoyed the privilege of working on a number of different SSAS deployments. Some are huge, some are complex and some of them are huge and complex and most interestingly they all behave differently.

What i want to share today is what i consider to be essential for an SSAS installation. This covers what i expect to see installed to compliment SSAS, configuration settings to be changed and the literature that should be at your finger tips.

Tools of the trade

ASTrace

I simply can’t imagine life without ASTrace. This utility writes a SSAS trace to a database and effectively turns a black box into an open book. The trace data is an absolute goldmine and with it i am able to work out why the users reported slowness 5 minutes ago and i have been able to get to the root cause of SSAS crashes quickly and work with Microsoft effectively. Other uses are to allow developers to run a stored procedure against the data to give details of whats currently running without granting sysadmin on the SSAS server. The opportunities are huge and if you don’t want to write a trace to a db then write it to a file but make sure you have a trace!

Resmon

This is a cube that captures resource information about SSAS and i love the irony of a cube that captures metrics on the health of SSAS! !  Its a fantastic tool and for example exposes details of your memory utilisation and you can drill down to the most granular levels. An example of what i might use it for is analysing performance of a cube on the server after a release by comparing before and after and perhaps identify a poorly designed attribute…… The potential is huge and the footprint is small so get it installed now from here. I would also encourage some community effort to enhance the cube, i have a number of ideas and know of other similar tools which could be merged together so watch that space for future enhancements.

SSAS Stored Procedure project

Another fantastic tool to extend functionality. Read what it can do (and share it with developers) and then decide if its for you. Some of the uses may not be immediately obvious but i like to have it on our non production servers as a minimum just to be able to clear the filesystemcaches easily so that testing is consistent and repeatable.

Activity Viewer

A must have tool to quickly see whats happening on the server. It compliments the traces nicely but i have found myself using it less with the introduction of DMV’s in 2008.

Essential Literature

SSAS 2008 R2 Operations Guide

IMHO everyone should have a copy of this on their desk and/or one click away and you should have read it more than once…. 😀

Analysis Services Performance Guide

This is an absolute must read and one vital thing to remember is you don’t read it once, you read again and again and then you have at your finger tips for the next time you have to troubleshoot a box. This guide also covers a number of configurations settings and how changing one setting impacts another and yes this is where i read about MDX blocking.

Microsoft SQL Server 2008 Analysis Services Unleashed

IMHO (again?) YES! This book is fantastic and whilst it is technically deep its an absolute gold mine which has helped me get my head around and resolve mind numbing issues.

Vidas Matelis

Vidas Matelis recently put up a post that links to many great documents which he read in preparation for the SSAS Maestro. I would encourage reviewing the documents.

SSAS Wiki

Last but by no means least (especially as some of my post are present on the site Winking smile) i recently became aware of a wiki that someone has been working hard on to centralise SSAS information.

Configuration

There are only a handful of settings i change out of the box because to be honest Microsoft have done a damn good job of the default configuration and the other settings i change arise from monitoring the behaviour of  SSAS so the settings i am about to discuss are my baseline.

Note: Settings in a green or red indicate a dependancy

Setting Proposed Comments
CoordinatorQueryBalancingFactor      1 Together these setting alleviate
CoordinatorQueryBoostPriorityLevel      0 MDX Blocking
LimitFileSystemCache FileSystemCache is very important and should be set. Greg Galloway gives an example config here.
MemoryHeapType     2 Together these settings switch SSAS to using the Windows memory heap
HeapTypeForObjects     0 for better performance and stability.
PreAllocate     20 The allocates memory up front and we start at 20%. Also see what you should know.
Threadpool\Query\MaxThreads 2 * Cores (this is default for 2008)
Threadpool\Process\MaxThreads 10 * Cores (this is default for 2008)
LowMemoryLimit 20% below TotalMemoryLimit but also see PreAllocate. This allows the cleaner thread headroom to do its work.

So, as you can see i do not change many configurations and i must stress this is what i consider to be the optimal starting point to baseline from. Its quite likely other settings will be tuned and you might even need to tune the ones i have highlighted but that is less likely.

I want to finish this post by pointing out the one thing that is often missing in an SSAS deployment and that is a strong relationship between the DBA’s and the developers. This is fundamental and if it does not exist you will pay the price. I maintain a strong relationship between DBA & Dev and i believe there is a lot of respect from both sides which under pins our successes.

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: