Star join query optimization - Change partitioning, use columnstore?

Question

I am trying to know the best possible way to improve performance of a query given to me by my client. It contains a few tables which are joined and one of table is called dwh.fac_sale_detail which contains 1.5 billion rows.

This table dwh.fac_sale_detail is partitioned based on one of its columns called TradingDateKey1. It actually stores data in yyyymmdd format but it is of INT Datatype.

This has TradingDateKeys from 2005 to 2015, but the partitions are created until year 2014 only.

One of the guys in another team advised the following and I am trying to follow his advice but I am new to creating or altering of partitions and don't know if this will actually make any difference to query performance:

What he said in his own words is "The FactSalesDetail table is currently about 1.5 Billion Rows and is currently partitioned on TradingDate into 10 partitions by year with about 150 Million Rows per partition. It would be a good idea to further partition the latest year into monthly partitions and apply a columnstore index on all the partitions. Applying the index on each partition will be a once-off and you should only need to maintain the index for the current partition going forward."

Here is the query plan for the query I am trying to optimize.

Please also see the attached screenshots for a better understanding:

Answer 1

Thanks for adding the query plan; it is very informative. I have a number of recommendations based on the query plan, but first a caveat: don't just take what I say and assume it's correct, try it out (ideally in your testing environment) first and make sure you understand why the changes do or don't improve your query!

The query plan: an overview

From this query plan (as well as the corresponding XML), we can immediately see a few useful pieces of information:

• You are on SQL 2012
• This is a classic star join query and you are getting the benefit of the in-row bitmap filter optimization that was added in SQL 2008 for such plans
• The fact table contains about 1.5 billion rows, and just over 500 million of those rows match the dimension filters
• The query requests 72GB of memory, but is only granted 12GB of memory (presumably, 12GB is the max that will be granted to any given query, meaning your machine likely has ~64GB of memory)
• SQL Server is performing a sort-stream aggregate that takes 500 million rows down to just 600,000 rows. The sort is exceeding it's memory grant and spilling to tempdb
• We have warnings for plan-affecting converts due to either explicit and implicit conversions in your query
• The query uses 32 threads, but the initial seek into your fact table has an enormous thread skew; just 2 of the 32 threads do all of the work. (At subsequent steps in the query plan, however, the work is more balanced.)

Optimization: columnstore or not

This is a tough question, but on balance I would not recommend columnstore for you in this case. The primary reason is that you are on SQL 2012, so if you are able to upgrade to SQL 2014 I think it might be worth trying out columnstore.

In general, your query is the type that columnstore was designed for and could benefit greatly from the reduced I/O of columnstore and the greater CPU efficiency of batch mode.

However, the limitations of columnstore in SQL 2012 are just too great, and the tempdb spill behavior, where any spill will cause SQL Server to abandon batch mode entirely, can be a devastating penalty that might come into play with the large volumes of rows you are working with. If you do go with columnstore on SQL 2012, be prepared to baby-sit all of your queries very closely and ensure that batch mode can always be used.

Optimization: more partitions?

I don't think that more partitions will help this particular query. You are welcome to try it, of course, but keep in mind that partitioning is primarily a data management feature (the ability to swap in new data in your ETL processes via SWITCH PARTITION and not a performance feature. It can obviously help performance in some cases, but similarly it can hurt performance in others (e.g., lots of singleton seeks that now have to be performed once per partition).

If you do go with columnstore, I think that loading your data for better segment elimination will be more important than partitioning; ideally you will probably want as many rows in each partition as possible in order to have full columnstore segments and great compression rates.

Optimization: improving cardinality estimates

Because you have a huge fact table and a handful of very small (hundreds or thousands of rows) set of rows from each dimension table, I would recommend an approach where you explicitly create a temporary table containing only the dimension rows that you plan to use. For example, rather than join to Dim_Date with a complicated logic like cast(right(ALHDWH.dwh.Dim_Date.Financial_Year,4) as int) IN ( 2015, 2014, 2013, 2012, 2011 ), you should write a pre-proccessing query to extract only the rows from Dim_Date that you care about and add the appropriate PK to those rows.

This will allow SQL Server to create statistics on just the rows you are actually using, which may yield better cardinality estimates throughout the plan. Because this pre-processing would be such a trivial amount of work compared to the overall query complexity, I would highly recommend this option.

Optimization: reducing thread skew

It's likely that extracting the data from Dim_Date into it's own table and adding a primary key to that table would also help to reduce thread skew (an imbalance of work across threads). Here's a picture that helps show why:

In this case, the Dim_Date table has 22,000 rows, SQL Server estimated that you are going to use 7,700 of those rows, and you actually only used 1,827 of those rows.

Because SQL Server uses statistics in order to allocate ranges of rows to threads, the poor cardinality estimates in this case are likely the root cause of the very poor distribution of rows.

Thread skew on 1,872 rows may not matter much, but the painful point is that this then cascades down to the seek into your 1.5 billion row fact table, where we have 30 threads sitting idle while 600 million rows are being processed by 2 threads.

Optimization: getting rid of the sort spill

Another area I would focus on is the sort spill. I think that the primary problem in this case is poor cardinality estimates. As we can see below, SQL Server thinks that the grouping operation being performed by the combination of a Sort and Stream Aggregate will yield 324 million rows. However, it actually yields just 643,000 rows.

If SQL Server knew that so few rows would come out of this grouping, it would almost certainly use a HASH GROUP (Hash Aggregate) rather than a SORT GROUP (Sort-Stream) in order to implement your GROUP BY clause.

