Optimizer Studies

I don’t have a real job, which means that if something strikes my fancy, like staring at a spot on the wall for an hour, I can do it.

Occasionally things are a bit more inspired, and so I go poking around what SMRT PPL might be up to.

All you nice people out there paying $7k a core for Enterprise Edition make really cool research, especially into databases, possible.

There’s all sorts of cool stuff in there that has been surfacing in the product lately, from plan progression to the coLUmn ____St0r3! improvements to, well, click around a little.

Modern Plans

The general direction of the optimizer is not only towards automation, but also towards adaptation. Hence all the work in 2017 for Adaptive Joins, Memory Grant Feedback, and Plan Correction.

One of those research papers looks especially interesting to me, despite being named with the intent of dissuading readership:

Leveraging Re-costing for Online Optimization of Parameterized Queries with Guarantees

If you don’t have an advanced math degree or a couple hours of spare time, just read the abstract:

Parametric query optimization (PQO) deals with the problem
of finding and reusing a relatively small number of plans
that can achieve good plan quality across multiple instances
of a parameterized query. An ideal solution to PQO would
process query instances online and ensure (a) tight, bounded
cost sub-optimality for each instance, (b) low optimization
overheads, and (c) only a small number of plans need to be
stored. Existing solutions to online PQO however, fall short
on at least one of the above metrics. We propose a plan recosting

based approach that enables us to perform well on
all three metrics. We empirically show the effectiveness of
our technique on industry benchmark and real-world query
workloads with our modified version of the Microsoft SQL
Server query optimizer.

Hang The DJ

If you’ve been using SQL Server for a while, you’ve probably had to ask the question “why is my query suddenly slow?”, you’ve likely come across the term Parameter Sniffing, and you’ve maybe even been sent directly to a Summer Forest.

Will this fix every parameter sniffing problem? I don’t know!

Like I always say — if performance was never good, there’s no automatic fix for it (yet!). You could go from having one bad plan to having ten bad plans.

You’ll still have the same underlying plan quality issues to address, with no magical fixes in sight.

“lol why is max degree of q-bits set to 1 trillion?”

I love having this kind of stuff to look forward to. People ask if I’m scared that X feature will put X line of work out of business.

My answer is always the same: I’d be a lot more scared about going out of business if there were suddenly no new features to learn.

Thanks for reading!

Brent says: With open source databases getting better, and Platform-as-a-Service options like Google Cloud SQL for Postgres and Amazon Aurora catching on, Microsoft has to keep innovating to stay ahead. They have a neat advantage: the money they invest in database development pays off both in their Azure SQL DB hosting, and in their SQL Server boxed product offering. It’s really cool to see them recognize that and to continue investing research money into features nobody else has yet.