Sunday, September 30, 2018

Build and rebuild clustered columnstore indexes online in SQL Server 2019


Microsoft development team works around the SQL Server to make it as a central part of the Microsoft data platform and we cannot deny the truth about SQL Server that is an industry leader in operational database management systems (ODBMS). Microsoft has been introduced SQL Server 2019 preview which builds on previous releases to grow SQL Server as a platform that gives us choices of development languages, data types, on-premises or cloud, and operating systems. 
If we are talking for optimizing database performance then the indexes are one of the most powerful tools for it. This is the biggest fact for the indexes that they can become more fragmented and less effective in case we did any insert, update, and delete operations on the data table. This is a pain for DBAs to take care of indexes and on the regularly basis, index rebuild operations is required.
With the growing sizes of databases, index rebuilds can take a very long time. Build and rebuild clustered columnstore indexes online is the feature of SQL Server 2019 preview where creating clustered columnstore indexes (CCI) was an offline process in the previous versions of SQL Server - requiring all changes stop while the CCI is created. 
With SQL Server 2019 preview and Azure SQL Database you can create or re-create CCI online. Workload will not be blocked and all changes made on the underlying data are transparently added into the target columnstore table.
Examples of new Transact-SQL statements that can be used are:
---- Create Clustered Columnstore Index

CREATE CLUSTERED COLUMNSTORE INDEX cci
  ON <tableName>
 WITH (ONLINE = ON);

---- Altered Clustered Columnstore Index
ALTER INDEX cci
  ON <tableName>
  REBUILD WITH (ONLINE = ON);
Combine that with the business needs for our applications to be always available and performant and this can be an issue. Big OLTP environments with busy workloads often have very short maintenance windows with some too short to execute large index rebuild operations. 

Popular Posts

Get Sponsored by Big Brands