research-article

Enhancements to SQL server column stores

Authors:

Per-Ake Larson,

Campbell Fraser,

Eric N. Hanson,

Mostafa Mokhtar,

Michal Nowakiewicz,

Vassilis Papadimos,

Susan L. Price,

Srikumar Rangarajan,

Mayukh SaubhasikAuthors Info & Claims

SIGMOD '13: Proceedings of the 2013 ACM SIGMOD International Conference on Management of Data

Pages 1159 - 1168

https://doi.org/10.1145/2463676.2463708

Published: 22 June 2013 Publication History

Abstract

SQL 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 cases by a 100X or more. The main limitations of the initial version are addressed in the upcoming release. Column store indexes are updatable and can be used as the base storage for a table. The repertoire of batch mode operators has been expanded, existing operators have been improved, and query optimization has been enhanced. This paper gives an overview of SQL Server's column stores and batch processing, in particular the enhancements introduced in the upcoming release.

References

[1]

Batory, D. S.: On searching transposed files. ACM Trans. Database Syst. 4, 4 (1979), 531--544.

Digital Library

[2]

P. A. Boncz, M. Zukowski, and N.Nes, MonetDB/X100: Hyper-pipelining query execution. CIDR, 2005, 225--237.

[3]

G. P. Copeland and S. Khoshafian, A decomposition storage model. SIGMOD, 1985, 268--279.

Digital Library

[4]

Harizopoulos, S., Liang, V., Abadi, D.J., and Madden, S.: Performance tradeoffs in read-optimized databases. VLDB, 2006, 487--498.

Digital Library

[5]

Sándor Héman, Marcin Zukowski, Niels J. Nes, Lefteris Sidirourgos, Peter A. Boncz: Positional update handling in column stores. SIGMOD, 2010: 543--554.

Digital Library

[6]

J. A. Hoffer and D. G. Severance, The use of cluster analysis in physical data base design, VLDB, 1975, 69--86.

Digital Library

[7]

M. Holsheimer and M. L. Kersten, Architectural support for data mining, KDD, 1994, 217--228.

[8]

D. Inkster, M. Zukowski, and P. A. Boncz, Integration of VectorWise with Ingres, SIGMOD Record, 40(3):45--53, 2011.

Digital Library

[9]

P.-Å. Larson, C. Clinciu, E. N. Hanson, A. Oks, S. L. Price, S. Rangarajan, A. Surna, and Q. Zhou, Sql Server column store indexes, SIGMOD, 2011, 1177--1184.

Digital Library

[10]

Microsoft, Column store Indexes in Books Online for SQL Server 2012, available at http://msdn.microsoft.com/en-us/library/gg492088.aspx.

[11]

Microsoft, SQL Server Column store Index FAQ, http://social.technet.microsoft.com/wiki/contents/articles/3540.sql-server-column store-index-faq-en-us.aspx.

[12]

M. Stonebraker et al. C-Store: A Column-oriented DBMS. VLDB, 2005, 553--564.

Digital Library

[13]

TPC Benchmark DS (Decision Support), Draft Specification, Version 32, http://tpc.org/tpcds.

[14]

ExaSolution, http://www.exasol.com

[15]

Greenplum Database, http://www.greenplum.com

[16]

InfoBright, http://www.infobright.com

[17]

Actian VectorWise, http://www.actian.com/products/vectorwise.

[18]

MonetDB, http://monetdb.cwi.nl

[19]

ParAccel Analytic Database, http://paraccel.com

[20]

SAND CDBMS, http://www.sand.com

[21]

Sybase IQ Columnar database, http://www.sybase.com/products/datawarehousing/sybaseiq

[22]

Teradata Columnar, http://www.teradata.com/products-and-services/database/teradata-14

[23]

Vertica, http://www.vertica.com

Cited By

Rong KLiu PSonje SCharikar M(2024)Dynamic Data Layout Optimization with Worst-Case Guarantees2024 IEEE 40th International Conference on Data Engineering (ICDE)10.1109/ICDE60146.2024.00327(4288-4301)Online publication date: 13-May-2024
https://doi.org/10.1109/ICDE60146.2024.00327
Lee KDutt ANarasayya VChaudhuri S(2023)Analyzing the Impact of Cardinality Estimation on Execution Plans in Microsoft SQL ServerProceedings of the VLDB Endowment10.14778/3611479.361149416:11(2871-2883)Online publication date: 24-Aug-2023
https://dl.acm.org/doi/10.14778/3611479.3611494
Sudhir STao WLaptev NHabis CCafarella MMadden S(2023)Pando: Enhanced Data Skipping with Logical Data PartitioningProceedings of the VLDB Endowment10.14778/3598581.359860116:9(2316-2329)Online publication date: 1-May-2023
https://dl.acm.org/doi/10.14778/3598581.3598601
Show More Cited By

Index Terms

Enhancements to SQL server column stores
1. Information systems
  1. Data management systems
    1. Database design and models
      1. Relational database model
    2. Query languages
      1. Relational database query languages

Recommendations

SQL server column store indexes
SIGMOD '11: Proceedings of the 2011 ACM SIGMOD International Conference on Management of data

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 ...
Real-time analytical processing with SQL server
Proceedings of the 41st International Conference on Very Large Data Bases, Kohala Coast, Hawaii

Over 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 demand ...
Modern Column Stores for Big Data Processing
Big Data Analytics
Abstract
The advent of MapReduce/Hadoop and NoSQL databases undermined the primacy of SQL relational databases for data processing. Pioneering work by researchers on MonetDB and C-Store opened up the world of column stores that retain the SQL model but use ...

Comments

