Execution Plans

Why Order Isn’t Guaranteed Without an ORDER BY

Start with the Stack Overflow database, with no nonclustered indexes. If you’ve been playing around with demos, run my DropIndexes proc just to clear things out, which leaves the clustered indexes in place. Say you run this query: Transact-SQL SELECT TOP 100 Id, DisplayName FROM dbo.Users; 12 SELECT TOP 100 Id, DisplayName  FROM dbo.Users; By default,…
Read More

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

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