Simple Parameterization Isn’t

Erik Darling ran into an interesting case with simple parameterization:

The query plan shows us that we got a Trivial Plan, and that Simple Parameterization was attempted (as shown by the 100000 literal turning into the @1 parameter.)

Simple Parameterization is only attempted in a Trivial Plan.

The key word here is attempted.

And Grant Fritchey has more:

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.

Narrator voice:  it wasn’t done.

Related Posts

Creating Temp Staging Tables to Avoid Spooling

Bert Wagner shows how you can create your own tables in tempdb to avoid eager or lazy spools: SQL Server Spool operators are a mixed bag. On one hand, they can negatively impact performance when writing data to disk in tempdb. On the other hand, they allow filtered and transformed result sets to be temporarily […]

Read More

Rewriting Expensive Updates

Erik Darling takes us through an experiment: Let’s also say that bad query is taking part in a modification. UPDATE u2SET u2.Reputation *= 2FROM Users AS uJOIN dbo.Users AS u2ON CHARINDEX(u.DisplayName, u2.DisplayName) > 0WHERE u2.Reputation >= 100000;AND u.Id <> u2.Id; This query will run for so long that we’ll get sick of waiting for it. […]

Read More

Categories

May 2018
MTWTFSS
« Apr Jun »
 123456
78910111213
14151617181920
21222324252627
28293031