Using Star Join and Few-Outer-Row Optimizations to Improve Data Warehousing Queries

Writers: Dayong Gu (Microsoft), Ashit Gosalia (Microsoft), Wei Liu (NetApp), Vinay Kulkarni (IBM)

Technical Reviewer: Susan Price (Microsoft)

Published: August 2009; Updated: December 2010

Applies To: SQL Server 2008, SQL Server 2008 R2

Summary: In this white paper we discuss two of the new features introduced in SQL Server 2008, Star Join and Few-Outer-Row optimizations. These two features are in SQL Server 2008 R2 as well.  We test the performance of SQL Server 2008 on a set of complex data warehouse queries designed to highlight the effect of these two features and observed a significant performance gain over SQL Server 2005 (without these two features). The results observed also apply to SQL Server 2008 R2.  On average, about 75 percent of the query execution time has been reduced, compared to SQL Server 2005. We also include data that shows a reduction in the number of rows processed and improved balance in parallel queries, both of which highlight the important role the Star Join and Few Outer-Row features played.

To review the document, please download the Using Star Join and Few-Outer-Row Optimizations to Improve Data Warehousing Queries Word document.

• Ask a question in the SQL Server 2008 Forums
• Send Feedback