Press "Enter" to skip to content

Category: Query Tuning

Weirdness with Aggregation

Published 2024-02-15 by Kevin Feasel

Erik Darling digs into a problem. Part 1 sets up the scenario:

Here’s the query plan, which yes, you’re reading correctly, runs for ~23 seconds, fully joining both tables prior to doing the final aggregation.

I’m showing you a little extra here, because there are missing index requests that the optimizer asks for, but we’ll talk about those in tomorrow’s post.

The wait stats for this query, since it’s running in Batch Mode, are predictably HT-related.

Part 2 covers those missing indexes:

I’ve taken a small bit of artistic license with them.

The crappy thing is… They really do not help and in some cases things get substantially worse.

Maybe it’s because it’s early and I’m trying to compile things in my head rather than actually trying it out, but it seems like a combo of CTE + CROSS APPLY or a pair of CROSS APPLY statements could work better (especially with a good index), assuming that join doesn’t need to be in place. Given the query as it is, with two MAX() aggregations and no GROUP BY clause, that could be an avenue for improvement, though one I have not actually tested. Nonetheless, read both of Erik’s posts.

Comments closed

Troubleshooting a Stored Procedure Performance Problem

Published 2024-02-14 by Kevin Feasel

Deborah Melkin digs in:

In fact, I just fixed a stored procedure that had its performance change due to an upgrade to SQL Server 2022 last week. We were doing internal testing in our test environment and one proc suddenly took significantly longer than it should have. But it was also a proc that had not changed in months so it was very clear that the reason it became a problem was due to the upgrade.

Click through for some detail on how Deborah figured it out.

Comments closed

Recompile Hints and Query Store

Published 2024-02-09 by Kevin Feasel

Erik Darling talks about one very popular tool and one very popular technique and what happens when they mix:

But let’s say one day you have performance issues regardless of all the recompiling in the world. No matter what set of parameters get passed in, your procedure is just sleepy-dog slow.

Things get so bad that you hire a young, handsome consultant with reasonable rates like myself to help you figure out why.

The plan cache will be pretty useless for troubleshooting the recompile-hinted queries, but we can still use Query Store.

Read on for more detail and a demonstration.

Comments closed

An Overview of Join Elimination

Published 2024-02-02 by Kevin Feasel

Chad Callihan doesn’t need solo rows; he needs team players:

SQL Server can be pretty smart when it comes to avoiding work it’s not required to do. One instance of this is the concept of join elimination. Join elimination occurs when queries involving joins may only need to check one table instead of two (or more) based on foreign key constraints.

Let’s take a look at join elimination in action.

Read on for one example of join elimination and one more reason why those foreign key constraints are so useful.

Comments closed

Being Smart about Missing Index Requests

Published 2024-01-26 by Kevin Feasel

Erik Darling doesn’t trust the system:

SQL Server’s missing index requests (and, by extension, automatic index management) are about 70/30 when it comes to being useful, and useful is the low number.

The number of times I’ve seen missing indexes implemented to little or no effect, or worse, disastrous effect… is about 70% of all the missing index requests I’ve seen implemented.

This has been pretty close to my experience as well. Click through for a much better approach to the task.

Comments closed

Fixing Eager Spooling

Published 2024-01-25 by Kevin Feasel

Erik Darling sends it to the moon:

Probably the most fascinating thing about Eager Index Spools to me is how often the optimizer will insert them into execution plans, often to a query’s detriment.

In a sane world, a non-loop join plan would be chosen, a missing index request would be registered that matches whatever would have been spooled into an index, and we’d all have an easier time.

Read on for a few examples of the problem and two separate ways you can fix it. Remember, kids: friends don’t let friends eagerly spool.

Comments closed

Preventing ASYNC_NETWORK_IO Waits in SQL Server

Published 2024-01-23 by Kevin Feasel

Vlad Drumea troubleshoots a pernicious wait type:

In this post I’ll go over what the ASYNC_NETWORK_IO wait type is, when it occurs, and how you can tell if the application is causing it.

Lately I’ve had to troubleshoot a few situations where the issue seemed like a poor performing query, but the apparent slowdown was due to applications causing excessive ASYNC_NETWORK_IO waits in SQL Server.

This particular wait doesn’t always happen because of slow apps, though that’s often the case. Vlad breaks out several reasons why you might see the wait and explains what you can do to fix the problem.

Comments closed

Explaining FIRST_VALUE() and LAST_VALUE() Execution Plans

Published 2024-01-22 by Kevin Feasel

Hugo Kornelis wraps up a mini-series on window functions:

In part twenty-eight of the plansplaining series, I’ll wrap up the six-part mini-series on window functions. After covering the basics, fast-track optimization, window frames ending at UNBOUNDED FOLLOWING, window frames specified with RANGE instead of ROWS, and LAG and LEAD, we will look at the LAST_VALUE and FIRST_VALUE analytical functions, and find that a function we would have expected to be available as an internal aggregate function does not exist at all! We’ll also find out how SQL Server works around that.

Click through to unravel that mystery.

Comments closed

Fixing Non-SARGable Predicates

Published 2024-01-22 by Kevin Feasel

Erik Darling makes the donuts:

Okay, deep breath. Deep, deep breath.

Would you believe I still have to fix queries with a bunch of functions in joins and where clauses? Like every day? All day?

Where things get tough is when there’s some third party vendor app where code can’t be changed, but customers have become so fed up with performance that they’re willing to make other changes to help things along.

Read on for an example of one scenario in which reversing an index can help improve performance without touching the code.

Comments closed

Hash Aggregates and Hash Joins in Postgres

Published 2024-01-19 by Kevin Feasel

Muhammad Ali plays matchmaker:

PostgreSQL employs various techniques for data joining and aggregation in its queries, among which the hash-based method stands out for its efficiency in particular situations and different data sizes. We will discuss hash joins and hash aggregates in PostgreSQL, providing insights on how they work and parameters which influence this algorithm.

Read on to learn more. This looks fundamentally similar to hash matches in SQL Server, so if you’re familiar with that, the concepts should be pretty clear.

Comments closed