Execution Plans

Froid

Finding Froid’s Limits: Testing Inlined User-Defined Functions

This week, I’ve been writing about how SQL Server 2019’s bringing a few new features to mitigate parameter sniffing, but they’re more complex than they appear at first glance: adaptive memory grants, air_quote_actual plans, and adaptive joins. Today, let’s talk about another common cause of wildly varying durations for a single query: user-defined functions. Scalar…
Read More
1 query, 3 parameters, 3 different plans

Parameter Sniffing in SQL Server 2019: Adaptive Joins

So far, I’ve talked about how adaptive memory grants both help and worsen parameter sniffing, and how the new air_quote_actual plans don’t accurately show what happened. But so far, I’ve been using a simple one-table query – let’s see what happens when I add a join and a supporting index: Transact-SQL CREATE INDEX IX_OwnerUserId_Includes ON…
Read More

When Does SARGability Matter Most?

!erehT iH I know what you’re thinking. Another post about how you should just never do this one thing and all your queries will magically end up faster. Just watch the video.   For more reading, check out these posts: Sargability: Why %string% Is Slow Optional Parameters and Missing Index Requests Computed Columns: Reversing Data…
Read More

Never Judge A Query By Its Cost

Execution Plans
7 Comments
Signs and Numbers When tuning queries, or even finding queries to tune, there’s a rather misguided desire to look for queries with a high cost, or judge improvement by lowering query cost. The problem is that no matter what you’re looking at, costs are estimates, and often don’t reflect how long a query runs for…
Read More

Getting Sneaky With Forced Parameterization

Execution Plans
0
Silly Rules I’ve blogged about some of the silly rules about where Forced Parameterization doesn’t work. One rule that really irked me is this one: The TOP, TABLESAMPLE, HAVING, GROUP BY, ORDER BY, OUTPUT…INTO, or FOR XML clauses of a query. TOP and FOR XML, get used, like, everywhere. TOP is pretty obvious in its usage.…
Read More

Not So Forced Parameterization

Execution Plans
0
Asking The Wrong Question Sometimes, when you wanna turn on a feature, you spend so much time wondering if you should, you don’t bother asking if it’ll even work when you do. There are a long list of things that are incompatible with Forced Parameterization, on a page that’s pretty hard to find. Now, there’s…
Read More

Adventures In Foreign Keys 3: Why Cascading Deletes Perform Slowly

Legendreary In the last post, I looked at some issues with implementing foreign keys with cascading actions. Namely that, well, it fell apart pretty quickly just trying to set up. I didn’t even get to the point where I wanted to test all those relationships. But there’s an even worse surprise waiting for you if…
Read More