Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

When Simple Parameterization... Isn't

DZone's Guide to

When Simple Parameterization... Isn't

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.

· Database Zone ·
Free Resource

Databases are better when they can run themselves. CockroachDB is a SQL database that automates scaling and recovery. Check it out here.

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, when you spot the change to your query string, go into the properties, and 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;

And 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 a 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.

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.

Databases should be easy to deploy, easy to use, and easy to scale. If you agree, you should check out CockroachDB, a scalable SQL database built for businesses of every size. Check it out here. 

Topics:
database ,paramaterization ,extended events

Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}