SQL Server 2008 will offer much more powerful relational data warehousing capabilities than its predecessor. As part of the development effort for the star join optimization in SQL Server 2008, Microsoft performed a number of performance studies based on benchmark and real customer workloads and made a significant enhancement.
Star Join Query Optimization is a new feature of SQL Server 2008 Enterprise Edition. This mechanism uses bitmap filtering for improving the performance of some types of queries by the effective retrieval of rows from fact tables.
SQL Server 2008 Star Join Query has
- Improved Query Response Times
- Automatically Implemented
- Query Selectivity
- Bitmap Filter – Integer Data Types
Understand – SQL Server 2008 Star Join Query Optimization (Video)
As per the Microsoft, they done a deep testing and It’s worth looking at the results from three of these workloads in the following figures.
Figure : Performance improvements with star join optimization
As the figure shows, all workloads improve significantly, by 12 up to 30 percent. While your individual mileage will vary, Microsoft expect decision support workloads against the SQL Server Engine to improve by approximately 15–20 percent based on the extension of the star join-specific optimizations that are new in SQL Server 2008.