Udostępnij za pośrednictwem

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

  • Artykuł

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.

Comments

  • Anonymous
    November 17, 2016
    Also note that the allocator improvement should benefit both, Multidimensional and Tabular!
    • Anonymous
      November 17, 2016
      regarding improvements which affect MD mode, are you saying that ONLY the memory allocation improvements will affect MD?
  • Anonymous
    November 17, 2016
    Nice, about time SSAS was NUMA aware.Good work SSAS team.
  • Anonymous
    November 17, 2016
    Multidimensional was already NUMA aware, Scott.
  • Anonymous
    November 18, 2016
    so SQL DB engine just announced in 2016 SP! that one goal's "scope was to have a consistent programmability surface area between Enterprise and Standard Editions"... and in doing so have enabled partitioning within Standard Edition... any such goal to see similar in the SQL AS world?
    • Anonymous
      November 29, 2016
      With SQL 2016, Tabular just became part of Standard Edition with a 16 GB memory limit. Given this limit, it doesn’t seem necessary to use partitioning, does it?
      • Anonymous
        December 23, 2016
        I do not agree. 16GB memory limit can still handle several fact tables with hundreds of millions of rows and those can take long time to process, partitioning would be very helpful. Also as SQL team stated - "scope was to have a consistent programmability surface area between Enterprise and Standard Editions”. Seems you have ignored it here, as you can't design a proper product for SSAS Tabular which works on both Standard and Enterprise.
        • Anonymous
          December 30, 2016
          Martin, I think I see your point: You want to write an app without having to check the server edition (https://msdn.microsoft.com/en-us/library/microsoft.analysisservices.core.server.edition.aspx). For now, though, you'll have to do this to disable such things as Partitioning features when connecting to editions with limited API support. It's unlikely that the licensing will change for the SQL 2016 release of Analysis Services, but maybe there is a possibility for vNext. I'll bring it up with our licensing decision makers. No promises, though.
  • Anonymous
    November 20, 2016
    This is great! I'd like to share as much useful detail as possible at my upcoming SQL Saturday presentation on SSAS Tabular 2016 (http://bit.ly/2dd2vPb) - something that distinguishes Intel TBB in SP1 from SQL 16 RTM. (If no blog posts or whitepapers will be available soon any quick tips/ideas would be appreciated. (Not a huge issue though, so much great content in SSAS Tabular 2016 as is).
    • Anonymous
      November 29, 2016
      Brett, we believe that the default configuration settings for SP1 will give customers the best experience, so the advice is pretty much, "don't change them" unless you are in contact with CSS and a support engineer tells you otherwise.