{"id":1736,"date":"2013-08-13T16:18:00","date_gmt":"2013-08-13T23:18:00","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/bobb\/?p=1736"},"modified":"2013-08-13T17:11:35","modified_gmt":"2013-08-14T00:11:35","slug":"columnstore-indexes-and-query-plans-in-sql-server-2014-ctp1","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/bobb\/columnstore-indexes-and-query-plans-in-sql-server-2014-ctp1\/","title":{"rendered":"Columnstore Indexes and Query Plans in SQL Server 2014 CTP1"},"content":{"rendered":"

Today I thought I’d try out some of the new, highly-touted columnstore\u00a0improvements in SQL Server 2014. First off, to figure out what’s new I used three main sources: one is the “canon”, the whitepaper, “Enhancements to SQL Server Column Stores<\/a>“, presented at Sigmod 2013. The second source is the SQL Server 2014 CTP1 Books Online page, that documents<\/a> and illustrates the DDL to define<\/a> and work with<\/a> clustered (and nonclustered) columnstore indexes. The\u00a0third source is Niko Neugebauer’s excellent blog post series and experiments on Clustered Columnstore Indexes, that starts here<\/a>.<\/p>\n

Rather than repeat what those folks have already implemented, documented,\u00a0and experimented with respectively, I went off on a different tangent. I’d like to find out how the new clustered columnstore index affects query plans, and look for changes in queries between SQL Server 2012 and 2014 CTP1. I started by restoring AdventureWorks2014 to a SQL Server 2014 instance and a SQL Server 2012 SP1 instance. And taking the FactInternetSales table up to over a million (about 1.15 million) rows using a combination of select…into and insert…select.<\/p>\n

On the SQL Server 2012 instance a made in copy of the table without a nonclustered columnstore index and one with a nonclustered columnstore index. The nonclustered columnstore index contained all of the columns in the table. SQL Server 2014 had a third copy of this table, one that used a clustered columnstore index.<\/p>\n

With each table (named FactInternetSalesNew, FactInternetSalesNewNCI, and FactInternetSalesNewCCI so I could keep trace of them), I did the relevant variation of the following query:<\/p>\n

select ProductAlternateKey, CalendarYear, SUM(SalesAmount) as SumSales
\nfrom FactInternetSalesNew fact
\njoin DimDate on fact.OrderDateKey = DateKey
\njoin DimProduct on DimProduct.ProductKey = fact.ProductKey
\nwhere CalendarYear BETWEEN 2006 AND 2007
\nand ProductAlternateKey LIKE ‘BK%’
\ngroup by ProductAlternateKey, CalendarYear;<\/p>\n

The query plans were interesting is their differences and similarities.<\/p>\n

1.The “traditional” row-based storage used a HashJoin with a Bitmap for one dimension (DimDate) and a MergeJoin to join in the Product dimension. This was the same in SQL Server 2012 SP1 and 2014. This is a different\u00a0plan shape than any of the columnstore plans, with the dimensions being joined in\u00a0a different order and the aggregation happening at\u00a0a different point in the plan.
\n2.The SQL Server 2012 nonclustered columnstore index used BatchHashTableBuild iterators to build hash tables for each dimension, then HashJoins for the join with each dimension. The scan of the fact table, BatchHashTableBuilds and HashJoins, and a HashMatch (partial aggregation) iterator after the joins used Batch Mode; the rest of the iterators used Row mode.
\n3. The SQL Server 2014 nonclustered columnstore index did NOT use BatchHashTableBuild iterators, instead the join between the Fact table and DateDim used a HashMatch. However, the HashMatch iterator did contain the new (for 2014) property “BitmapCreator=True”. The HashMatch iterator to join in the Product dimension did use a HashMatch, but the BitmapCreator property was not present. All iterators EXCEPT the Exchange, DimensionTable scans, and the last HashMatch (aggregate) used Batch Mode.
\n4. The SQL Server 2014 clustered columnstore index plan was essentially the same as the nonclustered columnstore index plan.<\/p>\n

For clarity’s sake, I’ll continue the series and see how the cardinality estimation works with columnstore indexes in the next post.
\nFor expediency’s sake, the plans are posted as an attachment. I’ll hopefully get back to replace them with pictures soon.<\/p>\n

So, to summarize:
\n1. The query plan for nonclustered columnstore index changes for the example query between SQL Server 2012 SP1 and 2014 CTP1. All experiments are performed on my 2-proc VM.
\n2. In SQL Server 2014 CTP1, the plan for nonclustered and clustered columnstore indexes are the same. The savings of the clustered columnstore index is that you don’t have to store your actual data twice.<\/p>\n

Cheers, Bob<\/p>\n

query_plans.zip<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"

Today I thought I’d try out some of the new, highly-touted columnstore\u00a0improvements in SQL Server 2014. First off, to figure out what’s new I used three main sources: one is the “canon”, the whitepaper, “Enhancements to SQL Server Column Stores“, presented at Sigmod 2013. The second source is the SQL Server 2014 CTP1 Books Online […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[43,41],"tags":[],"class_list":["post-1736","post","type-post","status-publish","format-standard","hentry","category-columnstore-indexes","category-sql-server-2014"],"yoast_head":"\nColumnstore Indexes and Query Plans in SQL Server 2014 CTP1 - Bob Beauchemin<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.sqlskills.com\/blogs\/bobb\/columnstore-indexes-and-query-plans-in-sql-server-2014-ctp1\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Columnstore Indexes and Query Plans in SQL Server 2014 CTP1 - Bob Beauchemin\" \/>\n<meta property=\"og:description\" content=\"Today I thought I’d try out some of the new, highly-touted columnstore\u00a0improvements in SQL Server 2014. First off, to figure out what’s new I used three main sources: one is the “canon”, the whitepaper, “Enhancements to SQL Server Column Stores“, presented at Sigmod 2013. The second source is the SQL Server 2014 CTP1 Books Online […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/bobb\/columnstore-indexes-and-query-plans-in-sql-server-2014-ctp1\/\" \/>\n<meta property=\"og:site_name\" content=\"Bob Beauchemin\" \/>\n<meta property=\"article:published_time\" content=\"2013-08-13T23:18:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2013-08-14T00:11:35+00:00\" \/>\n<meta name=\"author\" content=\"Bob Beauchemin\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Bob Beauchemin\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/columnstore-indexes-and-query-plans-in-sql-server-2014-ctp1\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/columnstore-indexes-and-query-plans-in-sql-server-2014-ctp1\/\",\"name\":\"Columnstore Indexes and Query Plans in SQL Server 2014 CTP1 - Bob Beauchemin\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website\"},\"datePublished\":\"2013-08-13T23:18:00+00:00\",\"dateModified\":\"2013-08-14T00:11:35+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/columnstore-indexes-and-query-plans-in-sql-server-2014-ctp1\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/bobb\/columnstore-indexes-and-query-plans-in-sql-server-2014-ctp1\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/columnstore-indexes-and-query-plans-in-sql-server-2014-ctp1\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Columnstore Indexes\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/category\/columnstore-indexes\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"Columnstore Indexes and Query Plans in SQL Server 2014 CTP1\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/\",\"name\":\"Bob Beauchemin\",\"description\":\"SQL Server Blog\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e\",\"name\":\"Bob Beauchemin\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/6f80e6cc667410857fa6a21931dc528b8092f4d112bf7a8ff7c267674d44ee37?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/6f80e6cc667410857fa6a21931dc528b8092f4d112bf7a8ff7c267674d44ee37?s=96&d=mm&r=g\",\"caption\":\"Bob Beauchemin\"},\"sameAs\":[\"http:\/www.sqlskills.com\/blogs\/bobb\/\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/author\/bobb\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Columnstore Indexes and Query Plans in SQL Server 2014 CTP1 - Bob Beauchemin","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.sqlskills.com\/blogs\/bobb\/columnstore-indexes-and-query-plans-in-sql-server-2014-ctp1\/","og_locale":"en_US","og_type":"article","og_title":"Columnstore Indexes and Query Plans in SQL Server 2014 CTP1 - Bob Beauchemin","og_description":"Today I thought I’d try out some of the new, highly-touted columnstore\u00a0improvements in SQL Server 2014. First off, to figure out what’s new I used three main sources: one is the “canon”, the whitepaper, “Enhancements to SQL Server Column Stores“, presented at Sigmod 2013. The second source is the SQL Server 2014 CTP1 Books Online […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/columnstore-indexes-and-query-plans-in-sql-server-2014-ctp1\/","og_site_name":"Bob Beauchemin","article_published_time":"2013-08-13T23:18:00+00:00","article_modified_time":"2013-08-14T00:11:35+00:00","author":"Bob Beauchemin","twitter_misc":{"Written by":"Bob Beauchemin","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/columnstore-indexes-and-query-plans-in-sql-server-2014-ctp1\/","url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/columnstore-indexes-and-query-plans-in-sql-server-2014-ctp1\/","name":"Columnstore Indexes and Query Plans in SQL Server 2014 CTP1 - Bob Beauchemin","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website"},"datePublished":"2013-08-13T23:18:00+00:00","dateModified":"2013-08-14T00:11:35+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/columnstore-indexes-and-query-plans-in-sql-server-2014-ctp1\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/bobb\/columnstore-indexes-and-query-plans-in-sql-server-2014-ctp1\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/columnstore-indexes-and-query-plans-in-sql-server-2014-ctp1\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/bobb\/"},{"@type":"ListItem","position":2,"name":"Columnstore Indexes","item":"https:\/\/www.sqlskills.com\/blogs\/bobb\/category\/columnstore-indexes\/"},{"@type":"ListItem","position":3,"name":"Columnstore Indexes and Query Plans in SQL Server 2014 CTP1"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/","name":"Bob Beauchemin","description":"SQL Server Blog","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/bobb\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e","name":"Bob Beauchemin","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/6f80e6cc667410857fa6a21931dc528b8092f4d112bf7a8ff7c267674d44ee37?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/6f80e6cc667410857fa6a21931dc528b8092f4d112bf7a8ff7c267674d44ee37?s=96&d=mm&r=g","caption":"Bob Beauchemin"},"sameAs":["http:\/www.sqlskills.com\/blogs\/bobb\/"],"url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/author\/bobb\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/posts\/1736","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/comments?post=1736"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/posts\/1736\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/media?parent=1736"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/categories?post=1736"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/tags?post=1736"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}