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…
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.
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 with caching queries that cost under,…
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 Sniffing? Can't I just right-click on the database, go into options, and turn this damn thing off?"
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 more.
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 can accomplish 'em in the span of an hour, but hard enough that they'll ... well, challenge you.
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:
[crayon-6ac3a5a551741192417085/]
The first query will always get the same plan, but the second query will get different plans and return different numbers of rows depending on which reputation we pass in.
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. You have a different query…
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.
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:
[crayon-6ac3a5a552c1b106514565/]
(Careful readers will note that I'm using a different reputation value than I used in the last posts - hold that thought. We'll come back to that.)
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 the last set of parameters used, not the current set.
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 the queries & discussion from that session as a starting point.
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. It has a lot of variations.
[crayon-6ac3a5a5548e5468608581/]
They all have the desired effect: substituting a passed in NULL with a magic number.
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.
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:
[crayon-6ac3a5a5565a5006138001/]
It puts a bunch of data into a table variable, and then queries that same table variable. On the small StackOverflow2010 database, it takes almost a full minute, and does almost a million logical reads. Here's the plan:
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…
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:
[crayon-6ac3a5a557d17482828737/]
There's a lot of perceived cleverness in here.
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.
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?
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.
