research-article

Operator and Query Progress Estimation in Microsoft SQL Server Live Query Statistics

Authors:

Arnd Christian König,

Vivek Narasayya,

Surajit Chaudhuri,

Alexey Eksarevskiy,

Praneeta Prakash,

Jeff NaughtonAuthors Info & Claims

SIGMOD '16: Proceedings of the 2016 International Conference on Management of Data

Pages 1753 - 1764

https://doi.org/10.1145/2882903.2903728

Published: 26 June 2016 Publication History

Abstract

We describe the design and implementation of the new Live Query Statistics (LQS) feature in Microsoft SQL Server 2016. The functionality includes the display of overall query progress as well as progress of individual operators in the query execution plan. We describe the overall functionality of LQS, give usage examples and detail all areas where we had to extend the current state-of-the-art to build the complete LQS feature. Finally, we evaluate the effect these extensions have on progress estimation accuracy with a series of experiments using a large set of synthetic and real workloads.

References

[1]

Program for TPC-H data generation with Skew. ftp://ftp.research.microsoft.com/users/viveknar/TPCDSkew/.

[2]

SESSION_LONGOPS. http://docs.oracle.com/cd/B19306_01/server.102/b14237/dynviews_2092.htm#REFRN30227.

[3]

S. Agrawal, S. Chaudhuri, L. Kollar, A. Marathe, V. Narasayya, and M. Syamala. Database Tuning Advisor for Microsoft SQL Server 2005. In VLDB, pages 1110--1121, 2004.

[4]

N. Bruno, S. Chaudhuri, and L. Gravano. STHoles: A Multidiemsnional Workload-Aware Histogram. In ACM SIGMOD, 2001.

Digital Library

[5]

S. Chaudhuri. An overview of Query Optimization in Relational Systems. In ACM PODS, 1998.

Digital Library

[6]

S. Chaudhuri, R. Kaushik, and R. Ramamurthy. When can we trust Progress Estimators for SQL queries? In Proceedings of the 2005 ACM SIGMOD international conference on Management of data, pages 575--586. ACM, 2005.

Digital Library

[7]

S. Chaudhuri, V. Narasayya, and R. Ramamurthy. Estimating Progress of Execution for SQL queries. In Proceedings of the 2004 ACM SIGMOD international conference on Management of data, pages 803--814. ACM, 2004.

Digital Library

[8]

C. M. Chen and N. Roussoploulos. Adaptive Selectivity Estimation Using Query Feedback. In Proceedings of the ACM SIGMOD Conference, pages 161--172, May 1994.

Digital Library

[9]

J. Duggan, U. Cetintemel, O. Papaemmanouil, and E. Upfal. Performance Prediction for Concurrent Database Workloads. In Proceedings of the 2011 ACM SIGMOD International Conference on Management of data, pages 337--348. ACM, 2011.

Digital Library

[10]

A. Ganapathi, H. Kuno, U. Dayal, J. L. Wiener, A. Fox, M. I. Jordan, and D. Patterson. Predicting Multiple Metrics for Queries: Better Decisions enabled by Machine Learning. In Data Engineering, 2009. ICDE'09. IEEE 25th International Conference on, pages 592--603. IEEE, 2009.

Digital Library

[11]

G. Graefe. Query Evaluation Techniques for Large Databases. ACM Computing Surveys (CSUR), 25(2):73--169, 1993.

Digital Library

[12]

R. H. Güting. Operator-Based Query Progress Estimation. Technical Report 343--2/2008, FernUniversit\"at Hagen, February 2008.

[13]

J. M. Hellerstein, P. J. Haas, and H. J. Wang. Online Aggregation. ACM SIGMOD Record, 26(2):171--182, 1997.

Digital Library

[14]

A. König and G. Weikum. Combining Histograms and Parametric Curve Fitting for Feedback-Driven Query Result-size Estimation. In 25th International Conference on Very Large Databases, 1999.

Digital Library

[15]

A. C. König, B. Ding, S. Chaudhuri, and V. Narasayya. A Statistical Approach towards Robust Progress Estimation. Proceedings of the VLDB Endowment, 5(4):382--393, 2011.

Digital Library

[16]

P.-A. Larson, C. Clinciu, C. Fraser, E. N. Hanson, M. Mokhtar, M. Nowakiewicz, V. Papadimos, S. L. Price, S. Rangarajan, R. Rusanu, and M. Saubhasik. Enhancements to SQL Server Column Stores. In ACM SIGMOD, 2013.

Digital Library

[17]

P.-A. Larson, C. Clinciu, E. N. Hanson, A. Oks, S. L. Price, S. Rangarajan, A. Surna, and Q. Zhou. Sql Server Column Store Indexes. In ACM SIGMOD, 2011.

