Netherlands: Software

Introductie van Micorosoft SQL Server 2016

Issue link: http://hub-nl.insight.com/i/692679

Contents of this Issue

Navigation

Page 22 of 212

12 C H A P T E R 1 | Faster queries An unnatural hierarchy is one for which there is a more arbitrary relationship between levels. For example, consider a hierarchy with a Color level at the top and a Size level at the bottom. A color such as black can have sizes of L and M, but the color white can also have these sizes. You cannot define attribute relationships for this data because an error occurs during processing when the same size has multiple relationships. In earlier versions of Analysis Services, it is considered best practice to avoid the use of unnatural hierarchies or to "naturalize" the data by artificially enhancing it to enforce the one-to-many structure between levels and then defining attribute relationships. Next, query processing is optimized for various other situations, including distinct count queries on ROLAP partitions. Now Analysis Services performs these queries faster by improving the processing time after the data is retrieved from SQL Server. Furthermore, if you add a columnstore index to the ROLAP partition source, you can experience significantly better performance gains on distinct count queries. Last, a change in memory allocation request handling is available in SQL Server 2016. Prior to this version, you had two options: Windows Low-Fragmentation Heap (LFH) or a custom heap allocator for Analysis Services. In the last few versions, the default is set to LFH to improve performance for multiuser workloads. Ordinarily, LFH works well with small memory blocks, but some situations can compromise the efficiency of LFH for Analysis Services by fragmenting memory, which in turn reduces query performance. If you have queries that execute satisfactorily after the service restarts but slow down over time, a possible cause is fragmentation. In SQL Server 2016 Analysis Services, the default option is a new hybrid allocator that strikes a balance by using LFH for small allocations and the custom heap allocator for large allocations. You can still configure the msmdrv.ini file for Analysis Services to force LFH or the custom heap allocator if necessary. Note Tabular mode uses the same memory settings as multidimensional mode, but it has its own msmdsrv.ini file in which these settings are defined. The tabular mode tends to require larger allocations than multidimensional mode, so the new hybrid allocator should rely more heavily on the custom heap allocator. Understanding tabular performance improvements Tabular-model performance has always been good because of its use of the same columnar technology as columnstore indexes and in-memory OLTP. However, it's even better in SQL Server 2016 because of DAX optimizations, storage-engine caching improvements, and changes to the way that T-SQL is generated for DirectQuery mode. The first DAX optimization is a reduction in the number of queries sent to the storage engine from Power BI and Power BI Desktop. Prior to this change, a single chart could potentially send hundreds of queries to the storage engine. Now the Power BI client tools generate a single storage-engine query, whether the storage engine retrieves data from memory or from SQL Server in DirectQuery mode—as long as the query includes only simple measures. Furthermore, a single DAX query can return multiple result sets as intermediate results that Power BI can use across multiple requests. The next optimization executes queries more efficiently in any client tool, including Microsoft Excel or SQL Server Reporting Services (SSRS) in addition to the Power BI client tools. This efficiency is a result of changes in the following areas that affect measure execution: Variables As we describe in more detail in Chapter 6, "More analytics," the use of variables in a DAX expression for measures allows you to reuse logic within the same expression, which can reduce overall execution time. Conditional expressions Rather than evaluating each branch of an IF or SWITCH conditional expression, a branch with a false condition no longer generates a storage-engine query.

Articles in this issue

Archives of this issue

view archives of Netherlands: Software - Introductie van Micorosoft SQL Server 2016