Live blogging again; hope you find my notes useful (scattered though they are). I’ve been waiting on this session because it’s a very specific area of interest. I work a lot with VLDB’s, and performance is always a concern; claims are that Denali’s columnstore may boost performance of certain queries hundred-fold. Let’s see how they work, and I’m hoping I can convince my boss to set up a test bed to try this out.
Presenter is Eric N Hanson from Microsoft (Twitter).
We start off with a story; I like story-time. Actually, it’s a very effective way to break out user cases.
Buzzphrase for Columnstore: “Enabling interaction with data”. Supposed to be super efficient, and get large amounts of data back from SQL Server Denali. Internal project name is Apollo; columnstore is only part of the picture.
Area of focus is BI & DW: load large amounts of data, high-read, incremental loads. Partitioning is mandatory for this feature.
Curious as to why the examples join tables in the WHERE clause, and not the more accepted syntax of JOIN.
K, here comes the magic: example uses a Fact Table with 100 million rows in it. Clustered on a date column, and a columnstore index. Clustered index is still B-TREE; columnstore indexes are nonclustered.
Running duplicate queries; using index hint to force optimizer to use the clustered index in one example. Wow; 100,000,000 rows of data aggregated in a second on a two-year old laptop. 50x speedup on this particular hardware. According to presenter: “this is the biggest enhancement to SQL Server since we bought the code from Sybase".
And here’s the meat and potatoes; how does this work? Vertical partitioning stores each column in a separate page. Columnstore is based on the same code as PowerPivot and the BI engine; Vertipaq if you want to do more reading on this. Columnstore data is highly compressed, so smaller footprint to read from disk and can be stored in main memory.
New query execution plan: batch processing. “the edsel is the way of the future”. Actually, the idea is that batches of vectors are stored in query plan; highly efficient data representation. We can also scale to more cores: tests are showing linear acceleration up to 32 cores.
Instead of storing data as a page, data is stored as a column segment which represents about 1,000,000 rows.
Questions have begun; some questions are good, but this is a 300 level session, folks. If you don’t understand basic SQL syntax (like how to create an index), this may not be the session for you. Great question about the relevance of traditional indexes after this is unveiled, and Hanson’s response: in most Decision Support Applications, columnstore is the way to go particularly for scans.
Some index hints for choosing the columnstore or ignoring it:
WITH (index(index_name))
OPTION (ignore_nonclustered_columnstore_index) <—use for bad plan selection if necessary.
Same traditional rules for index hints: trust the optimizer first, rewrite second, and then use hints last.
A couple of new icons for query execution plans: columnstore scan, and batch hash table processing. Each execution operator now operates in either batch mode or row mode; batch mode is what you want for speed.
New term of interest: dictionary. A dictionary is storage for unique values with a lookup so that a column can stores highly compressed information.
Most things just work with SQL Server; Backup and Restore, Mirroring, SSMS, etc.
Lots of datatypes don’t work with column store: long decimals, binary, BLOB, uniqueidentifier, long datetimes, CLR, (n)varchar(max).
query performance restrictions: outer joins, Unions; Stick with Inner Joins, Star Joins (need to look this one up) Aggregation. About to show a query which doesn’t benefit from batch processing. Essence is below:
SELECT t.ID, COUNT(t2.ID)
FROM t LEFT JOIN t2 ON t.ID=t2.ID
GROUP BY t.ID
Left Join knocks it out of batch processing; need to rewrite as an INNER JOIN, but note that you lose the NULL values, so you have to use a CTE; need to get slides for his sample, but you do an INNER JOIN in the CTE, and then do an OUTER JOIN.
WITH CTE( INNER JOIN)
SELECT blah
FROM t OUTER JOIN CTE ON t.ID yada yada.
Adding data to columnstore; basic methods:
1. Drop and re-add the index before load. Expensive, but works well with traditional daily builds
2. Partition switching. Sweet spot needs to be tested, but easy one is the hour. NOLOCK queries pre-empt the ability to do paritioned queries. Need to read up on this, but may be fixed in future version
3 trickle load can be done, but needs to be tested.
Very awesome; I cannot wait until this is actually released in CTP 3, so I can play around with it.