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

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


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
  • You should 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.

Comments (4)

  1. EricWelsh says:

    Is this StarJoinInfo tag going to be implemented in a later version or SP, reason i ask is i have tested this numerous time with not a single XML output having the StarJoinInfo tag in it.

    Any thoughts?



  2. peternolan9 says:

    By the way….

    When I go to here I get ‘the forum you requested does not exist’…does this point somewhere else now?

    Best Regards

    Peter Nolan

  3. chriscamp says:

    bump… looking for the "more info" link

  4. Goodie_666 says:

    Me too… can’t find it anywhere

Skip to main content