Press "Enter" to skip to content

Category: Query Tuning

Query Splitting in Entity Framework

Published 2022-07-28 by Kevin Feasel

Guy Glantser doesn’t pull punches:

Recently, while working with a customer and tuning some queries, we spotted a query that seemed odd. Something about it wasn’t right. After some more investigation, the developer recognized the query as a one generated by Entity-Framework and using the SplitQuery feature. This was new to me. It’s the first time I encountered this feature, so I went to learn about it.

Now that I know what it is and how it works, I can tell you that it’s a terrible feature in most cases. Developers should avoid using it, unless there is a good reason to use it (which I doubt).

I thought based on the title that this was something totally different. Reading what Guy has to say about it, I fully agree.

Comments closed

The Cure to Scalar Functions

Published 2022-07-25 by Kevin Feasel

Tom Zika has a cure:

In the first two parts, we have seen why the Scalar functions (UDFs) are a problem for the performance. So how do we deal with it now that we know it’s a problem?

There is only one solution:

I say we take off and nuke the entire site from orbit. It’s the only way to be sure.
— Ellen Ripley

Tom’s ideas are intriguing to me and I wish to subscribe to his newsletter.

Comments closed

Another Knock against OFFSET/FETCH

Published 2022-07-12 by Kevin Feasel

Erik Darling won’t play fetch:

The two methods we’re going to look at are OFFSET/FETCH and a more traditional ROW_NUMBER query.

As you may have picked up from the title, one will turn out better, and it’s not the OFFSET/FETCH variety. Especially as you get larger, or go deeper into results, it becomes a real boat anchor.

Click through for the details.

Comments closed

Join Removal Due to Foreign Key Constraint

Published 2022-07-12 by Kevin Feasel

David Alcock shows a performance benefit from having a foreign key constraint in place:

Foreign keys are used in database design to enforce referential integrity but they also have some performance benefits as well that you might not necessarily notice unless you’re looking into your execution plans.

Let’s take the following query using the AdventureWorks2019 sample database where I’m selecting the BusinessEntityID and JobTitle from the HumanResources.Employee table and by using an inner join I’m only returning rows that have matching values (BusinessEntityID) in both tables:

There are specific rules to table elimination but if you meet the criteria, it can save you a bit of CPU time and I/O.

Comments closed

Parameter Sensitive Plan Optimization with Branches and Local Variables

Published 2022-07-11 by Kevin Feasel

Erik Darling has some mixed news. First up, if you branch a lot:

I’ve spent a bit of time talking about how IF branches can break query performance really badly in SQL Server.

While the Parameter Sensitive Plan (PSP) optimization won’t fix every problem with this lazy coding habit, it can fix some of them in very specific circumstances, assuming:

– The parameter is eligible for PSP

– The parameter is present across IF branches

Less sanguine news if you use local variables a lot:

One fix I’ve been wishing for, or wish I’ve been fixing for, is a cure for local variables. I’d even be cool if Forced Parameterization was that cure, but you know…

Time will tell.

Though I prefer to call local variables an “Optimize for mediocre” plan hint.

Comments closed

The Power of Hash Match Joins

Published 2022-07-07 by Kevin Feasel

Jared Poche looks at hash matching:

When I began working at Microsoft, I was very much a novice at performance troubleshooting. There was a lot to learn, and hash match joins were pointed out to me multiple times as the potential cause for a given issue. So, for a while I had it in my head, “hash match == bad”. But this really isn’t the case.

Hash matches aren’t inefficient; they are the best way to join large result sets together. The caveat is that you have a large result set, and that itself may not be optimal. Should it be returning this many rows? Have you included all the filters you can? Are you returning columns you don’t need?

I might throw in one caveat about hash match joins and being the best performers for two really large datasets joining together: merge join can be more efficient so long as both sets are guaranteed to be ordered in the same way without an explicit sort operator. That last clause is usually the kicker.

Comments closed

Indexing and Parameter Sensitive Plan Optimization

Published 2022-07-07 by Kevin Feasel

Erik Darling continues a series on Parameter Sensitive Plan optimization. First up is a post on indexing:

Anyway, let’s use the example that I had started with here, to illustrate that the PSP optimization does work with a computed column, but… like any other column, indexes make all the difference.

I’m using the same example query over and over again, because a lot of the other great examples of parameter sensitivity that I have demo queries written for don’t seem to trigger it.

And sometimes there’s just nothing to do:

After seeing places where the Parameter Sensitive Plan (PSP) optimization quite stubbornly refuses to kick in, it’s somewhat amusing to see it kick in where it can’t possibly have any positive impact.

Even though some parameters are responsible for filtering on columns with highly skewed data, certain other factors may be present that don’t allow for the type of plan quality issues you might run into under normal parameter sensitivity scenarios:

This continues to be a very interesting look into one of the most-anticipated features in SQL Server 2022, as well as a necessary wet blanket for the hype.

Comments closed

The Performance Pain of User-Defined Functions

Published 2022-07-06 by Kevin Feasel

Tom Zika continues a series on why user-defined scalar functions are such a bad idea:

I’ll cover several test scenarios and analyze the performance using different monitoring tools.
The results will be for the second executions of the queries, so we have compiled and cached plans and all pages in the buffer pool.

Even if you already hate seeing scalar user-defined functions in code, the occasional reminder of how poorly they perform helps focus the mind.

Comments closed

More Notes on Parameter Sensitive Plan Optimization

Published 2022-07-05 by Kevin Feasel

Erik Darling continues a series on Parameter Sensitive Plan Optimization. First up is a missed opportunity:

If we re-run the procedure from the section up above to search for VoteTypeId 2 a second time, Memory Grant Feedback will fix the spill at the Hash Join, and bring the total execution time down to about 15 seconds.

That is an improvement, but… Look at the plan here. If VoteTypeId 2 uses a plan more suited to the number of rows it has to process, the overall time is around 4 seconds, with no need for a memory grant correction.

Second, Erik asks the pressing questions:

Under more favorable circumstances, dynamic SQL gets run, executed, and plans cached and reused with the same frequency as stored procedures.

Now, dynamic SQL isn’t exactly the same as stored procedures. There’s a lot you can do with those that just looks a mess in dynamic SQL, especially longer bits of code.

In today’s post, we’re going to look at how the Parameter Sensitive Plan (PSP) optimization works with dynamic SQL.

Read on to see if PSP works with dynamic SQL.

Comments closed

When Parameter Sensitive Plan Optimization Works

Published 2022-07-01 by Kevin Feasel

Erik Darling ends on a high note:

I’ve used this proc as an example in the past. It’s a great parameter sniffing demo.

Why is it great? Because there’s exactly one value in the Posts table that causes an issue. It causes that issue because someone hated the idea of normalization.

The better thing to do here would be to have separate tables for questions and answers. Because we don’t have those, we end up with a weird scnenario.

Read on for an example of PSP at its best.

Comments closed