Please enable JavaScript to view thecomments powered by Disqus.

Information & Contributors

Information

Published In

cover image ACM Conferences

SIGMOD '13: Proceedings of the 2013 ACM SIGMOD International Conference on Management of Data

June 2013

1322 pages

ISBN:9781450320375

DOI:10.1145/2463676

General Chairs:
Kenneth Ross
Columbia University
,
Divesh Srivastava
AT&T Research
,
Program Chair:
Dimitris Papadias
HKUST

Copyright © 2013 ACM.

Permission to make digital or hard copies of all or part of this work for personal or classroom use is granted without fee provided that copies are not made or distributed for profit or commercial advantage and that copies bear this notice and the full citation on the first page. Copyrights for components of this work owned by others than ACM must be honored. Abstracting with credit is permitted. To copy otherwise, or republish, to post on servers or to redistribute to lists, requires prior specific permission and/or a fee. Request permissions from [email protected]

Sponsors

SIGMOD: ACM Special Interest Group on Management of Data

Publisher

Association for Computing Machinery

New York, NY, United States

Publication History

Published: 22 June 2013

Permissions

Request permissions for this article.

Request Permissions

Check for updates

Author Tags

Qualifiers

Research-article

Conference

SIGMOD/PODS'13

Sponsor:

SIGMOD

SIGMOD/PODS'13: International Conference on Management of Data

June 22 - 27, 2013

New York, New York, USA

Acceptance Rates

SIGMOD '13 Paper Acceptance Rate 76 of 372 submissions, 20%;

Overall Acceptance Rate 785 of 4,003 submissions, 20%

Contributors

Other Metrics

View Article Metrics

Bibliometrics & Citations

Bibliometrics

Article Metrics

50
Total Citations
View Citations
828
Total Downloads

Downloads (Last 12 months)62
Downloads (Last 6 weeks)3

Reflects downloads up to 23 Nov 2024

Other Metrics

View Author Metrics

Citations

Cited By

Rong KLiu PSonje SCharikar M(2024)Dynamic Data Layout Optimization with Worst-Case Guarantees2024 IEEE 40th International Conference on Data Engineering (ICDE)10.1109/ICDE60146.2024.00327(4288-4301)Online publication date: 13-May-2024
https://doi.org/10.1109/ICDE60146.2024.00327
Lee KDutt ANarasayya VChaudhuri S(2023)Analyzing the Impact of Cardinality Estimation on Execution Plans in Microsoft SQL ServerProceedings of the VLDB Endowment10.14778/3611479.361149416:11(2871-2883)Online publication date: 24-Aug-2023
https://dl.acm.org/doi/10.14778/3611479.3611494
Sudhir STao WLaptev NHabis CCafarella MMadden S(2023)Pando: Enhanced Data Skipping with Logical Data PartitioningProceedings of the VLDB Endowment10.14778/3598581.359860116:9(2316-2329)Online publication date: 1-May-2023
https://dl.acm.org/doi/10.14778/3598581.3598601
Kuschewski MSauerwein DAlhomssi ALeis V(2023)BtrBlocks: Efficient Columnar Compression for Data LakesProceedings of the ACM on Management of Data10.1145/35892631:2(1-26)Online publication date: 20-Jun-2023
https://doi.org/10.1145/3589263
Abebe MLazu HDaudjee KIves ZBonifati AEl Abbadi A(2022)Proteus: Autonomous Adaptive Storage for Mixed WorkloadsProceedings of the 2022 International Conference on Management of Data10.1145/3514221.3517834(700-714)Online publication date: 10-Jun-2022
https://dl.acm.org/doi/10.1145/3514221.3517834
Li ZYiu MChan T(2022)PAW: Data Partitioning Meets Workload Variance2022 IEEE 38th International Conference on Data Engineering (ICDE)10.1109/ICDE53745.2022.00014(123-135)Online publication date: May-2022
https://doi.org/10.1109/ICDE53745.2022.00014
Gubner TLeis VBoncz P(2021)Optimistically Compressed Hash Tables & Strings in theUSSRACM SIGMOD Record10.1145/3471485.347150050:1(60-67)Online publication date: 17-Jun-2021
https://dl.acm.org/doi/10.1145/3471485.3471500
Ding JMinhas UChandramouli BWang CLi YLi YKossmann DGehrke JKraska TLi GLi ZIdreos SSrivastava D(2021)Instance-Optimized Data Layouts for Cloud Analytics WorkloadsProceedings of the 2021 International Conference on Management of Data10.1145/3448016.3457270(418-431)Online publication date: 9-Jun-2021
https://dl.acm.org/doi/10.1145/3448016.3457270
That DGharehdaghi MRasin AMalik T(2021)LDI: Learned Distribution Index for Column Stores2021 IEEE International Conference on Big Data (Big Data)10.1109/BigData52589.2021.9671318(376-387)Online publication date: 15-Dec-2021
https://doi.org/10.1109/BigData52589.2021.9671318
Yang ZChandramouli BWang CGehrke JLi YMinhas ULarson PKossmann DAcharya RMaier DPottinger RDoan ATan WAlawini ANgo H(2020)Qd-tree: Learning Data Layouts for Big Data AnalyticsProceedings of the 2020 ACM SIGMOD International Conference on Management of Data10.1145/3318464.3389770(193-208)Online publication date: 11-Jun-2020
https://dl.acm.org/doi/10.1145/3318464.3389770
Show More Cited By

View Options

Login options

Check if you have access through your login credentials or your institution to get full access on this article.

Full Access

Get this Publication

View options

PDF

View or Download as a PDF file.

eReader

View online with eReader.

Media

Figures

Other

Tables

View Table of Contents