Parameter Sensitivity and Plan Reuse

Parameter sniffing, parameterization, recompilation, and cached-plan reuse.

62 associated posts60 primary posts

Performance Tuning

New Official Documentation on Forced Parameterization

May 12, 2026

I love me some documentation.

For years, I've pointed folks to the SQL Server 2008 documentation on Forced Parameterization, a really useful tool for reducing plan cache bloat, getting more accurate reusable query plans, and enabling SQL Server 2019 and 2022's Intelligent Query Plan features, many of which rely on the same query text coming in repeatedly over time in order to tune it. That documentation was pretty sparse, though.

Read more about New Official Documentation on Forced Parameterization 5 comments — Join the discussion

How SQL Server 2025’s Optional Parameter Plan Optimization Works

May 22, 2025

About three years ago, SQL Server 2022 introduced Parameter-Sensitive Plan Optimization (PSPO). At the time, I explained that it didn't work particularly well, and went so far as to pronounce PSPO in a rather unflattering way. I wouldn't suggest that anyone turn it off - it's fine, just fine - but it isn't powerful enough, and poses serious challenges for monitoring and plan cache analysis.

Read more about How SQL Server 2025’s Optional Parameter Plan Optimization Works 15 comments — Join the discussion
T-SQL & Development

Coming in Entity Framework 9: Better Query Parameterization

May 30, 2024

Hallelujah. With current versions of Entity Framework, when developers add a mix of parameters and specific values to their query like this: [crayon-6a6eb83ebc6e2411507917/] See how part of the filter is hard-coded (".NET Blog") while the other part of the filter is dynamically generated, an ID the user is looking for? That causes Entity Framework to…

Read more about Coming in Entity Framework 9: Better Query Parameterization 22 comments — Join the discussion
T-SQL & Development

Option Recompile is a Magic Turbo Button That Actually Works.

March 12, 2024

I didn't say that - Guy Glantser did.

Guy Glantser is an Israeli SQL Server guru with a ton of great presentations on YouTube. I've had the privilege of hanging out with him in person a bunch of times over the year, and I'll always get excited to do it again. He's not just smart, but he's friendly and funny as hell.

Read more about Option Recompile is a Magic Turbo Button That Actually Works. 29 comments — Join the discussion
Performance Tuning

PSPO: How SQL Server 2022 Tries to Fix Parameter Sniffing

August 25, 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…

Read more about PSPO: How SQL Server 2022 Tries to Fix Parameter Sniffing 38 comments — Join the discussion

[Video] Office Hours: Ask Me Anything About SQL Server, Mink Edition

August 26, 2021

In this episode, I've got my own question: what's this animal running around at my feet? Commenters on the YouTube video explained that it's a mink. They were imported into Iceland in the 1930s for their fur, and they escaped out into the wild. The country's been trying to reduce their population, and they've even been part of the COVID19 news.

Read more about [Video] Office Hours: Ask Me Anything About SQL Server, Mink Edition 3 comments — Join the discussion
Performance Tuning

Can You Get Parameter Sniffing on Updates and Deletes?

January 28, 2021

Sure you can - parameter sniffing is everywhere. Anytime that you have one piece of code that needs to handle a varying number of rows, you're probably gonna have to worry about parameter sniffing. I'll start with any Stack Overflow database and run a couple of non-parameterized update statements. I'm using literals here, not parameters:…

Read more about Can You Get Parameter Sniffing on Updates and Deletes? 8 comments — Join the discussion
Performance Tuning

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

June 30, 2020

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?"

Read more about You Can Disable Parameter Sniffing. You Probably Shouldn’t. 9 comments — Join the discussion
Performance Tuning

Parallelism Can Make Queries Perform Worse.

May 20, 2020

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.

Read more about Parallelism Can Make Queries Perform Worse. 3 comments — Join the discussion
Performance Tuning

DBA Training Plan 13: Why Do Cached Plans Go Bad?

August 26, 2019

It's a little bit of a trick question: what could make a good query plan turn bad?

In the last episode, we used sp_BlitzCache to spot the most resource-intensive, longest-running query plans in your server's cache. Go run those queries again, right now, and I bet you're going to see plans in the cache that use parameters. Maybe they're stored procedures, or maybe they're parameterized SQL.

Read more about DBA Training Plan 13: Why Do Cached Plans Go Bad? 2 comments — Join the discussion