Leo Giakoumakis

Indexing XML data stored in a relational database

As XML usage grows for both data-centric and document-centric applications, introducing native support for XML data in relational databases brings significant benefits. It provides a more mature platform for the XML data model and serves as the basis for interoperability between relational and XML data. Whereas query processing on XML data shredded into one or more relational tables is well understood, it provides limited support for the XML data model. XML data can be persisted as a byte sequence (BLOB) in columns of tables to support the XML model more faithfully. This introduces new challenges for query processing such as the ability to index the XML blob for good query performance. This paper reports novel techniques for indexing XML data in the upcoming version of Microsoft® SQL ServerTM, and how it ties into the relational framework for query processing.

The mixed workload CH-benCHmark

While standardized and widely used benchmarks address either operational or real-time Business Intelligence (BI) workloads, the lack of a hybrid benchmark led us to the definition of a new, complex, mixed workload benchmark, called mixed workload CH-benCHmark. This benchmark bridges the gap between the established single-workload suites of TPC-C for OLTP and TPC-H for OLAP, and executes a complex mixed workload: a transactional workload based on the order entry processing of TPC-C and a corresponding TPC-H-equivalent OLAP query suite run in parallel on the same tables in a single database system. As it is derived from these two most widely used TPC benchmarks, the CH-benCHmark produces results highly relevant to both hybrid and classic single-workload systems.

Unit-testing query transformation rules

The process of validating the internal functionality of a query optimizer includes the selection of appropriate queries to be used as test cases for exercising and validating specific code paths. Specifically, it is desirable to be able to implement unit-tests for small components of the query optimizer such as the query transformation rules. In this paper, we present a practical method that simplifies the creation of test cases for validating query transformation rules in a query optimizer. We present the QRel programming framework, which allows designing test cases based on relational algebra expressions. We show how such a framework can be used to create classes of similar test cases that exercise transformation rules over a variety of relational algebra expressions. Finally, we provide some examples of how QRel is used to validate SQL Servers optimizations for subqueries.

Plan space analysis: an early warning system to detect plan regressions in cost-based optimizers

Plan regressions pose a significant problem in commercial database systems: Seemingly innocuous changes to a query optimizer component such as the cost model or the search strategy in order to enhance optimization results may result in unexpected and detrimental changes to previously satisfactory query plans. Database vendors spend substantial resources on quality assurance to guard against this very issue, yet, testing for plan regressions in optimizers has proven hard and inconclusive. This is due to the nature of the problem: the optimizer chooses a single plan---Best Plan Found (bpf)---from a search space of literally up to hundreds of millions of different plan alternatives. It is standard practice to use a known good bpf and test for changes to this plan, i. e., ensure that no changes have occurred. However, in the vast majority of cases the bpf is not be affected by a code-level change, even though the change is known to affect many plans in the search space. In this paper, we propose a holistic approach to address this issue. Instead of focusing on test suites consisting of BPFS we take the entire search space into account. We introduce a metric to assess the optimizers accuracy across the entire search space. We present preliminary results using a commercial database system, demonstrate the usefulness of our methodology with a standard benchmark, and illustrate how to build such an early warning system.

Testing cardinality estimation models in SQL server

Reliable query optimization greatly depends on accurate Cardinality Estimation (CE), which is inherently inexact as it relies on statistical information. In commercial database systems, cardinality estimation models are sophisticated components that over years of development can become very complex. The code that implements cardinality estimation models, like most complex software systems that handle a large space of possible inputs and conditions, can deviate from its original architecture and design points over time. Hence, it is often necessary to refactor and redesign the entire system to accommodate new inputs and conditions, and also to reflect existing ones in a more intentional way. In this paper, we describe such an exercise: the replacement and validation of a new cardinality estimation model in Microsoft SQL Server. We describe the motivation behind this change, and provide a high level sketch of the empirical methods used to ensure that the new cardinality estimation model satisfies its goals while minimizing the potential risk of plan regressions for existing customers.

Rule profiling for query optimizers and their implications

Many modern optimizers use a transformation rule based framework. While there has been a lot of work on identifying new transformation rules, there has been little work focused on empirically evaluating the effectiveness of these transformation rules. In this paper we present the results of an empirical study of “profiling” transformation rules in Microsoft SQL Server using a diverse set of real world and benchmark query workloads. We also discuss the implications of these results for designing and testing query optimizers.