Posted on

Fake News about Auto Parameterization/Simple Parameterization

I saw a question on Twitter today that took me down a little rabbit hole. And when I go down rabbit holes, I like to blog about it! There’s a TLDR at the bottom of this post if you’re short on time.

Here’s the question:

Simple parameterization leads to insanity

The thing about simple parameterization is that it’s not simple.

This is also known as auto-parameterization, and sometimes it’s not automatic — in other words, sometimes it looks like it happens, but it doesn’t actually happen.

In Klaus Aschenbrenner’s blog post, “The Pain of Simple Parameterization“, he wrote:

In general SQL Server only auto parameterizes your SQL statements if you deal with a so-called Safe Execution Plan: regardless of the provided input parameter values, the query must always lead to the same execution plan.

There are times when SQL Server just doesn’t think it’s safe to simply parameterize your query, and that can be SUPER confusing when you’re looking at queries.

Here’s what simple parameterization looks like when it works

I run two these two statements against the BabbyNames database…

SELECT FirstName FROM ref.FirstName where FirstNameId=76682;
GO
SELECT FirstName FROM ref.FirstName where FirstNameId=86055;
GO

In the actual execution plan, I see that the literal FirstNameId values have been replaced with @1.

Further, in the properties of the leftmost operator on the plan, I see that StatementParameterization type = 2.

Click for a larger image

In Query Store and the plan cache, the text for my query gets recorded as:

(@1 int)SELECT [FirstName] FROM [ref].[FirstName] WHERE [FirstNameId][email protected]

That single plan is show as having two executions. In other words, it’s re-used.

Simple parameterization sometimes doesn’t happen… but looks kinda like it did in your execution plan

Let’s change our query a little:

SELECT FirstNameId FROM ref.FirstName where FirstName='Grant';
GO
SELECT FirstNameId FROM ref.FirstName where FirstName='Kendra';
GO

When I run these new queries, here’s what my actual execution plan looks like:

Hmmmm… this is different! I’ve got the @1 again in my query text, and it’s even in the parameter list on the left.

But notice that this time, StatementParameterizationType is 0.

Last time, that was set to 2!

This didn’t really get parameterized

If I look in Query Store and my query plan cache, I find two queries and two plans.

The queries are formatted differently, they look like this:

SELECT FirstNameId FROM ref.FirstName where FirstName='Grant'
SELECT FirstNameId FROM ref.FirstName where FirstName='Kendra'

The parameter didn’t make it in there at all.

TLDR: Sometimes, it looks like something has been simple parameterized in an actual execution plan, but it hasn’t!

Just because I see an @1 being substituted in for a literal value in that text hint at the top of a plan doesn’t mean that simple parameterization has actually happened.

And for the record, I’m not sure that this is the exact scenario Grant was asking about, because my second example here isn’t technically a ‘trivial’ plan. But I do think that this is one of many reasons to use explicit parameterization practices, and not to rely on simple or auto-parameterization, because it just doesn’t work in many cases.

If you’d like to play around with these examples, the code is in this gist.

And thank you Grant for posting this question, I needed it to get me out of a blogging dry spell!

7 thoughts on “Fake News about Auto Parameterization/Simple Parameterization

  1. Happy that my confusion helps people out. It’s got to be good for something, right?

    1. I’m slowly training myself to recognize confusion as a good thing rather than something frustrating. I expect to complete this transition in approximately 2080.

  2. […] But, the devil is in the details. My thanks to Kendra Little for digging into this for me. She spotted something I was missing. […]

  3. Is there a misprint in the “Let’s change our query a little:” example? The first query is looking for “FirstName” and the 2nd for “FirstNameID”.

    1. Yes, thanks! Fixed it.

  4. Hi Kendra,

    Do you need to do anything fancy to have the StatementParameterization being shown in the SELECT properties?

    I’m not able able to see it and I already tried with SSMS 2012 and 2017. Any ideas?

    1. I was running the most recent build of SSMS, running against SQL Server 2017. What version is the SQL Server engine that you’re using? (I suspect this was introduced in plan properties in 2014 SP2+, but haven’t tested)

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.