ABSTRACT
The SQL Server 11 release (code named "Denali") introduces a new data warehouse query acceleration feature based on a new index type called a column store index. The new index type combined with new query operators processing batches of rows greatly improves data warehouse query performance: in some cases by hundreds of times and routinely a tenfold speedup for a broad range of decision support queries. Column store indexes are fully integrated with the rest of the system, including query processing and optimization. This paper gives an overview of the design and implementation of column store indexes including enhancements to query processing and query optimization to take full advantage of the new indexes. The resulting performance improvements are illustrated by a number of example queries.
- Abadi, D.J., Madden, S.R., and Ferreira, M.: Integrating compression and execution in column-oriented database systems. SIGMOD, 2006, 671--682. Google ScholarDigital Library
- Abadi, D.J., Myers, D.S., DeWitt, D.J., and Madden, S.R.: Materialization strategies in a column-oriented DBMS. ICDE, 2007, 466--475.Google Scholar
- Abadi, D.J., Madden, S.R., and Hachem, N.: Column-stores vs. row-stores: how different are they really? SIGMOD, 2008, 981--992. Google ScholarDigital Library
- Batory, D. S.: On searching transposed files. ACM Trans. Database Syst. 4, 4 (1979), 531--544. Google ScholarDigital Library
- Copeland, G.P., Khoshafian, S.N.: A Decomposition Storage Model. In Proc. SIGMOD, 1985, 268--279. Google ScholarDigital Library
- Harizopoulos, S., Liang, V., Abadi, D.J., and Madden, S.: Performance tradeoffs in read-optimized databases. VLDB, 2006, 487--498. Google ScholarDigital Library
- Jeffrey A. Hoffer, Dennis G. Severance: The Use of Cluster Analysis in Physical Data Base Design. VLDB 1975: 69--8. Google ScholarDigital Library
- S. Padmanabhan, T. Malkemus, R. Agarwal, and A. Jhingran. Block oriented processing of relational database operations in modern computer architectures. ICDE, 2001, 567--574. Google ScholarDigital Library
- M. Stonebraker et al. C-Store: A Column-oriented DBMS. VLDB, 2005, 553--564. Google ScholarDigital Library
- TPC Benchmark DS (Decision Support), Draft Specification, Version 32, available at http://tpc.org/tpcds.Google Scholar
- Aster Data, http://www.asterdata.co.Google Scholar
- ExaSolution, http://www.exasol.co.Google Scholar
- Greenplum Database, http://www.greenplum.co.Google Scholar
- InfoBright, http://www.infobright.co.Google Scholar
- Ingres VectorWise, http://www.ingres.com/products/vectorwis.Google Scholar
- MonetDB, http://monetdb.cwi.n.Google Scholar
- ParAccel Analytic Database, http://paraccel.co.Google Scholar
- SAND CDBMS, http://www.sand.co.Google Scholar
- Sybase IQ Columnar database, http://www.sybase.com/products/datawarehousing/sybasei.Google Scholar
- Vertica, http://www.vertica.com.Google Scholar
Index Terms
- SQL server column store indexes
Recommendations
Enhancements to SQL server column stores
SIGMOD '13: Proceedings of the 2013 ACM SIGMOD International Conference on Management of DataSQL Server 2012 introduced two innovations targeted for data warehousing workloads: column store indexes and batch (vectorized) processing mode. Together they greatly improve performance of typical data warehouse queries, routinely by 10X and in some ...
Real-time analytical processing with SQL server
Proceedings of the 41st International Conference on Very Large Data Bases, Kohala Coast, HawaiiOver the last two releases SQL Server has integrated two specialized engines into the core system: the Apollo column store engine for analytical workloads and the Hekaton in-memory engine for high-performance OLTP workloads. There is an increasing ...
Set Predicates in SQL: Enabling Set-Level Comparisons for Dynamically Formed Groups
In data warehousing and OLAP applications, scalar-level predicates in SQL become increasingly inadequate to support a class of operations that require set-level comparison semantics, i.e., comparing a group of tuples with multiple values. Currently, ...
Comments