Microsoft Technology Licensing, LLC

Other

Vivek Ravindranath Narasayya

Surajit J Chaudhuri

Kris Kuppuswamy Ganjam

Manoj A Syamala

Sudipto Das

Zhimin Chen

Yeye He

Sanjay Agrawal

Rajeev Motwani

Feng Li

Kaushik Chakrabarti

James P Finnigan

Arnd Christian König

Kanstantsyn Zoryn

Mayur D Datar

Ajay Kalhan

John Morgan Oslake

Pankaj Arora

Wentao Wu

Shize Xu

Junfeng Dong

Tarique Ashraf Siddiqui

Changsong Li

Jiaqi Liu

Ravishankar Ramamurthy

Chi Wang

Xu Chu

Anshuman Dutt

Bailu Ding

Badrish Chandramouli

Ashish Gupta

Arvind Arasu

Bolin Ding

Arnd Christian Konig

Yue Wang

Zhongyuan Wang

Lengning Liu

Lucas Pritz

Gantam Das

Yi Shan

Kalen Wong

Kirk Hartmann Freiheit

Aditya Kuppa

Patrick Clinton Little

Gaurav Ghosh

Meiyalagan Balasubramanian

Katchaguy Areekijseree

Paula Budig Greve

Hyunjung Park

Gaoxiang Xu

Lin Ma

Atul Adya

Marius Dumitru

Prasanna Ganesan

Venkatesh Ganti

Nicolas Bruno

Hideaki Kimura

Beverly Yang

Raghav Kaushik

Shriraghav Kaushik

Tao Cheng

Ravi Ramamurthy

Gunjan Jha

David Talby

Abhinandan Sujit Das

Guatam Das

Andrija Jovanovic

Jiewen Huang

Anish Das Sarma

Lik Han Chu

Sunita Sarawagi

Omer Zaki

Sarajit Chaudhuri

Christian Konig

Azade Nazi

Jingjing Wang

Saehan Jo

Tobias Ziegler

Aarati Kakaraparthy

Kapil Eknath Vaidya

Manoj Syamala

Matteo Brucato

Xiaoying Wang

Miodrag Radulovic

Ryan Marcus

Hicham G Elmongui

Eduardo Laureano

Adith Swaminathan

Renfei Luo

Tsheko Mutungu

Arjun Dasgupta

Leo Giakoumakis

Manoj A Symala

James Finnigan

Arnd Christian KÖNIG

This disclosure describes a workload reduction system that reduces the complexity of workloads sent to a database system. For instance, the workload reduction system pre-processes workloads sent to a database system by generating reduced workloads that include less complex queries that reference fewer tables or columns than the original workloads. In various implementations, the workload reduction system uses table reduction functions and query re-writing functions to generate the reduced workloads. As a result, the workload reduction system improves computational efficiency by rewriting complex queries from workloads into simpler ones that speed up index tuning and decrease individual what-if call times.

Using workload reduction to improve index tuning

This document relates to budget-constrained index tuning in database systems. A method for index tuning within a database system includes receiving a budget constraint, generating a set of candidate indexes for a workload of queries, and determining a derived cost for each query-configuration pair (QCP). The method further involves identifying a subset of QCPs where the derived cost and a what-if cost have a delta value below a predefined difference threshold and allocating the budget constraint to these identified QCPs to avoid what-if calls. Additionally, the method reallocates a saved portion of the budget constraint to other QCPs where the delta value exceeds the difference threshold, evaluates these other QCPs using what-if calls to select an optimal index configuration, and applies the selected index configuration to the database system.

Budget-constrained index tuning in database systems

Methods, computer systems, computer-storage media, and graphical user interfaces are provided for facilitating data transformations, according to embodiments of the present invention. In one embodiment, a transformation function is executed using an example input value to obtain an initial output value. Thereafter, a plurality of supplemental transformation tools is applied to the initial output value to generate a plurality of intermediary output values. Based on a comparison of each of the intermediary output values to an example output value, the supplemental transformation tool that generated an intermediary output value having a greatest extent of similarity to the example output values is identified. The identified supplemental transformation tool and the transformation function are used to generate a transformation program that transforms the example input values to the desired form in which to transform data.

Efficient transformation program generation

A data processing system implements a cache-conscious aggregation framework for cache-efficient top-k aggregation over high cardinality large datasets. The framework leverages skew in the distribution of data in the datasets to minimize data movements within the local caches of the cores of the multicore processors of the data processing system. The framework performs representative sampling on the dataset and utilizes these samples to identify candidate groups in the dataset for the top-k results. The system performs exact aggregations for the candidate groups and performs hashing and pruning on the non-candidate groups in the dataset to identify top-k results included in the non-candidate groups without having to calculate the exact aggregations for the non-candidate groups.

Cache-efficient top-k aggregation over high cardinality large datasets

Intelligent memory brokering for multiple process instances, such as relational databases (e.g., SQL servers), reclaims memory based on value, thereby minimizing cost across instances. An exemplary solution includes: based at least on a trigger event, determining a memory profile for each of a plurality of process instances at a computing node; determining an aggregate memory profile, the aggregate memory profile indicating a memory unit cost for each of a plurality of memory units; determining a count of memory units to be reclaimed; identifying, based at least on the aggregate memory profile and the count of memory units to be reclaimed, a count of memory units to be reclaimed within each process instance so that a total cost is minimized to reclaim the determined count; and communicating, to each process instance having identified memory units to be reclaimed, a count of memory units to be reclaimed within the process instance.

Memory management for multiple process instances

