CODEWITHSUNDEEP
sql-serverssasperformance-testingsql-server-2016ssas-2016
user11156893
user11156893

Reputation:

Do Clustered Columnstore Indexes Affect SSAS Query End User Performance

Do Clustered Columnstore Indexes affect SSAS Performance querying for the end-user, and how could to fix this? Read an article below here how Sort order affects SSAS query customer user performance.

Is there any way to resolve this issue?

Would rebuilding SSAS indexes/aggregations work? Already know time to process the cubes from Data Warehouse to SSAS can be affected. What real concern is about is end customer-user SSAS querying experience.

Currently implementing Multidimension model in SSAS Cube.

Upvotes: 9

Views: 1174

Answers (1)

Ferdipux
Ferdipux

Reputation: 5246

Well, it depends. Let's start from the problem definition.

  • SSAS multidimensional performs better when fed ordered data on processing step. This article gives you reasons why and insights on data ordering.
  • SSAS Index and Aggregations processing will not fix source data being not ordered; therefore, it will not fix problems described above. These processing tasks build artifacts based on data received, and it cannot fix problems with the data itself.
  • MS SQL Columnstore Index is roughly a new storage technology -- columnstore compression applied to heap tables. This gives fast insert (no indexes, no pre-sort required) compared to table with Clustered Index. Downside - SELECT query on table with Clustered Index is likely to return rows ordered on Clustered Index base (unless you set ordering with ORDER BY statement), while the same query on the Clustered Columnstore table will yield unsorted data.
    This problem of unsorted data with Clustered Columnstore index affects not only SSAS, it degrades query performance when CCI could do so called segment elimination. There are some techniques to defeat that - sorting data before converting regular table to CCI or sorting data on load to CCI table.
  • The main problem of the discussion you mentioned is that data ordering is done with additional views on SQL level. Then author defines partitions on SSAS, and reports that SSAS generated queries have suboptimal execution plans.

Regarding SSAS performance on unordered data. It certainly will be suboptimal, but to what extent? In fact, only tests will show it; it can depend on multitude of factors - initial data set, cube design, end user queries. Growth of cube structures will slow down operations, but how much? From the experience - I would bother and give efforts to provide data ordering if cube is 100+ GB and its biggest partition/measure group is more than 10% of RAM used by SSAS. In other circumstances I would not bother about such problem.

Ordering data from CCI. First, avoid obsolete syntax 

SELECT TOP 2147483647 ... FROM ... ORDER BY ...

Use ANSI-compliant and less restrictive 

SELECT ... FROM ... ORDER BY ... OFFSET 0 ROWS

Regarding suboptimal execution plan when used in SSAS Partition definition. Unfortunately, SSAS query generation engine does not allow magical option (recompile). Again, if this is a serious problem - define a table-valued function (parametric view) to achieve optimal execution plan, and use this TVF in SSAS partition definition.

If this is the first implementation of the project - I would go without such measures and report it as project risks which require attention on go productive and possibly - additional efforts after.

Unfortunately, rebuilding SSAS indexes/aggregations will not improve the situation. You need to pre-sort data on DB query level when feeding into SSAS.

Upvotes: 2

Related Questions