Database Reference
In-Depth Information
Figure 35-10.
Row groups after deletion of 30,000,000 rows
The test query needs to validate that rows have not been deleted during query execution. Similar to the previous
test, this adds considerable overhead. Table
35-6
shows the execution time of the test query, comparing it to the
execution time of the query before the data deletion.
Table 35-6.
Execution time and delete bitmap
Empty delete bitmap
(Elapsed/CPU time)
Delete bitmap with large number of rows
(Elapsed/CPU time)
2,148ms / 6,815ms
3,995ms / 11,421ms
Index Maintenance Options
You can address all of these performance issues by rebuilding the columnstore index, which you can trigger with the
ALTER INDEX REBUILD
command. The index rebuild forces SQL Server to remove deleted rows physically from the
index and to merge the delta stores' and row groups' data. All column segments are recreated with row groups fully
populated.
Similar to index creation, the index rebuild process is very resource intensive. Moreover, it prevents any data
modifications in the table by holding shared (S) table lock. However, other sessions can still read data from a table
while the rebuild is running.
One of the methods used to mitigate the overhead of index rebuild is
table/index partitioning
. You can rebuild
indexes on a partition-basis and only perform it for partitions that have volatile data. Old facts table data in most Data
Warehouse solutions is relatively static, and ETL processes usually load new data only. Partitioning by date in this
scenario localizes modifications within the scope of one or very few partitions. This can help you dramatically reduce
the overhead of an index rebuild.
As we already discussed, columnstore indexes support an index reorganize process, which you can trigger with
the
ALTER INDEX REORGANIZE
command. The term
index reorganize
is a bit vague here; you can think of it as a tuple
mover process running on-demand. The only action performed by index reorganization is compressing and moving
data from closed delta stores to row groups. Deleted bitmap and open delta stores stay intact.
In contrast to a single-threaded tuple mover process, the
ALTER INDEX REORGANIZE
operation uses all available
system resources while it is running. This can significantly speed up the execution process and reduce the time that
other sessions cannot modify or delete data in a table. It is worth noting again that insert processes are not blocked
during that time.
