Parameter Sniffing

PSPO: How SQL Server 2022 Tries to Fix Parameter Sniffing

Last Updated August 24, 2022

Parameter Sniffing, SQL Server 2022

Parameter sniffing is a notorious problem for Microsoft SQL Server because it tries to reuse execution plans, which doesn’t work out well for widely varying parameters. Here’s a primer for the basics about how it happens. SQL Server 2022 introduces a new feature called Parameter Sensitive Plan optimization. I’m not really sure why Microsoft capitalized…

Unusual Parameter Sniffing: Big Problems with Small Data

Last Updated February 3, 2021

Brent Ozar

Parameter Sniffing

Normally when we think about parameter sniffing, we think about a tiny data vs big data problem: the tiny data’s plan goes in first, and when we try to process big data, performance is terrible. But sometimes, it’s the exact opposite. I’ll start with the large Stack Overflow database (I’m using the 2018-06 version, but…

I Would Love a “Cost Threshold for Recompile” Setting.

Last Updated May 5, 2022

Brent Ozar

Parameter Sniffing

I’m tired of sniffing your parameters. In environments where complex queries can get bad plans due to parameter sniffing, it would help to say that all queries with an estimated cost over X should be recompiled every time they run. For example, in environments where most of my workload is small OLTP queries, I’m fine…

You Can Disable Parameter Sniffing. You Probably Shouldn’t.

Last Updated June 27, 2020

Brent Ozar

Parameter Sniffing

During my parameter sniffing classes, people get a little exasperated with the complexity of the problem. Parameter sniffing is totally hard. I get it. At some level, it’d be great to just hit a magic button and make the whole thing go away. So inevitably, somebody will ask, “What about the database-level setting called Parameter…

Updating Statistics Causes Parameter Sniffing.

Last Updated June 3, 2020

Brent Ozar

Parameter Sniffing, Statistics

In my free How to Think Like the Engine class, I explain that SQL Server builds execution plans based on statistics. The contents of your tables inform the decisions it makes about which indexes to use, whether to do seeks or scans, how many CPU cores to allocate, how much memory to grant, and much…

Parallelism Can Make Queries Perform Worse.

Last Updated May 26, 2020

Brent Ozar

Execution Plans, Parameter Sniffing

While I was building lab queries for my all-new Fundamentals of Parameter Sniffing course – first live one is next week, still time to get in – I ran across a query with delightfully terrible behavior. I’m always torn when I build the hands-on labs. I want to make the challenges easy enough that you…

How to Track Performance of Queries That Use RECOMPILE Hints

Last Updated May 26, 2020

Brent Ozar

Parameter Sniffing, Query Store (QDS), sp_BlitzCache

Say we have a stored procedure that has two queries in it – the second query uses a recompile hint, and you might recognize it from my parameter sniffing session: Transact-SQL CREATE OR ALTER PROC dbo.usp_SearchUsers @Reputation INT AS BEGIN /* Query 1, always the same: */ SELECT COUNT(*) FROM dbo.Users; /* Query 2, recompiles…

15 Reasons Your Query Was Fast Yesterday, But Slow Today

Last Updated May 26, 2020

Brent Ozar

Development, Parameter Sniffing

SQLSaturday Israel 2019 speaker shirt In rough order of how frequently I see ’em: There are different workloads running on the server (like a backup is running right now.) You have a different query plan due to parameter sniffing. The query changed, like someone did a deployment or added a field to the select list.…

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

Last Updated June 25, 2020

Brent Ozar

Execution Plans, Parameter Sniffing, SQL Server 2019, T-SQL

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…

1 query, 3 parameters, 3 different plans

Parameter Sniffing in SQL Server 2019: Adaptive Joins

Last Updated May 26, 2020

Brent Ozar

Adaptive Joins, Parameter Sniffing, SQL Server 2019

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…

Parameter Sniffing in SQL Server 2019: Air_Quote_Actual Plans

Last Updated May 26, 2020

Brent Ozar

Execution Plans, Memory Grants, Parameter Sniffing, SQL Server 2019

My last post talked about how parameter sniffing caused 3 problems for a query, and how SQL Server 2019 fixes one of them – kinda – with adaptive memory grants. However, the post finished up by talking about how much harder performance troubleshooting will be on 2019 because your query’s memory grant is based on…

Parameter Sniffing in SQL Server 2019: Adaptive Memory Grants

Last Updated May 26, 2020

Brent Ozar

Memory Grants, Parameter Sniffing, SQL Server 2019

This week, I’m demoing SQL Server 2019 features that I’m really excited about, and they all center around a theme we all know and love: parameter sniffing. If you haven’t seen me talk about parameter sniffing before, you’ll probably wanna start with this session from SQLDay in Poland. This week, I’m going to be using…

Sniffed Nulls and Magic Numbers

Last Updated May 26, 2020

Erik Darling

Parameter Sniffing, SQL Server

I Sniff Your Milkshake Building off of A Simple Stored Procedure Pattern To Avoid, I wanted to talk about a similar one that I see quite often that is not nearly as clever as one would imagine. I goes something like this: If this variable is passed in as NULL, substitute it with something else.…

Batch Mode For Row Store: Does It Fix Parameter Sniffing?

Last Updated May 26, 2020

Erik Darling

Batch Mode, Execution Plans, Indexing, Parameter Sniffing

Snorting The Future SQL Server 2019 introduced batch mode over row store, which allows for batch mode processing to kick in on queries when the optimizer deems it cost effective to do so, and also to open up row store queries to the possibility of Adaptive Joins, and Memory Grant Feedback. These optimizer tricks have…

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

Last Updated May 26, 2020

Brent Ozar

Parameter Sniffing, SQL Server 2019, T-SQL

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…

What’s New in SQL Server 2019: Adaptive Memory Grants

Last Updated May 26, 2020

Brent Ozar

Execution Plans, Memory Grants, Parameter Sniffing, SQL Server 2019

When you run a query, SQL Server guesses how much memory you’re going to need for things like sorts and joins. As your query starts, it gets an allocation of workspace memory, then starts work. Sometimes SQL Server underestimates the work you’re about to do, and doesn’t grant you enough memory. Say you’re working with…

A Simple Stored Procedure Pattern To Avoid

Last Updated May 26, 2020

Erik Darling

Execution Plans, Parameter Sniffing, SQL Server

Get Yourself Together This is one of the most common patterns that I see in stored procedures. I’m going to simplify things a bit, but hopefully you’ll get enough to identify it when you’re looking at your own code. Here’s the stored procedure: Transact-SQL CREATE OR ALTER PROCEDURE dbo.you_are_not_clever ( @cd DATETIME NULL) AS BEGIN…

Can Forced Parameterization Go Wrong?

Last Updated May 26, 2020

Erik Darling

Execution Plans, Parameter Sniffing, SQL Server

App Like That If you’ve got the kind of application that sends bare-assed strings to SQL Server, you may end up with a weird choice. Brent will be sitting on one shoulder telling you to use Forced Parameterization. I’ll be on the other shoulder asking if you’ve really thought this whole thing through while you…

What Kind Of Statistics Updates Invalidate Plans?

Last Updated May 26, 2020

Erik Darling

Parameter Sniffing, Statistics

Basics If you update statistics when the underlying objects haven’t been modified, plans won’t be invalidated. That makes total sense if your statistics update doesn’t also change the statistics. But what about when they do? That seemed like a no-brainer to me. What if you used a higher sampling percentage and got a more accurate…

Table Valued Parameters: Unexpected Parameter Sniffing

Last Updated May 26, 2020

Erik Darling

Execution Plans, Parameter Sniffing

Like Table Variables, Kinda Jeremiah wrote about them a few years ago. I always get asked about them while poking fun at Table Variables, so I thought I’d provide some detail and a post to point people to. There are some interesting differences between them, namely around how cardinality is estimated in different situations. Table…

Parameter Sniffing

Save your cart?