Category: Query Tuning

Jared Poche explains some of the ideas behind the costing algorithm in SQL Server:

One thing to remember is that cost in SQL Server is always an estimate. This is a number SQL Server calculates when considering multiple potential plans to determine which would be the best. But the number of rows it expects a given operation to return or how many times that operation runs can be off. All of that is based on statistics.

It doesn’t then go back and update the cost number later if those numbers were incorrect. So while we can use the cost as an indicator of which query or operator we should focus on, don’t completely tunnel-vision that one thing.

This kind of cost mismatch allows something to look awful on an execution plan but not actually be a problem, or (in the case of most user-defined functions prior to SQL Server 2019) vice versa.

Monica Rathbun wants us to think about whether we really need that ORDER BY clause:

We know that sorting can be one of the most expensive things in an execution plan as shown below. However,  we continue to do ORDER BYs repeatedly. Yes, I 100% agree that there is a need to sort a results set and that this should be done in the procedure for good reason, but my concern is having  multiple sorts, erroneous sorts, and the sorts that can be done elsewhere. These are the ones that waste resources and can stifle performance.

Click through for a demo showing that this does make a difference.

Jared Poche talks about the ability to use either predicate on a join for filtering:

You may recall the transitive property from elementary school math class. It states:

If A = B, and B = C, then A = C

The SQL Server optimizer can and will use this property, and it can lead to issues in your queries. When I’m writing a query, I have a clear idea of how I want it to operate. But using the transitive property, SQL Server has additional options one might not expect, and this may occasionally cause things to go awry

Click through for an example. Most of the time, the optimizer is smart about using transitivity, but sometimes it can go wrong.

Erik Darling notes that adding an ORDER BY clause to a query can change the underlying query plan:

Sometimes I think it’s interesting how adding a seemingly useless or harmless thing to a query can change the query plan.

Here’s a quick example using an Order By on an indexed column.

Your mileage may vary on whether that’s a good thing.

Grant Fritchey reminds us to look at the first operation when viewing an execution plan:

The first time you see a new execution plan that you’re examining to fix a performance problem, something broken, whatever, you should always start by looking at the first operator.

First Operator

The first operator is easily discerned (with an exception). It’s the very first thing you see in a graphical execution plan, at the top, on the left. It says SELECT in this case:

It’s easy to overlook, but Grant gives some good reasons not to do so.