Digital Library

[18]

J. Li, R. Nehme, and J. Naughton. GSLPI: A Cost-based Query Progress Indicator. In Data Engineering (ICDE), 2012 IEEE 28th International Conference on, pages 678--689. IEEE, 2012.

Digital Library

[19]

J. Li, R. V. Nehme, and J. F. Naughton. Toward Progress Indicators on Steroids for Big Data Systems. In CIDR, 2013.

[20]

L. Lim, M. Wang, and J. S. Vitter. SASH: a Self-adaptive Histogram Set for Dynamically Changing Workloads. In VLDB, 2003.

Digital Library

[21]

G. Luo, J. Naughton, and P. Yu. Multi-query SQL Progress Indicators. In EDBT, pages 921--941, 2006.

Digital Library

[22]

G. Luo, J. F. Naughton, C. J. Ellmann, and M. W. Watzke. Toward a progress indicator for database queries. In Proceedings of the 2004 ACM SIGMOD international conference on Management of data, pages 791--802. ACM, 2004.

Digital Library

[23]

G. Luo, J. F. Naughton, C. J. Ellmann, and M. W. Watzke. Increasing the Accuracy and Coverage of SQL Progress Indicators. In Data Engineering, 2005. ICDE 2005. Proceedings. 21st International Conference on, pages 853--864. IEEE, 2005.

Digital Library

[24]

C. Mishra and N. Koudas. A Lightweight Online Framework for Query Progress Indicators. In Data Engineering, 2007. ICDE 2007. IEEE 23rd International Conference on, pages 1292--1296. IEEE, 2007.

[25]

K. Morton, M. Balazinska, and D. Grossman. ParaTimer: a Progress Indicator for Mapreduce DAGs. In Proceedings of the 2010 ACM SIGMOD International Conference on Management of data, pages 507--518. ACM, 2010.

Digital Library

[26]

K. Morton, A. Friesen, M. Balazinska, and D. Grossman. Estimating the Progress of MapReduce Pipelines. In 26th International Conference on Data Engineering (ICDE). IEEE, 2010.

[27]

M. Stillger, G. Lohman, V. Markl, and M. Kandil. LEO - DB2's Learning Optimizer. In Proceedings of the 27th Conference on Very Large Databases, Rome, Italy, 2001.

Digital Library

[28]

N. Thaper, S. Guha, P. Indyk, and N. Koudas. Dynamic Multidimensional Histograms. In Proceedings of ACM SIGMOD Conference, Madison, USA, pages 428--439, 2002.

Digital Library

[29]

W. Wu, Y. Chi, H. Hacıgümüş, and J. F. Naughton. Towards Predicting Query Execution Time for Concurrent and Dynamic Database Workloads. Proceedings of the VLDB Endowment, 6(10):925--936, 2013.

Digital Library

[30]

W. Wu, Y. Chi, S. Zhu, J. Tatemura, H. Hacigumus, and J. F. Naughton. Predicting Query Execution Time: Are Optimizer Cost Models really Unusable? In Data Engineering (ICDE), 2013 IEEE 29th International Conference on, pages 1081--1092. IEEE, 2013.

Digital Library

Cited By

Lim WMa LZhang WButrovich MArch SPavlo A(2024)Hit the Gym: Accelerating Query Execution to Efficiently Bootstrap Behavior Models for Self-Driving Database Management SystemsProceedings of the VLDB Endowment10.14778/3681954.368203017:11(3680-3693)Online publication date: 1-Jul-2024
https://dl.acm.org/doi/10.14778/3681954.3682030
Dong QLuo G(2024)Progress Estimation for End-to-End Training of Deep Learning Models With Online Data PreprocessingIEEE Access10.1109/ACCESS.2024.335999612(18658-18684)Online publication date: 2024
https://doi.org/10.1109/ACCESS.2024.3359996
Deshmukh HSundarmurthy BPatel J(2022)On inter-operator data transfers in query processing2022 IEEE 38th International Conference on Data Engineering (ICDE)10.1109/ICDE53745.2022.00066(820-832)Online publication date: May-2022
https://doi.org/10.1109/ICDE53745.2022.00066
Show More Cited By

Index Terms

Operator and Query Progress Estimation in Microsoft SQL Server Live Query Statistics
1. Information systems
  1. Data management systems
    1. Database administration
    2. Database management system engines
      1. Database query processing

Recommendations

Query optimization in microsoft SQL server PDW
SIGMOD '12: Proceedings of the 2012 ACM SIGMOD International Conference on Management of Data

