Slides and demo code for Columnstore Index session

Almost a week has passed after SQLBits X in London, so I guess it’s about time for me to share the slides and demo code of my session on columnstore indexes. After all, I promised people I would do that – especially when I found out that I had enough demos prepared to fill two sessions!

I made some changes to the demo code. I added extra comments, not only to the demos I could not explain and run during the session, but also to the rest, so that people who missed the session will also be able to benefit. I also found and fixed the error that caused one of my demos to fail. It turned out to be as embarrassing as it was unspectacular – somewhere along the way, I must have accidentally fat-fingered the backspace button while the cursor was on the name of an index. And if the index name doesn’t match, queries against index-related DMVs tend to produce no results. <sigh>

After fixing this typo, I re-ran all demos and they now worked flawlessly.

One major catch (and those who were in my session already know this). I ran my demos on a database that I got from within Microsoft, and I have no permission to redistribute this database. That means that people can only study the code, but not run it – well, okay, they can run it, on the “small” version of the database and table (change database names AdventureWorks2008DWXL and AdventureWorks2008DWBig to AdventureWorks2008DW, and change table names FactResellerSalesXL and FactResellerSalesPart to FactResellerSales), but with the size of that table, I expect the optimizer to make completely different choices for the execution plans. So while you can’t see the actual performance benefit by running the code yourself, you can still learn the patterns to use to work around the many limitations of columnstore indexes and batch mode.

I normally prefer to use demo code that any attendee can replicate on their own test databases, but in this case, I simply did not have the time to make a realistic 100+-million row table, and I did not want to demonstrate columnstore indexes on an unrealistic and heavily skewed table or on a table that is too small to show the performance benefit.

File Attachment: Columnstore%20index.zip

80% off for SQL Azure!
The Curious Case of the Optimizer that doesn’t

Related Posts

No results found

1 Comment. Leave new

  • Francesco Quaratino
    April 5, 2012 15:02

    Your session was actually full of demos – a really good thing in my view. Weel done!

    Cheers.

    Reply

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Menu
%d bloggers like this:

By continuing to use the site, you agree to the use of cookies. more information

The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.

Close