Execution Plans

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

The Curse of Cursor Options

Execution Plans
12 Comments
Red Skies At Night I know it’s hard to believe, but I still see a lot of people using cursors when they shouldn’t. Other times, there’s some scary dungeon part of the code that someone wrote eons ago that no one wants to go anywhere near to fix. Sometimes there’s a decent reason, something like:…
Read More