Search Results for “parameter sniffing”

  1. Home
  2. Search Results
  3. (Page 2)

Fundamentals of Parameter Sniffing

Sometimes the exact same query goes slow out of nowhere. Your current fix is to update statistics, rebuild indexes, or restart the SQL Server. It works, but you don’t know why. You’re facing a problem called parameter sniffing: SQL Server “sniffs” the first set of parameters for a query, builds an execution plan for it,…
Read More

Mastering Parameter Sniffing

0 customer reviews
You’ve conquered my Fundamentals of Parameter Sniffing class, and now it’s time to get to work. You know you have parameter sniffing problems in your environment – and now you need to learn how to fix ’em. In this 3-day course, you’ll learn: How to capture good, bad, and terrible plans with the plan cache and…
Read More

Fundamentals of Parameter Sniffing

0 customer reviews
Sometimes the exact same query goes slow out of nowhere. Your current fix is to update statistics, rebuild indexes, or restart the SQL Server. It works, but you don’t know why. You’re facing a problem called parameter sniffing: SQL Server “sniffs” the first set of parameters for a query, builds an execution plan for it,…
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

11 – Parameter Sniffing (19m)

“Why is this query sometimes fast and sometimes slow? I swear nothing’s changed.” You’ve heard it a million times. You’ve played around with updating stats or rebuilding indexes, and you think that fixed it – but you’re really just putting a band aid on a nasty wound. Let’s show how it happens. Demo script: Transact-SQL…

To access this incredible, amazing content, you gotta get Recorded Class Season Pass, or log in if you already shelled out the cash.
Read More

What’s New in SQL Server 2019: Faster Table Variables (And New Parameter Sniffing Issues)

For over a decade, SQL Server’s handling of table variables has been legendarily bad. I’ve long used this Stack Overflow query from Sam Saffron to illustrate terrible cardinality estimation: Transact-SQL declare @VoteStats table (PostId int, up int, down int) insert @VoteStats select PostId, up = sum(case when VoteTypeId = 2 then 1 else 0 end), down…
Read More

2.1 Queries: How to Identify and Fix Parameter Sniffing (84m)

Got a query that’s sometimes fast, and sometimes slow, and there doesn’t seem to be anything changing? Learn how parameter sniffing impacts performance, how to recognize the symptoms, and improve your code to reduce parameter sniffing problems. Part 1 (45 minutes): Part 2: (39m): In part 2, we cover how to test your code (and…

You’ll need a membership to access this course. Check out my training classes.
Read More

ColumnStore Indexes: Rowgroup Elimination and Parameter Sniffing In Stored Procedures

Yazoo Over on his blog, fellow Query Plan aficionado Joe Obbish has a Great Post, Brent® about query patterns that qualify for Rowgroup Elimination. This is really important to performance! It allows scans to skip over stuff it doesn’t need, like skipping over the dialog in, uh… movies with really good fight scenes. Car chases?…
Read More

Parameter Sniffing

Why is my query sometimes fast, and sometimes terribly slow? Even weirder, it just gets slow out of nowhere – even when you swear you haven’t changed anything. Why Is This Query Sometimes Slow? – I show you how parameter sniffing happens, and why reboots/restarts seem to fix it, but it keeps coming back. How…
Read More
Pocket Square

Parameter Sniffing

sp_BlitzCache™ Result: Parameter Sniffing You’re here because sp_BlitzCache detected queries that ran at least a few times, and they have a wide, wide variance in CPU time and/or returned number of rows. It isn’t a guarantee that the queries involved are experiencing parameter sniffing, but we’re just looking for symptoms. (We can’t even guarantee that…
Read More
Menu