Press "Enter" to skip to content

Category: Query Tuning

Working around Unparameterized IN Clauses with EF

Published 2021-08-17 by Kevin Feasel

Erik Darling bears the bad news:

If you’re using an Entity Framework, and sending in queries that build up IN clauses, they won’t end up getting parameterized.

Even Forced Parameterization won’t help you if you’re sending in other parameters. One limitation is that it doesn’t kick in for partially parameterized queries.

Even if they did get parameterized, well, what’s one funny thing about IN clauses? You don’t know how many values will be in them.

Read on for a couple of work-arounds for this.

Comments closed

When Query Cost Goes Astray

Published 2021-08-11 by Kevin Feasel

Erik Darling warns that man cannot live on query cost alone:

There are also rather misguided efforts to figure out parallelism settings based on plan costs. The main problem with that being that if you currently have a lot of parallel queries, all that means is that the estimated cost of the serial plan was higher than your current Cost Threshold For Parallelism setting, and the cost of the parallel plan was less than the cost of the serial plan.

If you increase Cost Threshold For Parallelism, you may very well still end up with a parallel plan, because the serial version was still more expensive. If you eventually change Cost Threshold For Parallelism to the point where some queries are no longer eligible for parallelism, you may eventually find yourself unhappy with the performance of the serial version of the query plan.

Read on for Erik’s take on query cost, which is a good one.

Comments closed

Indexes and Sorts

Published 2021-08-06 by Kevin Feasel

Chad Callihan reminds us that sort order can matter for indexes:

When you’re working on an index for a query ordering by one column in ascending order and another column in descending order, do you have your index created to match? Did you know you can specify ASC or DESC in an index? Let’s go through a scenario where ordering in an index makes a noticeable difference.

This is particularly important for window functions—the optimizer can sometimes be smart enough to recognize that a value is in reverse order and not need to use a sort operator, but as soon as you drop that OVER() clause in, if things aren’t in the exact order they need, you get a sort operator thrown in for free. Or, well, the “your query is now a little bit slower” version of free.

Comments closed

The Costs and Benefits of Dirty Reads

Published 2021-08-03 by Kevin Feasel

Chad Callihan explains what a dirty read is and does a cost-benefit analysis on it:

When you are not careful with your transaction isolation levels or you get sneaky with the NOLOCK hint, one problem you can encounter is a dirty read. Let’s look at a short example to demonstrate a dirty read.

In a vacuum, I’m not necessarily opposed to the idea of dirty reads because you can find legitimate cases in which they can be useful. In practice, I’m generally very much in opposition because of two reasons: first, Read Committed Snapshot Isolation eliminates the majority of those reasons; and second, because the misuse is almost always in the direction of over-use of NOLOCK hints.

Comments closed

The Downside Risk of Index Hints

Published 2021-07-19 by Kevin Feasel

Chad Callihan explains why you should be careful before deploying code which uses index hints:

This might be good enough…for now. The potential issues with index hints can be more about the future than the present. You might come along later on and think “why not use an index to cover the whole query?” We can add the index:

But if our query is still written to include the index hint (in a stored procedure for example) the new index is not going to matter. The old index is still forced to be used. Even if something better comes along, you’re going to need to modify the query in addition to adding the better index. If an index was added for a completely separate query but would also be an improvement for the query in question, it’s also not going to get by the index hint.

Click through for additional problems which can crop up as you use index hints. This isn’t a big argument against using them at all, but rather understanding (and remembering!) where you do use them and making sure that’s communicated well to the entire team, including future you.

Comments closed

Queues and Watermarks

Published 2021-07-01 by Kevin Feasel

Forrest McDaniel wants a zippier queue in SQL Server:

I recently had the pleasure of investigating a procedure that pulled from a queue. Normally it was fast, but occasionally runtime would spike. The spooky thing was the query was using an ordered index scan that should only read one row, but during the spikes it was reading thousands.

Surely there’s a rational explanation…

Spoilers: there was. And Forrest a’int afraid of no ghosts.

(sotto voce – I’m so glad that Forrest didn’t sneak in any Ghostbusters references so that I could do that here and be original.)

Comments closed

Finding Eager Index Spools

Published 2021-06-29 by Kevin Feasel

Erik Darling hunts the most dangerous prey of all:

I’ve written a bunch about Eager Index Spools, and how to fix them, but I’ve always sort of left the “finding” part up to you, or pointed curious tuners to tools like sp_BlitzCache.

Recently though, I worked with a client who had Eager Index Spools so frequently that we needed to track them down specifically.

This is the plan cache query that I used to do it — they didn’t have Query Store enabled — and I wanted to share it.

Click through for the query.

Comments closed