Search Results for “parameter sniffing”

  1. Home
  2. Search Results

1.6 Lab 2: Fixing Parameter Sniffing Problems with Branching

Now, it’s your turn: I’ll give you a query that is experiencing parameter sniffing problems, and some examples of different parameters that seem to trigger problems. Your mission is to implement branching (to reduce the blast radius. Restore your Stack Overflow database to the starting point where you have the necessary indexes, and then run…

Read More

3.1 How Memory Grant Feedback Multiplies Parameter Sniffing

On day 1, we implemented monitoring to start collecting plans having parameter sniffing issues. To keep things simple, we kept the compatibility level at 2017. Now let’s start exploring the features that SQL Server 2019 brings to the table – in spirit, they’re supposed to help SQL Server adapt to varying amounts of data moving…

Read More

3.3 How Automatic Tuning Mitigates Parameter Sniffing

Built atop 2016’s Query Store, 2017 adds Automatic Tuning: the ability to monitor Query Store, watch for query plans that have gotten worse over time, and automatically force the better prior plans. The problem is that it only works well if there’s one plan that actually works well for enough of the possible parameters. Demo Script…

Read More

06. What Triggers Parameter Sniffing Emergencies, Part 2

There are 3 specialized causes for parameter sniffing: unparameterized queries flooding the plan cache, memory pressure due to query workspace grants, and plans aging out of the cache. Demo Script Transact-SQL /* Fundamentals of Parameter Sniffing What Triggers Parameter Sniffing Emergencies, Part 2 v1.2 – 2020-08-04 https://www.BrentOzar.com/go/snifffund This demo requires: * SQL Server 2016 or…

Read More

08. Lab: Tackle a Parameter Sniffing Emergency

The phones are ringing, the users are screaming, and your manager is dropping four-letter words. The server’s performance has tanked, and you’re the one on call. Start up a workload, find the query who suddenly got a terrible plan, and do the needful. Part 1: Setting Up the Lab Part 2: Your Mission Part 3:…

Read More

04. Lab: Write a Query that’s Susceptible to Parameter Sniffing

Now, it’s your turn: using the Stack Overflow database: Write a query that’s susceptible to parameter sniffing. Try to use just one table, and NOT the Users table. Let’s mix it up. You’ll need to create at least one index to give SQL Server tough choices. Build the list of parameters that will produce wildly…

Read More

02. The Kinds of Queries That Are Susceptible to Parameter Sniffing

In the first example, we used a single-table query that had to make a decision between: An index seek + key lookup vs a clustered index scan Asking for a single CPU core or going parallel across multiple cores Asking for a small memory grant or a large one Now we’ll review several different kinds…

Read More

07. How to React to Parameter Sniffing Emergencies

Remember the things we discussed in the last module? Restarting the service, rebuilding indexes, updating stats, and freeing the entire plan cache? Yeah, don’t do that. You solve the parameter sniffing emergency for one query, but you cause a parameter sniffing emergency for the rest of ’em – because now all the other queries are suddenly getting…

Read More

09. What Parameter Sniffing ISN’T

Some things get mistaken for parameter sniffing issues when they’re unrelated. For example, when you run the same query in two different environments and get two different plans, it might not have anything to do with the parameters at all: the data distribution might be different in the two environments, or they might have different…

Read More

05. What Triggers Parameter Sniffing Emergencies, Part 1

The biggest problem with parameter sniffing isn’t that SQL Server can produce different plans. The real problem is that the plans change unexpectedly, seemingly out of nowhere, with. no advanced warning. However, armed with an understanding of what triggers plans to be evicted from the cache, you can start to predict when these emergencies will…

Read More

01. What Parameter Sniffing Is

We’ll start by defining what parameter sniffing is: a query that builds a dramatically different plan depending on what parameters are first used to compile it. We’ll begin with a query that uses literal values, show two different parameters that produce different plans, and show what happens when it’s put into a stored procedure. Demo…

Read More

Mastering Parameter Sniffing

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 spot the common places where plans diverge: parallelism, memory grants, index…
Read More

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
Menu