Parameter sniffing, parameterization, recompilation, and cached-plan reuse.
62 associated posts60 primary posts
Those of you who follow me on Instagram, Twitter, or Facebook know I'm taking most of August off for vacation. Erika and I spent the last couple of weeks in Telluride, had a brief stop in Sacramento for SQL Saturday, and this week we're touring wineries in Napa and Sonoma. As part of recharging, I…
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…
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-6a6ece2087d84677903900/]
(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-6a6ece2091a0a124325718/]
They all have the desired effect: substituting a passed in NULL with a magic number.
Silly Rules
I've blogged about some of the silly rules about where Forced Parameterization doesn't work.
One rule that really irked me is this one:
The TOP, TABLESAMPLE, HAVING, GROUP BY, ORDER BY, OUTPUT...INTO, or FOR XML clauses of a query.
TOP and FOR XML, get used, like, everywhere.
Asking The Wrong Question
Sometimes, when you wanna turn on a feature, you spend so much time wondering if you should, you don't bother asking if it'll even work when you do.
There are a long list of things that are incompatible with Forced Parameterization, on a page that's pretty hard to find.
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.
This week, Brent, Erik, and Richie discuss small vendor apps, "Slow in the Application, Fast in SSMS?", virtualization, licensing, msdb backup history issues, implicit transactions, how to convince your boss to allow you to use sp_blitz, and more!
Here’s the video on YouTube:
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.
This week, Brent, Erik, Tara, and Richie discuss using SQL Server 2016 vs 2017 for production environment, reporting, reindexing jobs, Azure vs Amazon, parameter sniffing, creating custom alerts for agent jobs, activity monitor, troubleshooting SQL Server 2014, replication, turning off auto-updating stats on tempdb, and why Brent can't get a tan.
Here’s the video on YouTube:
Optimizer Studies
I don't have a real job, which means that if something strikes my fancy, like staring at a spot on the wall for an hour, I can do it.
Occasionally things are a bit more inspired, and so I go poking around what SMRT PPL might be up to.
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.
sp_prepare For Mediocre
You may remember me from movies like Optimize for… Mediocre? and Why You’re Tuning Stored Procedures Wrong (the Problem with Local Variables)!
Great posts, Kendra!
In part 1 and part 2 of this series, I showed you the various ways to get the application's SET options so that you can get the same execution plan as the application. If you skip that step and run the query that is timing out in the application, it could be fast in SSMS. Your SET options must match the application's in order to reproduce the slowness users are seeing.
In part 1 of this series, I showed you how to get the compiled parameter values for an execution plan that you are investigating. You've identified it as a bad execution plan. You've already fixed the production emergency by removing the bad plan from the plan cache. You're almost ready to start testing for a workaround or a solution to avoid this issue in the future.
Many of you are troubleshooting Parameter Sniffing issues the wrong way in production. In this three-part series, I'll show you how to troubleshoot it the right way:
Getting the Compiled Parameter Values
Getting the App's SET Options
Avoiding the Density Vector
Not Exactly The Catchiest Name
There's a TL;DR here, in case you don't feel like reading the whole darn thing.
Batch mode memory grant feedback works with stored procedures
It takes several runs to adjust upwards to a final number
It seems to only adjust downwards once (in this case by about 40%)
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?
