07May 2018 by Grant Fritchey 5 Comments

When Simple Parameterization…Isn’t

SQL Server 2017
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…
Read More
24Mar 2015 by Grant Fritchey 3 Comments

Simple Parameterization and Data Types

SQL Server, T-SQL
Simple paramaterization occurs when the optimizer determines that a query would benefit from a reusable plan, so it takes the hard coded values and converts them to a parameter. Great stuff. But... Let's take this example. Here's a very simple query: SELECT ct.* FROM Person.ContactType AS ct WHERE ct.ContactTypeID = 7; This query results in simple parameterization and we can see it in the SELECT operator of the execution plan: We can also see the parameter that was defined in use in the predicate of the seek operation: Hang on. Who the heck put the wrong data type in there that's causing an implicit conversion? The query optimizer did it. Yeah. Fun stuff. If I change the predicate value to 7000 or 700000 I'll get two more plans and I…
Read More