How to Check Whether the Final Query Plan is Optimized for Star Join Queries?

  • Article

The star join optimization technique is an index based optimization designed for data warehousing scenarios to make optimal use of non-clustered indexes on the huge fact tables. The general idea is to use the non-clustered indexes on the fact table to limit the number of rows scanned from it. More details of index based star join optimization can be found at https://blogs.msdn.com/bi_systems/pages/164502.aspx.

The following discussion is based on SQL Server 2005 query plans.

In SQL Server 2005, we put "StarJoinInfo" element in Showplan XML to highlight star join optimization. If the query plan contains the “StarJoinInfo” element, then SQL Server has identified this plan as a star join plan and it definitely is one.

However, the query optimizer may not detect all star join plans due to star join detection restrictions. Hence there are some star join plans that won’t have the StarJoinInfo. This post will shed some light on how to manually detect if a given query plan is a star join plan.

These steps can help you identify what’s NOT a star join plan:

  • First identify your fact table.
  • If you see clustered index scan (or table scan) on fact table, then it’s not an index-based star join plan (however, this is a valid multi-table join plan, which can benefit from multiple-bitmap filter pushdown).

To identify a star join plan, you should:

  • Again, first identify your fact table
  • Youshould have a single RID lookup or clustered index seek on the fact table
  • Restrictive dimensions (dimension tables with restrictive filters in the query) should be processed before processing the clustered index seek or RID lookup on the fact table. You should find either:
    • A Cartesian product between the dimensions joined with a multi-column index on the fact table.
    • Or a semi-join of the dimensions with some non-clustered indexes on the fact table.
    • Or a join between multiple dimensions.
  • Non-restrictive dimensions are joined later with the fact table.

So the rule of thumb is: to detect whether a given plan is using index-based star join optimization, you should always look for a seek on a fact table that is based on some joins of some dimension tables.