Category: Query Tuning

Paul White begins a new series:

This is the first part of a series about simple parameterization and trivial plans. These two compilation features are closely connected and have similar goals. Both target performance and efficiency for workloads frequently submitting simple statements.

Despite the “simple” and “trivial” names, both have subtle behaviours and implementation details that can make how they work difficult to understand. This series doesn’t dwell too long on the basics but concentrates on less well-known aspects likely to trip up even the most experienced database professionals.

In this first part, after a quick introduction, I look at the effects of simple parameterization on the plan cache.

Read on for more detail.

Erik Darling continues a series on understanding execution plans. First up, what some of those warnings mean:

The good news is that SQL Server’s query plans will attempt to warn you about problems. The bad news is that most of the warnings only show up in Actual Execution Plans. The worse news is that a lot of the warnings that try to be helpful in Estimated Execution plans can be pretty misleading.

Next, Erik reviews some interesting properties:

There’s a lot of stuff flying around in a query plan. Data and what not.

Sure, you can hover over operators and arrows and see some stuff, but if you really wanna see stuff — I mean REALLY wanna see stuff — you gotta get into the properties.

Check out both posts.

Erik Darling doesn’t need your costs:

Costs are okay for figuring out why SQL Server’s cost-based optimizer:

– Chose a particular query plan

– Chose a particular operator

Costs are not okay for figuring out:

– Which queries are the slowest

– Which queries you should tune first

– Which missing index requests are the most important

– Which part of a query plan was the slowest

Yep. And read on for more information.

Grant Fritchey implores us not to forget the SELECT/INSERT/UPDATE/whatever operator:

I’ve watched several people recently go straight to XML when reading execution plans because they didn’t know about the execution plan properties in the first operator. Now, don’t get me wrong. If going straight to the XML is working for you, that’s fine. Keep doing it. I’m absolutely not questioning how anyone does things. I just want people to know that “hidden” information isn’t so much hidden as much as it’s not too obvious.

Read on to see what this contains.

Erik Darling goes scalar hunting:

This is awful. It really is. It’s so awful. These operators skated by undetected for years. Acting so innocent with their 0% cost.

Subprime operators, or something.

In this post, I’m going to show you how compute scalars hide work, and how interpreting them in actual execution plans can even be tricky.

I’m now imagining Erik as the Steve Irwin of the query tuning world. And it’s kind of working for me.

Erik Darling takes us on a batch mode joyride:

Prior to SQL Server 2019, you needed to have a columnstore index present somewhere for batch mode to kick in for a query.

Somewhere is, of course, pretty loose. Just having one on a table used in a query is often enough, even if a different index from the table is ultimately used.

Batch mode is pretty great and Erik explains why.