Analysis Services Distinct Count Optimization Using Solid State Devices

A Real Practices Case Study at Xbox LIVE

Authors: Joseph Szymanski, Tyson Solberg, Denny Lee

Technical Reviewers: Lindsey Allen, Akshai Mirchandani, Heidi Steen

Executive Summary

To expand on the distinct count optimization techniques provided in the Analysis Services Distinct Count Optimization white paper, this technical note shows how using solid state devices (SSDs) can improve distinct count measures. We recount the experiences of the Microsoft Entertainment and Devices Data Warehousing Team (known for Xbox, Xbox LIVE, XNA, and Zune) in our analysis of applying SSDs to a real-world, distinct count heavy, Microsoft SQL Server Analysis Services customer environment. The key conclusion is that enterprise SSD devices, when combined with a well optimized Analysis Services MOLAP cube, will drastically improve the performance and scalability of the cube when it accesses distinct count measures. It can also improve non-distinct count measures, if the calculations being performed rely heavily on storage-engine calculations.

Read more ...