SQL Server 2012 ColumnStore Indexes – Simple Demonstration

I was reading the new Microsoft e-book on SQL Server 2012 today, and found myself wanting to try out ColumnStore Indexes.

The example in the book doesn’t go step by step, so I thought I would try it myself in order to get a better understanding.

The ColumnStore Index was created to significantly increase query speeds for read only tables, which of course lends itself well to Data Warehousing. It is a powerful new tool, however read the documentation carefully to ensure your scenario doesn’t come across any of the many caveats.

Anyhow, download the Data Warehouse database from AdventureWorks, attach it and let’s start playing. The table FactResellerSales has over 60,000 rows, so a good place to start.

SELECT TOP 1000 [ProductKey]
,[OrderDateKey]
,[DueDateKey]
,[ShipDateKey]
,[ResellerKey]
,[EmployeeKey]
,[PromotionKey]
,[CurrencyKey]
,[SalesTerritoryKey]
,[SalesOrderNumber]
,[SalesOrderLineNumber]
,[RevisionNumber]
,[OrderQuantity]
,[UnitPrice]
,[ExtendedAmount]
,[UnitPriceDiscountPct]
,[DiscountAmount]
,[ProductStandardCost]
,[TotalProductCost]
,[SalesAmount]
,[TaxAmt]
,[Freight]
,[CarrierTrackingNumber]
,[CustomerPONumber]
,[OrderDate]
,[DueDate]
,[ShipDate]
FROM [AdventureWorksDW2012].[dbo].[FactResellerSales]

The effort of this query is shown in the following clustered index scan:


So, let’s create a ColumnStore Index

CREATE NONCLUSTERED COLUMNSTORE INDEX [NonClusteredColumnStoreIndex-Test1] ON [dbo].[FactResellerSales]
(
 [ProductKey],
 [OrderDateKey],
 [DueDateKey],
 [ShipDateKey],
 [ResellerKey],
 [EmployeeKey],
 [PromotionKey],
 [CurrencyKey],
 [SalesTerritoryKey],
 [SalesOrderNumber],
 [SalesOrderLineNumber],
 [RevisionNumber],
 [OrderQuantity],
 [UnitPrice],
 [ExtendedAmount],
 [UnitPriceDiscountPct],
 [DiscountAmount],
 [ProductStandardCost],
 [TotalProductCost],
 [SalesAmount],
 [TaxAmt],
 [Freight],
 [CarrierTrackingNumber],
 [CustomerPONumber],
 [OrderDate],
 [DueDate],
 [ShipDate]
)WITH (DROP_EXISTING = OFF)

Now if we run the same query again, we find that there is a new icon in the execution plan for a ColumnStore scan:

And we also discover that the estimated I/O cost has dropped right down from from 2.19868 to 0.158681.


So if you have an existing Data Warehouse with reporting then you can realize some really significant performance benefits just by moving to SQL Server 2012.

At the same time, you need to evaluate your candidate tables in order to ensure that they are compatible and would benefit from this new feature.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s