Methods, computer systems, computer-storage media, and graphical user interfaces are provided for facilitating data transformations, according to embodiments of the present invention. In one embodiment, a set of example values including example input values that indicate data values to be transformed and example output values that indicate a desired form in which to transform data. Based on the set of example values, a data transformation function that is relevant to the set of example values is identified. The data transformation function is used to generate a transformation program to transform the example input values to the desired form in which to transform data. A suggestion of the transformation program can be provided to a user device, wherein selection of the transformation program suggestion results in a data transformation.

Facilitating data transformations

Solutions for data unification include: receiving a data record, the data record comprising a plurality of data fields; selecting, from among the plurality of data fields, a subset of the data fields, the subset of the data fields being fewer in number than the plurality of data fields, wherein selecting the subset of the data fields comprises: applying a first rule to select at least a first one of the data fields within the data record for inclusion in the subset of the data fields; using content of the subset of the data fields, generating a stable identifier (stableID) for the data record; and inserting the stableID into a primary key data field of the data record.

Data unification

A method of training an index filter for an index tuning system includes receiving a plurality of different workloads and a plurality of different databases, each database including different tables and each workload including a plurality of queries; generating labeled training by making optimizer calls to a query optimizer using query and index configuration pairs from the plurality of databases and the plurality of workloads; training an index filter model to identify signals in the labeled training data, the signals being indicative of a potential performance improvement associated with using an index configuration for a given query; training the index filter model to learn rules over the signals for identifying spurious indexes; and storing the index filter model in a memory.

Scalable index tuning with index filtering and index cost models

A system and method for executing SQL statements includes receiving an SQL statement for comparing two trendsets over a relation using a scoring function, each of the trendsets including one or more trends, each of the trends being designated by a constraint and a grouping-measure combination, wherein comparing the trendsets includes identifying trend pairs for comparison, each of the trend pairs including a trend from the each of the trendsets having a common grouping-measure combination. The SQL statement is transformed into a basic plan of existing logical operators for performing the SQL statement. A set of sub-plans is determined based on the basic plan. Pairs of sub-plans are merged to generate a set of merged sub-plans. A cost for each of the merged sub-plans is determined. The merged sub-plan having the lowest cost is used to execute the SQL statement.

Systems and methods for accelerating and optimizing groupwise comparison in relational databases

The present disclosure relates to methods and systems for compressing workloads for use with index tuning. The methods and systems receive a workload with a plurality of queries. The methods and systems represent each query using query features and a utility. The methods and systems select a query for a query subset based on a benefit of the query determined using the query features and the utility. The methods and systems update the features and the utility of the remaining queries in the workload and select another query to add to the query subset based on an updated benefit determined using the updated features and utilities. The methods and systems select queries for the query subset equal to a received query subset size. The methods and systems use the query subset in index tuning to provide one or more indexes to recommendations.

Compressing workloads for scalable index tuning

The present disclosure relates to systems, methods, and computer-readable media for determining optimal index configurations for processing workloads in a database management system. For instance, an index configuration system can efficiently determine a subset of indexes for processing a workload utilizing one or more reinforcement learning models. For example, in various implementations, the index configuration system utilizes a Markov decision process and/or a Monte Carlo tree search model to determine an optimal subset of indexes for processing a workload in a manner that effectively utilizes computing device resources while also avoiding significant interference with customer workloads.

Constraint-based index tuning in database management systems utilizing reinforcement learning

Placement of a tenant database in an oversubscribed, database-as-a-service cluster comprised of a plurality of nodes is described. The placement may be based on per-node estimates of a probability of resource demand violation if the tenant database is placed on the node. Past resource usage of similar tenant databases subscribed to the cluster that are collected and stored as compressed traces may be used to obtain the estimates. In some examples, based on the estimates, a per-node expected number of resource violations is determined and compared across nodes, where the determined placement minimizes the number of resource violations. In other examples, when the tenant database is being placed in parallel with other tenant databases, a score assigned to each valid configuration for the placement may be modified based on the estimates, where the determined placement is the configuration having a lowest score.

Tenant database placement in oversubscribed database-as-a-service cluster

The present disclosure relates to systems, methods, and computer-readable media for optimizing selection of a cached execution plan to use in processing a parametric query. For example, systems described herein involve training a plan selection model that makes use of machine learning to identify an execution plan from a set of pre-selected execution plans based on predicted cost of executing a query instance in accordance with the selected execution plan (e.g., relative to predicted costs of executing the query instance using other pre-selected execution plans). This application describes features related to lowering costs associated with selecting the execution plan in a way that will continue to be more accurate overtime based on training and refining the plan selection model.

Using query logs to optimize execution of parametric queries

A model generator constructs a model for estimating selectivity of database operations by determining a number of training examples necessary for the model to achieve a target accuracy and by generating approximate selectivity labels for the training examples. The model generator may train the model on an initial number of training examples using cross-validation. The model generator may determine whether the model satisfies the target accuracy and iteratively and geometrically increase the number of training examples based on an optimized geometric step size (which may minimize model construction time) until the model achieves the target accuracy based on a defined confidence level. The model generator may generate labels using a subset of tuples from an intermediate query expression. The model generator may iteratively increase a size of the subset of tuples used until a relative error of the generated labels is below a target threshold.

Efficiently constructing regression models for selectivity estimation

Methods, computer systems, computer-storage media, and graphical user interfaces are provided for facilitating data transformations, according to embodiments of the present invention. In one embodiment, a plurality of remote sources is searched to identify candidate transformation tools relevant for performing data transformations. The candidate transformation tools are analyzed to identify tool examples corresponding with each of the candidate transformation tools. For each of the candidate transformation tools, the tool examples are stored in association with the corresponding candidate transformation tool. Based on a comparison of tool examples with example values, a transformation tool is identified as relevant to facilitate transforming example input values to the desired form in which to transform data.

Collecting and annotating transformation tools for use in generating transformation programs

Growing community of inventors

Redmond, WA, United States of America

Vivek Ravindranath Narasayya