SQL SSAS tabular is touted as being a fast in-memory OLAP engine that provides for the quick analysis of large amounts of data by users through ubiquitous client tools such as Excel. Users usually have two major considerations when querying these data sources, accuracy and performance (query and processing). This blog looks to highlight some of the possible areas that can assist in making your users happier with regards to query performance. This is not meant to be an in depth discussion on each of the topics but more to provide guidance on where possible performance improvements can be realised.
Please note that below information was obtained via the WWW as well as through personal experience and that not every concept needs to be applied and testing on a development server is highly recommended.
2. Software/Hardware Setup
- Physical Xeon 2 NUMA Node server
- Windows Server 2012
- Microsoft SQL Server 2012 SSAS Tabular
- Microsoft SQL Server 2012
- ~100GB Tabular Cube
3. Performance Considerations
3.1 Segmentation size
- Decrease this to increase compression and to decrease cold cache query time.
- Possible 50% gain on cold cache querying.
- Can be found under advanced settings, Vertipaq\DefaultSegmentRowCount, of the SSAS instance properties.
- Decreased to 3’s to kill queries that are blocking cube processing commits, as soon as possible
- Decreases cube processing time, as less blocking of occurs during heavy user querying, which in turn improves query time as querying the cube is slower whilst the cube is processing.
- Can be found under CommitTimeout, within the SSAS instance properties.
- Removes unneeded connections, especially connections left over from Excel workbooks being left open for prolonged periods of time.
- Changed from never (0) to 1hr so as to cleanout idle connections left over from open excel workbooks.
3.4 Split cubes on same SSAS instance
- Performance gains experienced by splitting one large cube into several smaller cubes as data sizes are less, also less querying of user queries occurs.
3.5 Manual Archiving
- If your cube makes use of table partitions then setup an automated archiving process that will allow you the flexibility of both archiving and restoring records for the purpose of maximising space utilisation and in turn increasing performance.
3.6 Process Defrag
- Facilitates the recalculation of a column’s dictionary.
- A dictionary can become fragmented due to the nature of processing tabular data which adds data but does not remove non-existent data from the dictionary, which results in un-warranted row scans during querying.
3.7 NUMA Affinitazation
- Due to the fact that the SSAS Tabular engine is not NUMA aware
- Experienced 20%-50% improvement
- Using Windows System Resource Manager
3.8 Excel/MSOLAP Versions
- MSOLAP .5 is the default OLE DB provider for Excel 2013, but this is not the case with previous versions of Excel, 2007 and 2010.
- MSOLAP .5 includes query optimizations and can be installed for previous Excel versions.
- Experienced ~32% improvement
3.9 SSAS SQL 2012 SP2
- SP provides some general performance gains when querying from Excel but these are more attributable to optimising MDX queries generated by Excel when using calculated members and Sub Select statements.
3.10 Split cubes onto different SSAS instances
- Processing of tabular cubes is instance wide; this causes instance wide locks which affect user queries on all cubes sharing the same instance.
- To facilitate a separation of concerns, between locking/processing and querying, a cube can be deployed to its own SSAS instance.
3.11 Replicate for Reporting
- Implementing a synchronisation strategy, between multiple SSAS instances (on the same server), can assist in alleviating contention between cube processing and querying, especially if processing time slots are looking to coincide with user querying time slots.
3.12 Split cubes to different servers
- To further separate cube processing and querying contention it is possible to implement a synchronisation strategy using two servers one for processing and one for querying.
3.13 Change BIOS Power Settings
- In checking your server’s power settings there are two areas of interest:
- Your OS power settings plan
- Your servers BIOS Power settings
- Both these settings can drastically affect your SSAS instance’s performance, which is further compounded due to the SSAS Tabular engines unawareness of NUMA nodes.
This blog presented several considerations for quick wins when trying to squeeze out some extra performance from your SSAS Tabular cube. These considerations are more highlights and further reading is recommended and easily obtainable online. As always base lining your current performance and the use of testing environments is key to realizing measureable and robust results.