{"id":3525,"date":"2014-01-30T07:35:00","date_gmt":"2014-01-30T06:35:00","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/sql-server-2014-rebuilding-clustered-columnstore-indexes-1\/"},"modified":"2014-01-30T07:35:00","modified_gmt":"2014-01-30T06:35:00","slug":"sql-server-2014-rebuilding-clustered-columnstore-indexes-1","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/sql-server-2014-rebuilding-clustered-columnstore-indexes-1\/","title":{"rendered":"SQL Server 2014: Rebuilding Clustered Columnstore Indexes"},"content":{"rendered":"<p>I was surprised that you have to rebuild indexes that are stored In Memory. My previous posting <a href=\"\/sql-server-2014-new-features-xvelocity-memory-optimized-columnstore-index\">&#8220;SQL Server 2014 &#8211; New Features: xVelocity memory optimized columnstore index&#8221;<\/a> explains all processes connected with columnstore indexes. In this article, I will explain why, when, and how to rebuild them.<\/p>\n<h3>Why do we need to rebuild the Clustered Columnstore Indexes?<\/h3>\n<p>When a row is deleted, SQL Server marks the row as logically deleted. Physically, the row is still there.<br \/>\nThis is true for the columnstore, but if the row is in the deltastore, the row is directly logically and physically deleted.<br \/>\nIn addition to the DELETE query, an UPDATE query is a deletion followed by an insert.<br \/>\nWe can easily consider that a lot of rows are marked as deleted during the lifetime of a database. The next question is: How can we determine when a rebuild must be started?<\/p>\n<h3>The traditional question: When and How?<\/h3>\n<p>A few specifically interesting Dynamic Management Views (DMVs) are delivered with SQL Server 2014 for Clustered Columnstore Indexes.These views help us understand what is happening in this new feature.<\/p>\n<p>The most important DMV is sys.column_store_row_groups. This view has two interesting columns:<\/p>\n<ul>\n<li><strong>total_rows<\/strong> is the total number of rows stored<\/li>\n<li><strong>deleted_rows<\/strong> is the total number of rows marked as deleted<\/li>\n<\/ul>\n<p>You can find more information <a href=\"http:\/\/msdn.microsoft.com\/en-us\/library\/dn223749(v=sql.120).aspx\">here<\/a>.<br \/>\nA comparison between these 2 columns gives us an idea of when to rebuild a Clustered Columnstore Index.<br \/>\nWhen the number of deleted rows increases and represents a large percentage of the total rows, performance problems can occur on the table.<br \/>\nTo reduce the size of the table and of course reduce the disk I\/O during a read, the best way is to really delete all rows marked as deleted.<br \/>\nThen, rebuilding the index is required.<br \/>\nThe following example will help you understand this problem.<\/p>\n<h3>Rebuilding a Clustered Columnstore Index<\/h3>\n<p>I have created the Clustered Columnstore Index CCSI_Person_Person on Person&#8217;s table from the AdventureWorks database:<\/p>\n<p><a class=\"easyblog-thumb-preview\" title=\"Rebuild01.jpg\" href=\"http:\/\/dbi-services.com\/blog\/images\/easyblog_images\/88\/Rebuild01.jpg\"><img decoding=\"async\" title=\"b2ap3_thumbnail_Rebuild01.jpg\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/b2ap3_thumbnail_Rebuild01.jpg\" alt=\"b2ap3_thumbnail_Rebuild01.jpg\" \/><\/a><\/p>\n<p>My first action is an update from a title for all rows with an &#8220;e&#8221;:<\/p>\n<p><a class=\"easyblog-thumb-preview\" title=\"Rebuild02.jpg\" href=\"http:\/\/dbi-services.com\/blog\/images\/easyblog_images\/88\/Rebuild02.jpg\"><img decoding=\"async\" title=\"b2ap3_thumbnail_Rebuild02.jpg\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/b2ap3_thumbnail_Rebuild02.jpg\" alt=\"b2ap3_thumbnail_Rebuild02.jpg\" \/><\/a><\/p>\n<p>10446 rows are updated.<br \/>\nMy second action is a delete from same rows, and such as for the update, I have 10446 rows in the column delete_rows:<\/p>\n<p><a class=\"easyblog-thumb-preview\" title=\"Rebuild04.jpg\" href=\"http:\/\/dbi-services.com\/blog\/images\/easyblog_images\/88\/Rebuild04.jpg\"><img decoding=\"async\" title=\"b2ap3_thumbnail_Rebuild04.jpg\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/b2ap3_thumbnail_Rebuild04.jpg\" alt=\"b2ap3_thumbnail_Rebuild04.jpg\" \/><\/a><\/p>\n<p>Afterwards, I change my clause WHERE and I delete all rows with an &#8220;a&#8221;:<\/p>\n<p><a class=\"easyblog-thumb-preview\" title=\"Rebuild05.jpg\" href=\"http:\/\/dbi-services.com\/blog\/images\/easyblog_images\/88\/Rebuild05.jpg\"><img decoding=\"async\" title=\"b2ap3_thumbnail_Rebuild05.jpg\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/b2ap3_thumbnail_Rebuild05.jpg\" alt=\"b2ap3_thumbnail_Rebuild05.jpg\" \/><\/a><\/p>\n<p>18269 rows are marked as deleted.<br \/>\nNow, using a Select command and retrieve a total count of 1703 rows.<\/p>\n<p><a class=\"easyblog-thumb-preview\" title=\"Rebuild06.jpg\" href=\"http:\/\/dbi-services.com\/blog\/images\/easyblog_images\/88\/Rebuild06.jpg\"><img decoding=\"async\" title=\"b2ap3_thumbnail_Rebuild06.jpg\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/b2ap3_thumbnail_Rebuild06.jpg\" alt=\"b2ap3_thumbnail_Rebuild06.jpg\" \/><\/a><\/p>\n<p>With sys.column_store_row_groups and a simple calculation, I have 91% of rows marked as deleted, kind of phantom rows&#8230;<\/p>\n<p><a class=\"easyblog-thumb-preview\" title=\"Rebuild07.jpg\" href=\"http:\/\/dbi-services.com\/blog\/images\/easyblog_images\/88\/Rebuild07.jpg\"><img decoding=\"async\" title=\"b2ap3_thumbnail_Rebuild07.jpg\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/b2ap3_thumbnail_Rebuild07.jpg\" alt=\"b2ap3_thumbnail_Rebuild07.jpg\" \/><\/a><\/p>\n<p>If I open the Index properties, no rebuild operation is needed as the index fragmentation is at 0%.<br \/>\nThis is good, I do not perform a rebuild because I have fragmentation.<br \/>\nColumnstore index is not a row-store index type and is stored in memory. Do not forget that!:roll:<\/p>\n<p><a class=\"easyblog-thumb-preview\" title=\"Rebuild03.jpg\" href=\"http:\/\/dbi-services.com\/blog\/images\/easyblog_images\/88\/Rebuild03.jpg\"><img decoding=\"async\" title=\"b2ap3_thumbnail_Rebuild03.jpg\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/b2ap3_thumbnail_Rebuild03.jpg\" alt=\"b2ap3_thumbnail_Rebuild03.jpg\" \/><\/a><\/p>\n<p>You can directly\u00a0rebuild with a right-click on the index:<\/p>\n<p><a class=\"easyblog-thumb-preview\" title=\"Rebuild08.jpg\" href=\"http:\/\/dbi-services.com\/blog\/images\/easyblog_images\/88\/Rebuild08.jpg\"><img decoding=\"async\" title=\"b2ap3_thumbnail_Rebuild08.jpg\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/b2ap3_thumbnail_Rebuild08.jpg\" alt=\"b2ap3_thumbnail_Rebuild08.jpg\" \/><\/a><\/p>\n<p>Or also via T-SQL script: ALTER INDEX CCSI_Person_Person REBUILD PARTITION = ALL<br \/>\nUsing my script, I will add two options: ONLINE and DROP_EXISTING<\/p>\n<p><a class=\"easyblog-thumb-preview\" title=\"Rebuild09.jpg\" href=\"http:\/\/dbi-services.com\/blog\/images\/easyblog_images\/88\/Rebuild09.jpg\"><img decoding=\"async\" title=\"b2ap3_thumbnail_Rebuild09.jpg\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/b2ap3_thumbnail_Rebuild09.jpg\" alt=\"b2ap3_thumbnail_Rebuild09.jpg\" \/><\/a><\/p>\n<p>DROP_EXISTING is not possible:-|!<\/p>\n<p><a class=\"easyblog-thumb-preview\" title=\"Rebuild10.jpg\" href=\"http:\/\/dbi-services.com\/blog\/images\/easyblog_images\/88\/Rebuild10.jpg\"><img decoding=\"async\" title=\"b2ap3_thumbnail_Rebuild10.jpg\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/b2ap3_thumbnail_Rebuild10.jpg\" alt=\"b2ap3_thumbnail_Rebuild10.jpg\" \/><\/a><\/p>\n<p>For ONLINE, same result as for DROP_EXISTING: impossible:-?<\/p>\n<p><a class=\"easyblog-thumb-preview\" title=\"Rebuild11.jpg\" href=\"http:\/\/dbi-services.com\/blog\/images\/easyblog_images\/88\/Rebuild11.jpg\"><img decoding=\"async\" title=\"b2ap3_thumbnail_Rebuild11.jpg\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/b2ap3_thumbnail_Rebuild11.jpg\" alt=\"b2ap3_thumbnail_Rebuild11.jpg\" \/><\/a><\/p>\n<p>Let&#8217;s check if my rebuilding really deletes all rows marked as deleted:<\/p>\n<p><a class=\"easyblog-thumb-preview\" title=\"Rebuild12.jpg\" href=\"http:\/\/dbi-services.com\/blog\/images\/easyblog_images\/88\/Rebuild12.jpg\"><img decoding=\"async\" title=\"b2ap3_thumbnail_Rebuild12.jpg\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/b2ap3_thumbnail_Rebuild12.jpg\" alt=\"b2ap3_thumbnail_Rebuild12.jpg\" \/><\/a><\/p>\n<p>As expected all rows previously marked deleted are now physically deleted.<\/p>\n<h3>Conclusion<\/h3>\n<p>With Clustered Columnstore Indexes, we need to plan Index Rebuild Operation on a regular basis. The best way to do this is to insert this in your index work maintenance plan.<br \/>\nFor your information, the rebuilding task in the maintenance plan does not include Clustered Columnstore Indexes rebuilds. <img decoding=\"async\" title=\"Cry\" src=\":cry:\" alt=\"Cry\" border=\"0\" \/><br \/>\nI hope an option will be added in the RTM (Release To Manufacture) version!<\/p>\n","protected":false},"excerpt":{"rendered":"<p>I was surprised that you have to rebuild indexes that are stored In Memory. My previous posting &#8220;SQL Server 2014 &#8211; New Features: xVelocity memory optimized columnstore index&#8221; explains all processes connected with columnstore indexes. In this article, I will explain why, when, and how to rebuild them. Why do we need to rebuild the [&hellip;]<\/p>\n","protected":false},"author":15,"featured_media":3526,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[198],"tags":[49,51,52],"type_dbi":[],"class_list":["post-3525","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-database-management","tag-microsoft","tag-sql-server","tag-sql-server-2014"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v27.2 (Yoast SEO v27.5) - https:\/\/yoast.com\/product\/yoast-seo-premium-wordpress\/ -->\n<title>SQL Server 2014: Rebuilding Clustered Columnstore Indexes - dbi Blog<\/title>\n<meta name=\"description\" content=\"I was surprised that you have to rebuild indexes that are stored In Memory. My previous posting &quot;SQL Server 2014 - New Features: xVelocity memory optimized columnstore index&quot; explains all processes connected with columnstore indexes. In this article, I will explain why, when, and how to rebuild them.\" \/>\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.dbi-services.com\/blog\/sql-server-2014-rebuilding-clustered-columnstore-indexes-1\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SQL Server 2014: Rebuilding Clustered Columnstore Indexes\" \/>\n<meta property=\"og:description\" content=\"I was surprised that you have to rebuild indexes that are stored In Memory. My previous posting &quot;SQL Server 2014 - New Features: xVelocity memory optimized columnstore index&quot; explains all processes connected with columnstore indexes. In this article, I will explain why, when, and how to rebuild them.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/sql-server-2014-rebuilding-clustered-columnstore-indexes-1\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2014-01-30T06:35:00+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/b2ap3_thumbnail_Rebuild01.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"500\" \/>\n\t<meta property=\"og:image:height\" content=\"375\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"St\u00e9phane Haby\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"St\u00e9phane Haby\" \/>\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\":\"Article\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-2014-rebuilding-clustered-columnstore-indexes-1\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-2014-rebuilding-clustered-columnstore-indexes-1\\\/\"},\"author\":{\"name\":\"St\u00e9phane Haby\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/d0bfb7484ae81c8980fc2b11334f803b\"},\"headline\":\"SQL Server 2014: Rebuilding Clustered Columnstore Indexes\",\"datePublished\":\"2014-01-30T06:35:00+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-2014-rebuilding-clustered-columnstore-indexes-1\\\/\"},\"wordCount\":617,\"commentCount\":0,\"image\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-2014-rebuilding-clustered-columnstore-indexes-1\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/sites\\\/2\\\/2022\\\/04\\\/b2ap3_thumbnail_Rebuild01.jpg\",\"keywords\":[\"Microsoft\",\"SQL Server\",\"SQL Server 2014\"],\"articleSection\":[\"Database management\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-2014-rebuilding-clustered-columnstore-indexes-1\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-2014-rebuilding-clustered-columnstore-indexes-1\\\/\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-2014-rebuilding-clustered-columnstore-indexes-1\\\/\",\"name\":\"SQL Server 2014: Rebuilding Clustered Columnstore Indexes - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-2014-rebuilding-clustered-columnstore-indexes-1\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-2014-rebuilding-clustered-columnstore-indexes-1\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/sites\\\/2\\\/2022\\\/04\\\/b2ap3_thumbnail_Rebuild01.jpg\",\"datePublished\":\"2014-01-30T06:35:00+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/d0bfb7484ae81c8980fc2b11334f803b\"},\"description\":\"I was surprised that you have to rebuild indexes that are stored In Memory. My previous posting \\\"SQL Server 2014 - New Features: xVelocity memory optimized columnstore index\\\" explains all processes connected with columnstore indexes. In this article, I will explain why, when, and how to rebuild them.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-2014-rebuilding-clustered-columnstore-indexes-1\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-2014-rebuilding-clustered-columnstore-indexes-1\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-2014-rebuilding-clustered-columnstore-indexes-1\\\/#primaryimage\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/sites\\\/2\\\/2022\\\/04\\\/b2ap3_thumbnail_Rebuild01.jpg\",\"contentUrl\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/sites\\\/2\\\/2022\\\/04\\\/b2ap3_thumbnail_Rebuild01.jpg\",\"width\":500,\"height\":375},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-2014-rebuilding-clustered-columnstore-indexes-1\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SQL Server 2014: Rebuilding Clustered Columnstore Indexes\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/d0bfb7484ae81c8980fc2b11334f803b\",\"name\":\"St\u00e9phane Haby\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/1123227ca39a5dca608c0f72d23cd1904fee29979749bbb3a485b9438436c553?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/1123227ca39a5dca608c0f72d23cd1904fee29979749bbb3a485b9438436c553?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/1123227ca39a5dca608c0f72d23cd1904fee29979749bbb3a485b9438436c553?s=96&d=mm&r=g\",\"caption\":\"St\u00e9phane Haby\"},\"description\":\"St\u00e9phane Haby has more than ten years of experience in Microsoft solutions. He is specialized in SQL Server technologies such as installation, migration, best practices, and performance analysis etc. He is also an expert in Microsoft Business Intelligence solutions such as SharePoint, SQL Server and Office. Futhermore, he has many years of .NET development experience in the banking sector and other industries. In France, he was one of the first people to have worked with Microsoft Team System. He has written several technical articles on this subject. St\u00e9phane Haby is Microsoft Most Valuable Professional (MVP) as well as Microsoft Certified Solutions Associate (MCSA) and\u00a0Microsoft Certified Solutions Expert (MCSE) for SQL Server 2012. He is also Microsoft Certified Technology Specialist (MCTS) and Microsoft Certified IT Professional (MCITP) for SQL Server 2008 as well as ITIL Foundation V3 certified. He holds a Engineer diploma in industrial computing and automation from France. His branch-related experience covers Chemicals &amp; Pharmaceuticals, Banking \\\/ Financial Services, and many other industries.\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/author\\\/stephane-haby\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"SQL Server 2014: Rebuilding Clustered Columnstore Indexes - dbi Blog","description":"I was surprised that you have to rebuild indexes that are stored In Memory. My previous posting \"SQL Server 2014 - New Features: xVelocity memory optimized columnstore index\" explains all processes connected with columnstore indexes. In this article, I will explain why, when, and how to rebuild them.","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.dbi-services.com\/blog\/sql-server-2014-rebuilding-clustered-columnstore-indexes-1\/","og_locale":"en_US","og_type":"article","og_title":"SQL Server 2014: Rebuilding Clustered Columnstore Indexes","og_description":"I was surprised that you have to rebuild indexes that are stored In Memory. My previous posting \"SQL Server 2014 - New Features: xVelocity memory optimized columnstore index\" explains all processes connected with columnstore indexes. In this article, I will explain why, when, and how to rebuild them.","og_url":"https:\/\/www.dbi-services.com\/blog\/sql-server-2014-rebuilding-clustered-columnstore-indexes-1\/","og_site_name":"dbi Blog","article_published_time":"2014-01-30T06:35:00+00:00","og_image":[{"width":500,"height":375,"url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/b2ap3_thumbnail_Rebuild01.jpg","type":"image\/jpeg"}],"author":"St\u00e9phane Haby","twitter_card":"summary_large_image","twitter_misc":{"Written by":"St\u00e9phane Haby","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2014-rebuilding-clustered-columnstore-indexes-1\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2014-rebuilding-clustered-columnstore-indexes-1\/"},"author":{"name":"St\u00e9phane Haby","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/d0bfb7484ae81c8980fc2b11334f803b"},"headline":"SQL Server 2014: Rebuilding Clustered Columnstore Indexes","datePublished":"2014-01-30T06:35:00+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2014-rebuilding-clustered-columnstore-indexes-1\/"},"wordCount":617,"commentCount":0,"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2014-rebuilding-clustered-columnstore-indexes-1\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/b2ap3_thumbnail_Rebuild01.jpg","keywords":["Microsoft","SQL Server","SQL Server 2014"],"articleSection":["Database management"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/sql-server-2014-rebuilding-clustered-columnstore-indexes-1\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2014-rebuilding-clustered-columnstore-indexes-1\/","url":"https:\/\/www.dbi-services.com\/blog\/sql-server-2014-rebuilding-clustered-columnstore-indexes-1\/","name":"SQL Server 2014: Rebuilding Clustered Columnstore Indexes - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2014-rebuilding-clustered-columnstore-indexes-1\/#primaryimage"},"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2014-rebuilding-clustered-columnstore-indexes-1\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/b2ap3_thumbnail_Rebuild01.jpg","datePublished":"2014-01-30T06:35:00+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/d0bfb7484ae81c8980fc2b11334f803b"},"description":"I was surprised that you have to rebuild indexes that are stored In Memory. My previous posting \"SQL Server 2014 - New Features: xVelocity memory optimized columnstore index\" explains all processes connected with columnstore indexes. In this article, I will explain why, when, and how to rebuild them.","breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2014-rebuilding-clustered-columnstore-indexes-1\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/sql-server-2014-rebuilding-clustered-columnstore-indexes-1\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2014-rebuilding-clustered-columnstore-indexes-1\/#primaryimage","url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/b2ap3_thumbnail_Rebuild01.jpg","contentUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/b2ap3_thumbnail_Rebuild01.jpg","width":500,"height":375},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2014-rebuilding-clustered-columnstore-indexes-1\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"SQL Server 2014: Rebuilding Clustered Columnstore Indexes"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/d0bfb7484ae81c8980fc2b11334f803b","name":"St\u00e9phane Haby","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/1123227ca39a5dca608c0f72d23cd1904fee29979749bbb3a485b9438436c553?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/1123227ca39a5dca608c0f72d23cd1904fee29979749bbb3a485b9438436c553?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/1123227ca39a5dca608c0f72d23cd1904fee29979749bbb3a485b9438436c553?s=96&d=mm&r=g","caption":"St\u00e9phane Haby"},"description":"St\u00e9phane Haby has more than ten years of experience in Microsoft solutions. He is specialized in SQL Server technologies such as installation, migration, best practices, and performance analysis etc. He is also an expert in Microsoft Business Intelligence solutions such as SharePoint, SQL Server and Office. Futhermore, he has many years of .NET development experience in the banking sector and other industries. In France, he was one of the first people to have worked with Microsoft Team System. He has written several technical articles on this subject. St\u00e9phane Haby is Microsoft Most Valuable Professional (MVP) as well as Microsoft Certified Solutions Associate (MCSA) and\u00a0Microsoft Certified Solutions Expert (MCSE) for SQL Server 2012. He is also Microsoft Certified Technology Specialist (MCTS) and Microsoft Certified IT Professional (MCITP) for SQL Server 2008 as well as ITIL Foundation V3 certified. He holds a Engineer diploma in industrial computing and automation from France. His branch-related experience covers Chemicals &amp; Pharmaceuticals, Banking \/ Financial Services, and many other industries.","url":"https:\/\/www.dbi-services.com\/blog\/author\/stephane-haby\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/3525","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/15"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=3525"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/3525\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media\/3526"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=3525"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=3525"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=3525"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=3525"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}