Level: Intermediate

Kevin Goff

Microsoft SQL Server MVP

Practice Manager

SetFocus, LLC

It might seem far-fetched that a company would upgrade to SQL Server 2012 "just" for a database index. But after you see the performance enhancements gained from the new columnstore index in SQL Server 2012, you'll see why many are calling this one of the most important features in the history of SQL Server. In this presentation, I'll talk about the physical aspects of columnstore index in SQL Server 2012, how to create it, and what environments (mainly data warehousing environments) and queries can make use of it. I'll show performance benchmarks between columnstore indexes in SQL Server 2012, and compare them to index strategies prior to SQL Server 2012. I'll also show how the new Batch execution mode can improve performance as well. Finally, I'll show what queries can take advantage of the columnstore index (and which ones can't) and how to use partition switching as a workaround for the fact that the columnstore index is a read only index.

You will learn:

• Where environments can best leverage the columnstore index
• Which queries can take optimum advantage of the columnstore index, and which ones can't
• What a columnstore index really is "under the hood", how SQL Server works with it, and how SQL Server can generate such huge performance gains