{"id":998,"date":"2013-05-05T04:52:51","date_gmt":"2013-05-05T11:52:51","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/joe\/?p=998"},"modified":"2013-05-05T04:56:04","modified_gmt":"2013-05-05T11:56:04","slug":"exploring-columnstore-index-batch-sizes","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/joe\/exploring-columnstore-index-batch-sizes\/","title":{"rendered":"Exploring Columnstore Index Batch Sizes"},"content":{"rendered":"<p>In this blog post we\u2019ll explore columnstore index batch execution mode limits.\u00a0 I\u2019m using SQL Server 2012 Developer Edition, version 11.0.3349 (SP1, CU3).<\/p>\n<p>To start off with, I\u2019ll create a table that uses six different supported nonclustered columnstore index data types:<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nUSE &#x5B;CS];\r\nGO\r\n\r\nCREATE TABLE dbo.&#x5B;FactContrived]\r\n(\r\ncol00 TINYINT NOT NULL DEFAULT 255,\r\ncol01 SMALLINT NOT NULL DEFAULT 32767,\r\ncol02 INT NOT NULL DEFAULT 2147483647,\r\ncol03 BIGINT NOT NULL DEFAULT 9223372036854775807,\r\ncol04 SMALLMONEY NOT NULL DEFAULT 214748.3647,\r\ncol05 MONEY NOT NULL DEFAULT 922337203685477.5807);\r\nGO\r\n<\/pre>\n<p>I\u2019ll populate this table with 1,048,576 rows, all using the same value for each row (contrived by-design):<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nSET NOCOUNT ON;\r\nGO\r\n\r\nINSERT dbo.&#x5B;FactContrived]\r\nDEFAULT VALUES;\r\nGO\r\n\r\nINSERT dbo.&#x5B;FactContrived]\r\nSELECT\u00a0 &#x5B;col00],\r\n&#x5B;col01],\r\n&#x5B;col02],\r\n&#x5B;col03],\r\n&#x5B;col04],\r\n&#x5B;col05]\r\nFROM dbo.&#x5B;FactContrived];\r\nGO 20\r\n <\/pre>\n<p>Next I\u2019ll create a nonclustered columnstore index on each column in the table:<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nCREATE NONCLUSTERED COLUMNSTORE INDEX &#x5B;NCI_FactContrived]\r\nON dbo.&#x5B;FactContrived]\r\n(&#x5B;col00], &#x5B;col01], &#x5B;col02], &#x5B;col03], &#x5B;col04], &#x5B;col05] )\r\nWITH (MAXDOP = 1);\r\nGO\r\n<\/pre>\n<p>Once created, I\u2019ll check the page count statistics with the following query:<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nSELECT\u00a0 &#x5B;partition_id],\r\nSUM(&#x5B;in_row_used_page_count]) AS &#x5B;in_row_used_page_count],\r\nSUM(&#x5B;lob_used_page_count]) AS &#x5B;lob_used_page_count],\r\nSUM(&#x5B;row_count]) AS &#x5B;row_count]\r\nFROM\u00a0\u00a0\u00a0 sys.&#x5B;dm_db_partition_stats]\r\nWHERE\u00a0\u00a0 &#x5B;object_id] = OBJECT_ID('FactContrived') AND\r\n&#x5B;index_id] = INDEXPROPERTY\r\n(OBJECT_ID('FactContrived'),\r\n'NCI_FactContrived',\r\n'IndexId')\r\nGROUP BY &#x5B;partition_id];\r\nGO\r\n<\/pre>\n<p>This query returns the following results:<\/p>\n<p><a href=\"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-content\/uploads\/2013\/05\/image.png\"><img decoding=\"async\" style=\"background-image: none; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border: 0px;\" title=\"image\" alt=\"image\" src=\"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-content\/uploads\/2013\/05\/image_thumb.png\" width=\"564\" height=\"44\" border=\"0\" \/><\/a><\/p>\n<p>So as expected, the nonclustered columnstore index is stored within LOB pages (1,050 total).<\/p>\n<p>Next I\u2019ll check the segment metadata for the specific partition_id:<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\"> \r\nSELECT\r\n&#x5B;column_id],\r\n&#x5B;segment_id],\r\n&#x5B;encoding_type],\r\n&#x5B;row_count],\r\n&#x5B;primary_dictionary_id],\r\n&#x5B;secondary_dictionary_id],\r\n&#x5B;min_data_id],\r\n&#x5B;max_data_id],\r\n&#x5B;on_disk_size]\r\nFROM\u00a0\u00a0\u00a0 sys.&#x5B;column_store_segments]\r\nWHERE &#x5B;partition_id] = 72057594039762944;\r\n<\/pre>\n<p>This returns the following 7 rows:<\/p>\n<p><a href=\"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-content\/uploads\/2013\/05\/image1.png\"><img fetchpriority=\"high\" decoding=\"async\" style=\"background-image: none; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border: 0px;\" title=\"image\" alt=\"image\" src=\"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-content\/uploads\/2013\/05\/image_thumb1.png\" width=\"916\" height=\"141\" border=\"0\" \/><\/a><\/p>\n<p>Observations on this output:<\/p>\n<ul>\n<li>We have 7 columns instead of 6 that are defined for the index.\u00a0 Recall that there is no primary key or unique column for this table \u2013 and also look at column_id 7 and its associated on_disk_size and primary_dictionary_id of \u201c-1\u201d.<\/li>\n<li>Our data was duplicated across each column when we populated it and notice the min_data_id and max_data_id is identical for each column as expected (except column_id 7).<\/li>\n<\/ul>\n<p>Next I\u2019ll check the dictionary metadata:<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nSELECT\u00a0 &#x5B;column_id],\r\n&#x5B;dictionary_id],\r\n&#x5B;type],\r\n&#x5B;entry_count],\r\n&#x5B;on_disk_size]\r\nFROM\u00a0\u00a0\u00a0 sys.column_store_dictionaries\r\nWHERE &#x5B;partition_id] = 72057594039762944;\r\n<\/pre>\n<p>This returns the following 6 rows (no dictionary for our column_id 7):<\/p>\n<p><a href=\"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-content\/uploads\/2013\/05\/image2.png\"><img decoding=\"async\" style=\"background-image: none; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border: 0px;\" title=\"image\" alt=\"image\" src=\"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-content\/uploads\/2013\/05\/image_thumb2.png\" width=\"506\" height=\"157\" border=\"0\" \/><\/a><\/p>\n<p>Notice each dictionary just has one entry (since each column has just one unique value in the table across the 1,048,576 rows).\u00a0 Also notice the very small on_disk_size is bytes \u2013 and the dictionary type of \u201c1\u201d which according to BOL is a \u201cHash dictionary containing int values.\u201d<\/p>\n<p>Now I\u2019ll execute a query that leverages the columnstore index using batch execution mode:<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nSELECT\u00a0\u00a0\u00a0 &#x5B;col00],\r\nMAX(&#x5B;col00]) AS &#x5B;MaxCol00]\r\nFROM &#x5B;dbo].&#x5B;FactContrived]\r\nGROUP BY &#x5B;col00]\r\nOPTION (RECOMPILE);\r\nGO\r\n<\/pre>\n<p>The plan (via <a href=\"http:\/\/www.sqlsentry.net\/plan-explorer\/sql-server-query-view.asp\" target=\"_blank\">SQL Sentry Plan Explorer<\/a>) is as follows:<\/p>\n<p><a href=\"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-content\/uploads\/2013\/05\/image3.png\"><img loading=\"lazy\" decoding=\"async\" style=\"background-image: none; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border: 0px;\" title=\"image\" alt=\"image\" src=\"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-content\/uploads\/2013\/05\/image_thumb3.png\" width=\"752\" height=\"117\" border=\"0\" \/><\/a><\/p>\n<p>The Plan Tree tab shows that batch execution mode was used for the Columnstore Index Scan and the Hash Match (Partial Aggregate) operators:<\/p>\n<p><a href=\"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-content\/uploads\/2013\/05\/image4.png\"><img loading=\"lazy\" decoding=\"async\" style=\"background-image: none; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border: 0px;\" title=\"image\" alt=\"image\" src=\"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-content\/uploads\/2013\/05\/image_thumb4.png\" width=\"755\" height=\"114\" border=\"0\" \/><\/a><\/p>\n<p>As for thread distribution, while the plan had a degree of parallelism of 8 and and one branch with 8 reserved threads, only one thread has rows associated with it:<\/p>\n<p><a href=\"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-content\/uploads\/2013\/05\/image5.png\"><img loading=\"lazy\" decoding=\"async\" style=\"background-image: none; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border: 0px;\" title=\"image\" alt=\"image\" src=\"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-content\/uploads\/2013\/05\/image_thumb5.png\" width=\"677\" height=\"167\" border=\"0\" \/><\/a><\/p>\n<p>Now regarding the actual number of batches, we see the following:<\/p>\n<p><a href=\"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-content\/uploads\/2013\/05\/image6.png\"><img loading=\"lazy\" decoding=\"async\" style=\"background-image: none; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border: 0px;\" title=\"image\" alt=\"image\" src=\"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-content\/uploads\/2013\/05\/image_thumb6.png\" width=\"366\" height=\"203\" border=\"0\" \/><\/a><\/p>\n<p>So with 1,166 batches of rows across 1,048,576 rows, we\u2019re looking at an average batch size of 899.29 rows.<\/p>\n<p>Experimenting with referencing the other columns of this table, we\u2019ll see the same number of batches each time (I\u2019ll spare you the repeated results).<\/p>\n<p>Now when columnstore indexes were first discussed a year or so ago, you would see references to batch sizes of approximately 1,000 rows.\u00a0 For example, the SQL Server Columnstore Index FAQ talks about how a batch \u201c<a href=\"http:\/\/social.technet.microsoft.com\/wiki\/contents\/articles\/3540.sql-server-columnstore-index-faq.aspx#Batch_mode_processing\" target=\"_blank\" class=\"broken_link\">typically represents about 1000 rows of data<\/a>.\u201d\u00a0 Then a few months after SQL Server 2012 I recall seeing references to it being approximately 900 rows.\u00a0 The results of this particular example show we\u2019re closer to that 900 row value.<\/p>\n<p>Now for contrast \u2013 I went ahead and dropped the nonclustered columnstore index, truncated the table, and repopulated it with random values instead of using one unique value per column.\u00a0 Below is a revisiting of the metadata for the new data distributions after adding back a nonclustered columnstore index\u2026<\/p>\n<p><strong>Partition Stats<\/strong><\/p>\n<p><a href=\"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-content\/uploads\/2013\/05\/image7.png\"><img loading=\"lazy\" decoding=\"async\" style=\"background-image: none; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border: 0px;\" title=\"image\" alt=\"image\" src=\"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-content\/uploads\/2013\/05\/image_thumb7.png\" width=\"582\" height=\"45\" border=\"0\" \/><\/a><\/p>\n<p>Notice we have more LOB pages for the random data vs. the uniform data \u2013 as expected.<\/p>\n<p><strong>Segment Metadata<\/strong><\/p>\n<p><a href=\"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-content\/uploads\/2013\/05\/image8.png\"><img loading=\"lazy\" decoding=\"async\" style=\"background-image: none; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border: 0px;\" title=\"image\" alt=\"image\" src=\"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-content\/uploads\/2013\/05\/image_thumb8.png\" width=\"927\" height=\"135\" border=\"0\" \/><\/a><\/p>\n<p>Notice the higher on_disk_size and the varying min\/max data ranges per segment.\u00a0 Also notice that with the random, more unique data, there only one of the columns (our tinyint column) has a primary_dictionary.<\/p>\n<p><strong>Dictionary Metadata<\/strong><\/p>\n<p><a href=\"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-content\/uploads\/2013\/05\/image9.png\"><img loading=\"lazy\" decoding=\"async\" style=\"background-image: none; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border: 0px;\" title=\"image\" alt=\"image\" src=\"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-content\/uploads\/2013\/05\/image_thumb9.png\" width=\"573\" height=\"49\" border=\"0\" \/><\/a><\/p>\n<p>And we see one dictionary entry for our tinyint column.<\/p>\n<p>Revisiting our original test query, the plan shape changes somewhat (additional Parallel Gather Streams), given that we now actually have 256 rows returned based on the tinyint column:<\/p>\n<p><a href=\"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-content\/uploads\/2013\/05\/image10.png\"><img loading=\"lazy\" decoding=\"async\" style=\"background-image: none; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border: 0px;\" title=\"image\" alt=\"image\" src=\"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-content\/uploads\/2013\/05\/image_thumb10.png\" width=\"788\" height=\"109\" border=\"0\" \/><\/a><\/p>\n<p>As for row distribution across threads, we still see one thread handling the batch-mode operations and then a spread of the row-mode rows across the other operators:<\/p>\n<p><a href=\"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-content\/uploads\/2013\/05\/image11.png\"><img loading=\"lazy\" decoding=\"async\" style=\"background-image: none; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border: 0px;\" title=\"image\" alt=\"image\" src=\"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-content\/uploads\/2013\/05\/image_thumb11.png\" width=\"746\" height=\"155\" border=\"0\" \/><\/a><\/p>\n<p>Now as for the number of batches, we see the following:<\/p>\n<p><a href=\"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-content\/uploads\/2013\/05\/image12.png\"><img loading=\"lazy\" decoding=\"async\" style=\"background-image: none; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border: 0px;\" title=\"image\" alt=\"image\" src=\"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-content\/uploads\/2013\/05\/image_thumb12.png\" width=\"385\" height=\"214\" border=\"0\" \/><\/a><\/p>\n<p>So 1,280 batches over 1,048,576 rows.\u00a0 Averaging 819.2<strong> <\/strong>rows per batch, vs. our previous test\u2019s 899.29 rows.<\/p>\n<p>There is more to explore on this subject \u2013 but that\u2019s all for today as I\u2019m about to jump on a plane to Chicago to help deliver <a href=\"https:\/\/www.sqlskills.com\/sql-server-training\/ie2\/\" target=\"_blank\">IE2: Immersion Event on Performance Tuning<\/a>.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>In this blog post we\u2019ll explore columnstore index batch execution mode limits.\u00a0 I\u2019m using SQL Server 2012 Developer Edition, version 11.0.3349 (SP1, CU3). To start off with, I\u2019ll create a table that uses six different supported nonclustered columnstore index data types: USE &#x5B;CS]; GO CREATE TABLE dbo.&#x5B;FactContrived] ( col00 TINYINT NOT NULL DEFAULT 255, col01 [&hellip;]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[16],"tags":[],"class_list":["post-998","post","type-post","status-publish","format-standard","hentry","category-columnstore-indexes"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v21.9.1 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Exploring Columnstore Index Batch Sizes - Joe Sack<\/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\/joe\/exploring-columnstore-index-batch-sizes\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Exploring Columnstore Index Batch Sizes - Joe Sack\" \/>\n<meta property=\"og:description\" content=\"In this blog post we\u2019ll explore columnstore index batch execution mode limits.\u00a0 I\u2019m using SQL Server 2012 Developer Edition, version 11.0.3349 (SP1, CU3). To start off with, I\u2019ll create a table that uses six different supported nonclustered columnstore index data types: USE &#x5B;CS]; GO CREATE TABLE dbo.&#x5B;FactContrived] ( col00 TINYINT NOT NULL DEFAULT 255, col01 [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/joe\/exploring-columnstore-index-batch-sizes\/\" \/>\n<meta property=\"og:site_name\" content=\"Joe Sack\" \/>\n<meta property=\"article:published_time\" content=\"2013-05-05T11:52:51+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2013-05-05T11:56:04+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-content\/uploads\/2013\/05\/image_thumb.png\" \/>\n<meta name=\"author\" content=\"Joseph Sack\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Joseph Sack\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"5 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/exploring-columnstore-index-batch-sizes\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/exploring-columnstore-index-batch-sizes\/\",\"name\":\"Exploring Columnstore Index Batch Sizes - Joe Sack\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/#website\"},\"datePublished\":\"2013-05-05T11:52:51+00:00\",\"dateModified\":\"2013-05-05T11:56:04+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/533eb0113a15fb5a6e8067a49e4ae648\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/exploring-columnstore-index-batch-sizes\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/joe\/exploring-columnstore-index-batch-sizes\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/exploring-columnstore-index-batch-sizes\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Columnstore indexes\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/category\/columnstore-indexes\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"Exploring Columnstore Index Batch Sizes\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/\",\"name\":\"Joe Sack\",\"description\":\"SQL Server Performance Tuning, High Availability and Disaster Recovery Blog\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/533eb0113a15fb5a6e8067a49e4ae648\",\"name\":\"Joseph Sack\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/a4b39a7719a6bfff1add3ec00527810734579ee114d6d983e8e68f937b77be96?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/a4b39a7719a6bfff1add3ec00527810734579ee114d6d983e8e68f937b77be96?s=96&d=mm&r=g\",\"caption\":\"Joseph Sack\"},\"description\":\"Joe Sack is a Principal Consultant with SQLskills. He has worked as a SQL Server professional since 1997 and has supported and developed for SQL Server environments in financial services, IT consulting, manufacturing, retail and the real estate industry. Prior to joining SQLskills he worked at Microsoft as a Premier Field Engineer supporting very large enterprise customer environments. He was responsible for providing deep SQL Server advisory services, training, troubleshooting and ongoing solutions guidance. His areas of expertise include performance tuning, scalability, T-SQL development and high-availability. In 2006 Joe earned the \u201cMicrosoft Certified Master: SQL Server 2005\u201d certification and in 2008 he earned the \u201cMicrosoft Certified Master: SQL Server 2008\u201d certification. In 2009 he took over responsibility for the entire SQL Server Microsoft Certified Master program and held that post until 2011. He was given the SQL Server MVP award in 2013.\",\"sameAs\":[\"http:\/\/3.209.169.194\/blogs\/joe\",\"https:\/\/twitter.com\/https:\/\/twitter.com\/josephsack\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/author\/joe\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Exploring Columnstore Index Batch Sizes - Joe Sack","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\/joe\/exploring-columnstore-index-batch-sizes\/","og_locale":"en_US","og_type":"article","og_title":"Exploring Columnstore Index Batch Sizes - Joe Sack","og_description":"In this blog post we\u2019ll explore columnstore index batch execution mode limits.\u00a0 I\u2019m using SQL Server 2012 Developer Edition, version 11.0.3349 (SP1, CU3). To start off with, I\u2019ll create a table that uses six different supported nonclustered columnstore index data types: USE &#x5B;CS]; GO CREATE TABLE dbo.&#x5B;FactContrived] ( col00 TINYINT NOT NULL DEFAULT 255, col01 [&hellip;]","og_url":"https:\/\/www.sqlskills.com\/blogs\/joe\/exploring-columnstore-index-batch-sizes\/","og_site_name":"Joe Sack","article_published_time":"2013-05-05T11:52:51+00:00","article_modified_time":"2013-05-05T11:56:04+00:00","og_image":[{"url":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-content\/uploads\/2013\/05\/image_thumb.png"}],"author":"Joseph Sack","twitter_misc":{"Written by":"Joseph Sack","Est. reading time":"5 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/exploring-columnstore-index-batch-sizes\/","url":"https:\/\/www.sqlskills.com\/blogs\/joe\/exploring-columnstore-index-batch-sizes\/","name":"Exploring Columnstore Index Batch Sizes - Joe Sack","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/#website"},"datePublished":"2013-05-05T11:52:51+00:00","dateModified":"2013-05-05T11:56:04+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/533eb0113a15fb5a6e8067a49e4ae648"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/exploring-columnstore-index-batch-sizes\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/joe\/exploring-columnstore-index-batch-sizes\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/exploring-columnstore-index-batch-sizes\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/joe\/"},{"@type":"ListItem","position":2,"name":"Columnstore indexes","item":"https:\/\/www.sqlskills.com\/blogs\/joe\/category\/columnstore-indexes\/"},{"@type":"ListItem","position":3,"name":"Exploring Columnstore Index Batch Sizes"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/joe\/","name":"Joe Sack","description":"SQL Server Performance Tuning, High Availability and Disaster Recovery Blog","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/joe\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/533eb0113a15fb5a6e8067a49e4ae648","name":"Joseph Sack","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/a4b39a7719a6bfff1add3ec00527810734579ee114d6d983e8e68f937b77be96?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/a4b39a7719a6bfff1add3ec00527810734579ee114d6d983e8e68f937b77be96?s=96&d=mm&r=g","caption":"Joseph Sack"},"description":"Joe Sack is a Principal Consultant with SQLskills. He has worked as a SQL Server professional since 1997 and has supported and developed for SQL Server environments in financial services, IT consulting, manufacturing, retail and the real estate industry. Prior to joining SQLskills he worked at Microsoft as a Premier Field Engineer supporting very large enterprise customer environments. He was responsible for providing deep SQL Server advisory services, training, troubleshooting and ongoing solutions guidance. His areas of expertise include performance tuning, scalability, T-SQL development and high-availability. In 2006 Joe earned the \u201cMicrosoft Certified Master: SQL Server 2005\u201d certification and in 2008 he earned the \u201cMicrosoft Certified Master: SQL Server 2008\u201d certification. In 2009 he took over responsibility for the entire SQL Server Microsoft Certified Master program and held that post until 2011. He was given the SQL Server MVP award in 2013.","sameAs":["http:\/\/3.209.169.194\/blogs\/joe","https:\/\/twitter.com\/https:\/\/twitter.com\/josephsack"],"url":"https:\/\/www.sqlskills.com\/blogs\/joe\/author\/joe\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/posts\/998","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/comments?post=998"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/posts\/998\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/media?parent=998"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/categories?post=998"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/tags?post=998"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}