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.
No comments:
Post a Comment