Updated: June 17, 2020
Memory is everything—especially when it comes to SQL Server Analysis Services (SSAS), where memory constraints are a common issue. Because the SSAS Tabular database should reside entirely in-memory in Tabular mode, performance bottlenecks can be particularly challenging in that mode.
Although every environment is different, in SSAS Tabular mode, the database in-memory is typically compressed up to 10x versus on disk. But if the system isn’t configured properly, various data-processing actions can gum up the works (so to speak). A memory-efficient data model relieves that memory pressure when processing data in SSAS.
Three specific configuration hacks can turn your data model into the picture of efficiency. These hacks help you optimize SSAS performance, reduce bottlenecks, and boost speed.
SSAS uses configurable memory limits to instruct its cleaner on the handling of both low and high total system memory. If you run SSAS on the same machine as a relational SQL Server instance, you can encounter conflicts when these two programs compete for resources. The cleaning process can also affect performance in and of itself, so keeping track of these settings is important.
The three most important memory limits in Tabular mode are low, high, and VertiPaq.
You can use these settings to allocate more space to memory (if free space is available on the server). Be aware that at allocations above 100, the setting is in bytes. Don’t let this change trip you up.
Two strategies can lead to SSAS performance benefits:
Memory Usage Shown on BI Sentry Dashboard
An easy way to decrease the data model size—thus making it more memory efficient—is to limit the columns that are included in the data model. By including only the necessary columns from the relational data source, you can save memory during columnar compression. BI Sentry automatically collects object memory usage data that you can use to identify memory intensive columns that you might not need.
Object Memory Usage
The third SSAS memory hack is also a simple one: When possible, limit cardinality. Columns with unique data may be compressed minimally (or not at all), reducing memory efficiency. You can improve compression by modifying or splitting columns that have high cardinality. DateTime columns are a great example. Consider removing the Time element if it’s not needed. Or, you can split them into separate columns for date and time, decreasing the amount of cardinality and thus increasing compression. You can also use BI Sentry to identify high-cardinality columns.
These three little hacks can make a big difference in the efficiency of your data model—and SSAS performance. The changes discussed here are even easier to implement with the insight that SQL Sentry provides. We recently extended our free trials for all products to 30-days. There's no better time to take SQL Sentry for a spin, risk-free.