When Simple Parameterization…Isn’t

I’m desperately working to finish up a new version of my book on Execution Plans. We’re close, so close. However, you do hit snags. Here’s one. My editor decided to change one of my queries. I used a local variable so that I got one set of behaviors. He used a hard-coded value to get a different set. However, the really interesting thing was that his query, at least according to the execution plan, went to simple parameterization. Or did it?

Simple Parameterization

The core concept of simple parameterization is easy enough to understand. You have a trivial query using a hard-coded value like this:

SELECT *
FROM Person.Person AS p
WHERE p.BusinessEntityID = 42;

The resulting execution plan looks like this:

The initial, graphical, pointer that we’re seeing parameterization is right up there in the SELECT query. You can see that instead of p.BusinessEntityID = 42, we’re looking at [p].[BusinessEntityID]=@1. SQL Server has taken our trivial query plan and added a parameter value.

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

Let’s look at the details in the Properties of the first operator, the SELECT operator:

Normally, you spot the change to your query string, you go in to the properties and you see both (and it has to be both) a Parameter Compiled Value and a Parameter Runtime Value, you’ve got simple parameterization going on.

Or do you?

Notice the final property on the sheet, StatementParameterizationType. Honestly, I never really paid attention to that property. I knew what kind of parameterization I was seeing. I’m not running Forced Parameterization. This isn’t a parameterized query. It’s Simple Parameterization. Of course it is. All the keys are there. Change to the code. Parameter List values. Done.

Let’s go a bit deeper. Take a quick look at what’s in Query Store for this as well:

SELECT qsqt.query_sql_text,
       qsq.count_compiles,
       qsrs.count_executions,
       qsq.query_parameterization_type_desc
FROM sys.query_store_query AS qsq
    JOIN sys.query_store_query_text AS qsqt
        ON qsqt.query_text_id = qsq.query_text_id
    JOIN sys.query_store_plan AS qsp
        ON qsp.query_id = qsq.query_id
    JOIN sys.query_store_runtime_stats AS qsrs
        ON qsrs.plan_id = qsp.plan_id
WHERE qsqt.query_sql_text LIKE '%@1 tinyint%';

The results are clear:

The whole idea here is for code reuse, so if we were to modify our original query as follows:

SELECT *
FROM Person.Person AS p
WHERE p.BusinessEntityID = 24;

If we look again to Query Store, we should see the compiles stay the same and the executions go up:

Ta-da!

What’s the big deal? Let’s take a look at another query.

Not So Simple, Simple Parameterization

Let’s take another trivial query:

SELECT *
FROM Production.Product AS p
WHERE p.ListPrice = 49.99;

This query results in a very simple execution plan:

Nothing to it. Simple Parameterization, exactly as expected. Let’s check the properties:

There we go. Compile and Runtime Values, so we’re all set… StatementParameterizationType is 0… What?

What’s happening? Enter another player. Erik Darling hit similar issue and figured it out using queries against sys.dm_os_performance_counters to find that, even though we see evidence of parameterization, what we’re seeing is evidence of ATTEMPTED parameterization. I’ll link to Erik’s blog post when it comes out for the full explanation. No sense stealing his thunder. UPDATE: Link added.

Oh, and I dug like mad through the Extended Events and couldn’t find a way to track this (I did find something else about execution plans resulting from Extended Events that I really dislike, but that’s another blog post). So, when you see what looks like Simple Parameterization, make sure you’re checking the Parameterization Type to see if you’re really getting what you think you’re getting.

Conclusion

We’re dealing with the single easiest queries and execution plans in the system, trivial plans. Despite this, there are still crazy complex things going on behind the scenes. I’ve posted before about issues with simple parameterization. You should also read what Klaus Aschenbrenner has to say about simple parameterization. I think it’s safe to say, that there is literally nothing simple about SQL Server, let alone simple parameterization.

---

If you want to learn more about query tuning and indexing, and the necessary tools to make query tuning easier, I have a bunch of opportunities coming up this year where I’m teaching an all day course on the topic. Please follow the links below for your local event:

For SQLSaturday NYC on May 18, 2018. Go here to register.

My first time teaching in Indiana at SQLSaturday Indianapolis on August 10, 2018. Please go here to sign up.

For SQLSaturday Oslo on August 31, 2018. Click here right now to register.