Deploying a Steered Query Optimizer in Production at Microsoft

TL;DR

This paper presents QQ-advisor, a system that specializes query optimizers for complex workloads by externalizing planning, addressing operational challenges, and successfully deploying it in Microsoft's production environment to improve query performance.

Contribution

We introduce QQ-advisor, a novel system that externalizes query planning for workload-specific optimization, and demonstrate its effective deployment in Microsoft’s production environment.

Findings

QQ-advisor improves query plan quality in production workloads.

The system manages steering costs and prevents regressions effectively.

Deployment shows significant performance benefits in real-world scenarios.

Abstract

Modern analytical workloads are highly heterogeneous and massively complex, making generic query optimizers untenable for many customers and scenarios. As a result, it is important to specialize these optimizers to instances of the workloads. In this paper, we continue a recent line of work in steering a query optimizer towards better plans for a given workload, and make major strides in pushing previous research ideas to production deployment. Along the way we solve several operational challenges including, making steering actions more manageable, keeping the costs of steering within budget, and avoiding unexpected performance regressions in production. Our resulting system, QQ-advisor, essentially externalizes the query planner to a massive offline pipeline for better exploration and specialization. We discuss various aspects of our design and show detailed results over production SCOPE workloads at Microsoft, where the system is currently enabled by default.