Improving Analysis Services Performance and Scalability with SQL Server 2016 Service Pack 1

  • Article

SQL Server 2016 Analysis Services delivered numerous performance improvements over previous releases, such as better modeling performance thanks to the new 1200 compatibility level for tabular databases, better processing performance for tables with multiple partitions thanks to parallel partition processing, and better query performance thanks to additional DAX functions that help to optimize the client/server communication. And with SQL Server 2016 Service Pack 1 (SP1), Analysis Services can deliver even more performance and scalability improvements through NUMA awareness and optimized memory allocation based on Intel Threading Building Blocks (Intel TBB), helping customers to lower Total Cost of Ownership (TCO) by supporting more users on fewer, more powerful enterprise servers.

SQL Server 2016 SP1 Analysis Services features improvements in these key areas:

  • NUMA awareness - For better NUMA support, the in-memory (VertiPaq) engine inside Analysis Services SP1 maintains a separate job queue on each NUMA node. This means that the segment scan jobs run on the same node where the memory is allocated for the segment data. Note, NUMA awareness is only enabled by default on systems with at least four NUMA nodes. On two-node systems, the costs of accessing remote allocated memory generally doesn’t warrant the overhead of managing NUMA specifics.
  • Memory allocation - Analysis Services SP1 uses an Intel TBB-based scalable allocator that provides separate memory pools for every core. As the number of cores increases, the system can scale almost linearly.
  • Heap fragmentation - The Intel TBB-based scalable allocator is also expected to help mitigate performance problems due to heap fragmentation that have been shown to occur with the Windows Heap. For more information, see the Intel TBB product brief at https://software.intel.com/sites/products/collateral/hpc/tbb/Intel_tbb4_product_brief.pdf.

Microsoft internal performance and scalability testing shows significant gains in query throughput when running SQL Server 2016 SP1 Analysis Services on large multi-node enterprise servers in comparison to previous Analysis Services versions. Please note that results may vary depending on your specific data and workload characteristics.

Download SQL Server 2016 SP1 from the Microsoft Download Center at https://www.microsoft.com/en-us/download/details.aspx?id=54276 and see for yourself how you can scale with Analysis Services SP1. Also, be sure to stay tuned for more blog posts and white papers covering these exciting performance and scalability improvements in more detail.