It's possible that this may fix itself if you make some of the other changes above in order to improve cardinality estimates. However, if it doesn't you could try to use the OPTION (HASH GROUP) query hint in order to force SQL Server to do so. This would let you evaluate the magnitude of the improvement and decide whether or not to use the query hint in production. I would generally be cautious about query hints, but specifying just HASH GROUP is a much lighter touch than something like using a join hint, using FORCE ORDER, or otherwise taking too much of the control out of the query optimizer's hands.

Optimization: memory grants

One last potential problem was that SQL Server estimated that the query would want to use 72GB of memory, but your server was not able to provision this much memory to the query. While it's technically true that adding more memory to the server would help, I think there are at least a couple other ways to attack this problem:

• Get rid of the Sort operator (as described above); it's really the only operator consuming any substantial memory grant in your query
• Split your query up into multiple batches; it may be the case that you can run the query once per partition, for example. This could reduce the size of the sort, keep it in memory, and potentially improve performance significantly. A side benefit could be that you might get better utilization of threads if you access just one partition since this does impact the way that SQL Server allocates threads to partitions in some cases.

Answer 2

Optimizing STAR Queries is in many respects the same as optimizing other query styles. Additionally STAR Queries present some special considerations. It may pay for you to revisit some of the essentials and basics.

1. PRECISION STYLE vs. WAREHOUSE STYLE. Any query STAR or otherwise can be classified as PRECISION or WAREHOUSE style. By this this we mean it returns either a large number of rows or a small number of rows. Large and Small are most often defined as a % OF ROWS from a table after filtering and within this context, the 2% RULE is often quoted, as this percentage is both mathematically sound and successful in real world practice as a guideline for most query situations in defining query style. What all this means is that if your want <2% of the rows in a table then an index may be better for fetching those rows, but if you want >2% of the rows in a table then a table scan is likely better. Yes there are fringe cases, and variatons for specific data skews, and different database management products have different capabilities which means the actual % may be more or less than 2%, but overall this rule works well, particularly as a starting point for optimizing queries.

2. Given the idea of a "2% RULE%", a true STAR Query is intended to return <2% of the rows in its fact table. A fact table surrounded by dimensions and covered in BITMAP indexes, does not imply a query against this design is a STAR Query. A "STAR Query" which returns >2% of of the rows from its fact table is not a slow STAR Query but is instead a DATA MINING operation using the wrong database design. Additionally BITMAP indexes were created specifically for the STAR Query problem of optimization. Thus for a STAR Query, though no particular single index must identify <2% of the rows, after all indexes searched are combined via the BITMAP MERGE step, the resulting rowset returned should be <2% of the rows in your fact table. This is defined in RAW rows from the fact table, not aggregated rows. Otherwise you would be better off simply scanning the FACT table in most cases and not wasting time on indexes at all. Thus even a STAR Query is dominated in the optimization space by the concept of PRECISION vs. WAREHOUSE style.

3. Partitioning for Performance. Partitioning from a performance perspective has two main utilities. First is partition pruning. For queries whose predicates provide filtering against partition keys, an optimizer can skip entire partitions rather than scan them. This reduces IO. The second use is to enable Parallel Joins done as partition pairs. If two tables in a query are both equi-partitioned across the join key of a query, that query can join the two tables in parallel using only matching pairs of partitions. This makes the join highly scalable, and significanly reduces memory needs. Again though the key here is equi-partitioning across the join columns.

4. Columnar Storage and Performance. A columnar data store offers performance also in two basic ways. First is compression. This storage strategy colocates similar data. Thus, particularly if the step of presorting is taken, large comression ratios can be achieved in a column data store. This compression can under the right conditions greatly reduce IO costs. Second, this storage stategy seeks to exploit the fact that most queries do not require all columns. Thus a columnar data store opens the possibily of skipping columns which are not required by a query. Again this can reduce IO costs. As a general rule, if a query requires <5% of the columns in a table, the a performance boost from column projection can be significant.

5. PRE-Aggregation. Summary tables still play a significant role in performance. It will alway be quicker to fetch rows already summed rather than take the detail and sum them every time you need them. In the STAR Query space, this usually means monitoring queries to see what the most popular dimensions are that are being sliced/diced/hierarchically traversed. These make good candidates for summary tables, assuming your database system is capable of transparently identfying these summary objects when needed.

With this in mind, start by asking yourself some questions:

1. do you have the right design? Is your query really a STAR Query that is ultimately retuning <2% of the rows from the fact table? Or are you trying to do somthing large against the wrong storage design?

2. do your query predicates mapsto your partitioning scheme? If so, are they exploiting partitioning (actually doing pruning)? If so, are you then possibly a candidate for parallelism across one of the joins?

3. what % of columns are you looking for? Can you achieve an order of magnitude reduction is space requirements using compression and thus maybe speed up query time by reducing IO? Are you looking for <5% of the columns in your table and thus might benefit from a columnstore to skip unwated parts fo the table.

4. how much of your query cost is from IO and how much from joins / aggregations / sorts? Where your costs sit will drive your performance feature choices.

5. are there specific aggregations which if they existed would dramatically change your query performance profile?

As to your friend's advice, you should ask for more clarifications. There are clearly many assumptions there. 1. that older partitions are readonly. 2. that your query is fetching specific months rather than crossing years (compare two quarters for example).

Lastly consider that if your friend things partitioning and columnstore might be significant aids, then that suggests your query is not really a STAR Query since if it was, then the BITMAP INDEXING would be the main performance driver of the query and its partitioning and columnar storage would mean very little to overall performance. Thus it should be apparent that you need first to look at what the query wants and map it to the right storage design.