In recent years, Massively Parallel Processors have increasingly been used to manage and query vast amounts of data. Dramatic performance improvements are achieved through distributed execution of queries across many nodes. Query optimization for such ...
Microsoft SQL Server 2014 Query Tuning & Optimization
Multi-query SQL progress indicators
EDBT'06: Proceedings of the 10th international conference on Advances in Database Technology

Recently, progress indicators have been proposed for SQL queries in RDBMSs. All previously proposed progress indicators consider each query in isolation, ignoring the impact simultaneously running queries have on each other’s performance. In this paper, ...

Comments

Information & Contributors

Information

Published In

cover image ACM Conferences

SIGMOD '16: Proceedings of the 2016 International Conference on Management of Data

June 2016

2300 pages

ISBN:9781450335317

DOI:10.1145/2882903

General Chairs:
Fatma Özcan
IBM Research, USA
,
Georgia Koutrika
HP Labs, USA
,
Program Chair:
Sam Madden
Massachusetts Institute of Technology, USA

Copyright © 2016 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 the author(s) 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: 26 June 2016

Permissions

Request permissions for this article.

Request Permissions

Check for updates

Author Tags

Qualifiers

Research-article

Conference

SIGMOD/PODS'16

Sponsor:

SIGMOD

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

June 26 - July 1, 2016

California, San Francisco, USA

Acceptance Rates

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

Contributors

Other Metrics

View Article Metrics

Bibliometrics & Citations

Bibliometrics

Article Metrics

14
Total Citations
View Citations
424
Total Downloads

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

Reflects downloads up to 30 Aug 2024

Other Metrics

View Author Metrics

Citations

Cited By

Lim WMa LZhang WButrovich MArch SPavlo A(2024)Hit the Gym: Accelerating Query Execution to Efficiently Bootstrap Behavior Models for Self-Driving Database Management SystemsProceedings of the VLDB Endowment10.14778/3681954.368203017:11(3680-3693)Online publication date: 1-Jul-2024
https://dl.acm.org/doi/10.14778/3681954.3682030
Dong QLuo G(2024)Progress Estimation for End-to-End Training of Deep Learning Models With Online Data PreprocessingIEEE Access10.1109/ACCESS.2024.335999612(18658-18684)Online publication date: 2024
https://doi.org/10.1109/ACCESS.2024.3359996
Deshmukh HSundarmurthy BPatel J(2022)On inter-operator data transfers in query processing2022 IEEE 38th International Conference on Data Engineering (ICDE)10.1109/ICDE53745.2022.00066(820-832)Online publication date: May-2022
https://doi.org/10.1109/ICDE53745.2022.00066
Dong QZhang XLuo G(2022)Improving the Accuracy of Progress Indication for Constructing Deep Learning ModelsIEEE Access10.1109/ACCESS.2022.318149310(63754-63781)Online publication date: 2022
https://doi.org/10.1109/ACCESS.2022.3181493
Baresi LLeva AQuattrocchi G(2021)Fine-Grained Dynamic Resource Allocation for Big-Data ApplicationsIEEE Transactions on Software Engineering10.1109/TSE.2019.293153747:8(1668-1682)Online publication date: 1-Aug-2021
https://doi.org/10.1109/TSE.2019.2931537
Dong QLuo G(2020)Progress Indication for Deep Learning Model Training: A Feasibility DemonstrationIEEE Access10.1109/ACCESS.2020.29896848(79811-79843)Online publication date: 2020
https://doi.org/10.1109/ACCESS.2020.2989684
Marcus RPapaemmanouil O(2019)Plan-structured deep neural network models for query performance predictionProceedings of the VLDB Endowment10.14778/3342263.334264612:11(1733-1746)Online publication date: 1-Jul-2019
https://dl.acm.org/doi/10.14778/3342263.3342646
Baresi LDenaro GQuattrocchi GDumas MPfahl DApel SRusso A(2019)Symbolic execution-driven extraction of the parallel execution plans of Spark applicationsProceedings of the 2019 27th ACM Joint Meeting on European Software Engineering Conference and Symposium on the Foundations of Software Engineering10.1145/3338906.3338973(246-256)Online publication date: 12-Aug-2019
https://dl.acm.org/doi/10.1145/3338906.3338973
Kalmegh PBabu SBoncz PManegold SAilamaki ADeshpande AKraska T(2019)MIFOProceedings of the 2019 International Conference on Management of Data10.1145/3299869.3319902(1678-1695)Online publication date: 25-Jun-2019
https://dl.acm.org/doi/10.1145/3299869.3319902
Luo G(2018)Progress Indication for Machine Learning Model BuildingACM SIGKDD Explorations Newsletter10.1145/3299986.329998820:2(1-12)Online publication date: 11-Dec-2018
https://dl.acm.org/doi/10.1145/3299986.3299988
Show More Cited By

View Options

Get Access

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