Search results matching tag 'columnstore'

Bleeding Edge 2012 – session material

Hugo Kornelis — Sat, 27 Oct 2012 10:46:00 GMT

As promised, here are the slide deck and demo code I used for my presentation at the Bleeding Edge 2012 conference in Laško, Slovenia. Okay, I promised to have them up by Tuesday or Wednesday at worst, and it is now Saturday – my apologies for the delay.

Thanks again to all the attendees of my session. I hope you enjoyed it, and if you have any question then please don’t hesitate to get in touch with me.

I had a great time in Slovenia, both during the event and in the after hours. Even if everything the tour guide said during the tour of the Laško brewery was lost on me (in his defense, he did offer to translate the Slovenian explanations to Russian), I still liked it – especially the part where we got to sample some of the produce!

I truly hope that there will be another Bleeding Edge conference next year. And if there is, I definitely want to speak there again!

Geek City: Build a Big Table with a Columnstore Index

Kalen Delaney — Thu, 26 Apr 2012 18:18:00 GMT

I was looking all over to find a big table to use for my columnstore examples in my SQL Server 2012 book, and the only one I found was from someone at Microsoft and was not publicly available. When I demonstrate code in my writings, I like to have the code available for anyone to use, so that was no use.

Finally I realized I was just going to have to do it myself. I actually based the script on some work by the awesome Benjamin Nevarez (blog| twitter), but I needed to make a couple of changes to the table structure, and then the loop for adding the data took some tweeking to make sure that uniqueness was possible, in case you need to test out how the metadata changes when a columnstore index is built on a table with a unique clustered index compared to a nonunique clustered index compared to a heap.

I have just finished the chapter on indexes, and decided to make this script available. The initial table data is based on the data in Microsoft AdventureWorksDW2012 sample database that you can download here. (Note that the DW version is the first one under “Other Available Downloads”’; it’s not the one under “Recommended Download”. )

Here is the section of the script that populates most of the FactInternetSalesBIG table. (The original 60K rows were just copied from the original FactInternetSales table. Note that all the statements have to be run in a single batch because the local variable’s scope is the batch, and because the GO 9 applies to the single batch that precedes it. Of course, you can change the number of iterations to end up with a different size table. Mine ends up at just over 30 million rows. In addition to modifying the SalesOrderNumber value on each iteration, I also changed the value of the Revision column to indicate which pass through the insert loop was being executed.

-- Copy the new big table into itself 9 times
DECLARE @RevisionNumber nchar(2);
SELECT @RevisionNumber = RevisionNumber + 1 FROM RevisionNumberValue;
SELECT @RevisionNumber as RevisionNumber;
INSERT INTO dbo.FactInternetSalesBig WITH (TABLOCK)
SELECT ProductKey
,OrderDateKey
,DueDateKey
,ShipDateKey
,CustomerKey
,PromotionKey
,CurrencyKey
,SalesTerritoryKey
,SalesOrderNumber + @RevisionNumber
,SalesOrderLineNumber
,@RevisionNumber
,OrderQuantity
,UnitPrice
,ExtendedAmount
,UnitPriceDiscountPct
,DiscountAmount
,ProductStandardCost
,TotalProductCost
,SalesAmount
,TaxAmt
,Freight
,CarrierTrackingNumber
,CustomerPONumber
,OrderDate
,DueDate
,ShipDate
FROM dbo.FactInternetSalesBig;
UPDATE RevisionNumberValue SET RevisionNumber = RevisionNumber + 1;
GO 9

Make sure you have enough log space. My log grew from 1GB to just over 8GB when running the script to build the 30 million row table and columnstore index, with no clustered index.

The downloadable script has commented options to build either a clustered or nonclustered index before you build the columnstore index. I suggest building the clustered index before the columnstore index, because building the clustered index will rebuild any existing nonclustered indexes, including my columnstore index. Building the columnstore index can take a while, on my system it was about 10 minutes. (Building the original 30+ million row table took even longer.)

After I finish the chapter on partitioning, I'll have a script to partition this table, but feel free to do that yourself!

Have fun!

~Kalen

Slides and demo code for Columnstore Index session

Hugo Kornelis — Wed, 04 Apr 2012 20:58:00 GMT

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.