New Data Warehouse Technologies - Data Warehouse Systems: Design and Implementation - page 516

13.5.6 SQL Server xVelocity
Microsoft's approach to column-store technology differs from the ones
described above. Microsoft decided to keep SQL Server as its only database
product and to incorporate the column-store technology in the form of an
optional index. Microsoft SQL Server includes a collection of in-memory and
memory-optimized data management technologies denoted xVelocity. 8 The
xVelocity in-memory analytics engine , formerly known as VertiPaq ,
is an in-memory column-store engine for analytic queries, which uses the
four main techniques we discussed above: columnar storage, compression
techniques, in-memory caching, and highly parallel data scanning and
aggregation algorithms. The xVelocity engine works with the tabular models
of PowerPivot for Excel, SharePoint, and Analysis Services, but not with the
multidimensional and data mining tools of Analysis Services.
The xVelocity engine provides column-store indexes ,whichaimat
enhancing query processing in SQL Server data warehouses. Each column
is stored separately as in a column-store database. In addition, xVelocity
includes a vector-based query execution technology called batch processing
to further speed up query processing. Data are brought to a memory-
optimized cache on demand, although full in-memory query performance is
achieved when all data needed by a query is already in main memory. The
xVelocity column-store index groups and stores data for each column and
then joins all the columns to complete the whole index. The SQL Server
query processor can then take advantage of this kind of index to significantly
improve query execution time.
A key feature of column-store indexes is that they are built in into SQL
Server, which is a general-purpose row-store RDBMS, and the indexes can
be defined as any other one. Given the performance gain which this approach
achieves for many kinds of data warehouse queries, we can even get rid of the
need of building and maintaining summary tables. However, these features
come together with some limitations we discuss later.
The syntax for creating a column-store index was introduced in Chap. 7 .
As an example, we index the columns of the Sales fact table in the Northwind
data warehouse as follows:
CREATE NONCLUSTERED COLUMNSTORE INDEX CSIdx Sales
ON Sales (ProductKey, EmployeeKey, CustomerKey);
Column-store indexes are organized as follows. The Sales fact table is
stored as groups of rows. Given the column-store index defined above, for each
row group and each column, a segment is built containing each column in a
group in compressed form. That means, in our example, if the table contains
ten groups, there will be thirty segments of compressed data. Each segment is
8 http://msdn.microsoft.com/en-us/library/hh922900.aspx