My highlights from the Microsoft “Performance Tuning of Tabular Models” Paper

Microsoft have now released the highly anticipated and detailed paper on performance tuning tabular models.  It is available here

At over 180 pages and a high level of technicality it takes a bit of getting through, this blog aims to summarise a few of the points I feel are new to me and useful.  I wont cover the DAX query plan section in detail here but its well worth a read.  There is lots that i wont mention as I had already come across it elsewhere.

As such I will mostly be pointing at important sections in the document, all credit goes to the authors: John Sirmon, Greg Galloway, Cindy Gross, Karan Gulati

Query speed

• Avoiding error handling functions made easier with fourth parameter on SEARCH, FIND and the DIVIDE function (p47)
• Prefer ISEMPTY to ISBLANK (p50)
• Only evaluate many to many measures when you need to (p55):

• Train users of Power View and Excel to add measures first to avoid cross products of dimensions (p57)
• Beware of Excel compatibility mode and Pre 2010 Excel, this can send less efficient queries which return unnecessary sub total rows (p60)
• Excel slicers connected to a lot of pivots is a known performance issue (I thought it was just my cube!) (p64)

Processing

• The Process Data followed by process recalc pattern i knew about, however the paper also proposes that if you are overnight processing and users are not querying during your process then doing a process clear and then process full reduces your memory pressure as you don’t need both copies in memory at once.  Great for those smaller environments. (p75)
• You can control how many connections can be opened against your data source for parallel processing of tables, default is 10 (p82)
• Data types and encoding, lots to consider here, generally favour currency and avoid stings where you could use int. (p87)
• Compression setting can be adjusted to fine tune query performance vs processing time (p89)
• To process tables in parallel you need to manually adjust the XMLA to include Parallel tags () around all processing commands (p95)
• You can query to check when a process defrag is necessary rather than it just being an overhead task (p116)

Server Settings

• Get all the latest cumulative updates, lots of fixes and performance improvements are not in SP1 (118)
• On large servers with a lot of RAM raise the Low Memory limit as the default allocation is probably too aggressive (p127)

That’s all I will list out for now but there is a lot in there

Thanks

@CalvinFerns