Performance Improvements - Getting Started with SQL Server 2014 Administration - page 75

The ONLINE = ON option can also be used and now contains a further switch
WAIT_AT_LOW_PRIORITY option that will allow you as a DBA to specify how long
the rebuild process should wait for the necessary locks. The WAIT_AT_LOW_PRIORITY
option will also allow the DBA to configure the termination of blocking processes
related to the rebuild statement. This can be used in combination with the REBUILD
Partition option.
Columnstore indexes
Columnstore index is a technology used for storing, retrieving, and indexing using
the column data as opposed to the traditional row-based formats. Columnstore
indexes were first introduced in SQL Server 2012 with certain limitations, and some
of the limitations have been removed in SQL Server 2014. In SQL Server 2014, there is
support for both clustered and non-clustered columnstore indexes, which is a change
from SQL Server 2012 as it only supported non-clustered index columnstore indexes.
The SQL Server columnstore index differs from more traditional index types as it
stores and manages data using the column-based data storage and column-based
query processing. Columnstore indexes were initially designed to work well for data
warehousing and decision support environments. In these types of environments,
it's not uncommon that bulk loads run during an overnight batch processing job,
when users are not accessing the system. During peak hours, the system supports
read-only queries for reports. Columnstore index, if configured appropriately on the
correct tables, can be used to achieve up to 10 times the query performance compared
to traditional row-based storage and up to 7 times the data compression over the
original data size.
Columnstore index offers a number of benefits, including the following:
• Columns often have similar data that is stored contiguously on disks. This
can result in high compression rates.
• High compression rates improve the query performance as they have
a smaller in-memory footprint; therefore, SQL Server can carry out more
query processing in the memory.
• Columnstore indexes can reduce CPU usage. Batch-mode execution is a new
query execution mechanism added to SQL Server that reduces CPU usage.
Batch-mode execution is integrated with the columnstore storage format.
This is sometimes referred to as vector-based or vectorized execution.
• A typical query will often select only a few columns from a table, which can
reduce the total I/O from the physical storage, thus improving the overall
performance again.