Query optimization in microsoft SQL server PDW

Abstract

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 system is a challenging and important problem.In this paper we describe the Query Optimizer inside the SQL Server Parallel Data Warehouse product (PDW QO). We leverage existing QO technology in Microsoft SQL Server to implement a cost-based optimizer for distributed query execution. By properly abstracting metadata we can readily reuse existing logic for query simplification, space exploration and cardinality estimation. Unlike earlier approaches that simply parallelize the best serial plan, our optimizer considers a rich space of execution alternatives, and picks one based on a cost-model for the distributed execution environment. The result is a high-quality, effective query optimizer for distributed query processing in an MPP.

Robust heuristic algorithms for exploiting the common tasks of relational cloud database queries

“…In [47], a classical query optimizer is adapted to Cloud computing workloads where it uses a partitioned database on a shared-nothing architecture. In [44], a parallel data warehouse system optimizer is developed for single queries by considering a rich space of execution alternatives with bushy-tree plans instead of simply parallelizing the best serial plan. Query optimization in Cloud environments can have different goals unlike the traditional query optimizers and the search space becomes much larger.…”

“…The model uses QP trees that can be executed in a parallel manner by several computers to examine every factor in detail [44]. The cost model depends on the statistics of the database.…”

Orca

“…The proposed design allows each operator to execute independently on local data, as well as work in parallel with other copies of the operator running in other processes. Several MPP databases [6,8,18,20,23] make use of these principles to build commercially successful products.…”

“…Cascades [13] is an extensible optimizer framework whose principles have been used to build MS-SQL Server, SCOPE [6], PDW [23], and Orca, the optimizer we present in this paper. The popularity of this framework is due to its clean separation of the logical and physical plan spaces.…”

Designing query optimizers for big data problems of the future

“…It contains a SQL query optimizer, written from scratch, especially for the Vertica Storage System and Execution Engine. We wrote our own optimizer, despite a countervailing industry trend to reuse or wrap existing optimizers [1,4] in new database systems.…”