When Simple Parameterization... Isn't
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.
Join the DZone community and get the full member experience.Join For Free
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?
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
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:
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.
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.
Published at DZone with permission of Grant Fritchey , DZone MVB. See the original article here.
Opinions expressed by DZone contributors are their own.