Database Reference
In-Depth Information
tradeoffs.
Tip
In SQL 2012, you can select from a column store index and you can also rebuild.
A new feature added to SQL 2014 is the ability to select and rebuild a column
store index but also directly insert, update, or delete individual rows.
When using Column Storage, you will not be able to use Large Pages and Lock Pages in
Memory (trace flag 834) because this will increase the work the translation look-aside
be read-only. Any time you need to write data to the table, you need to drop and re-
create the column store index, but this can easily be done with scheduled batch jobs. For
the types of workloads that Column Storage is well suited to, these tradeoffs are
normally worth the benefits.
Note
For detailed information on the xVelocity memory optimized column store
feature, see the following Microsoft article:
The benefits of Column Storage as documented in the link in the following tip include:
Index compression
—Column Storage indexes are far smaller than their B-Tree
counterparts.
Parallelism
—The query algorithms are built from the ground up for parallel
execution.
Optimized and smaller memory structures
From a storage perspective, the benefits of Column Storage are far less storage capacity
and performance being required to achieve the desired query performance. The
improvement in query performance ranges from 3X to 6X on average, up to 50X. See
postattachments/00-10-36-36-
43/SQL_5F00_Server_5F00_2012_5F00_Column_2D00_Store.pdf
.
Tip
If you are using SAP BW with SQL Server 2012 (SP1 recommended, cumulative
update 2 minimum), then Column Storage is turned on by default (for SAP BW
7.0 and above) when certain support packs are applied. For detailed information
