Can Forced Parameterization Go Wrong?

Last Updated 4 years ago

Erik Darling

Execution Plans, Parameter Sniffing, SQL Server

App Like That

If you’ve got the kind of application that sends bare-assed strings to SQL Server, you may end up with a weird choice.

Brent will be sitting on one shoulder telling you to use Forced Parameterization.

I’ll be on the other shoulder asking if you’ve really thought this whole thing through while you ignore me.

It’s cool. That’s what this post is for.

See, one potential down side of Forced Parameterization is, well, Forced Parameter Sniffing.

Live Nude Strings

Let’s take a closer look with some demos.

This’ll get us started by creating some indexes and looping over the same query, putting in different Vote Types to search on.

USE StackOverflow2010

CREATE INDEX ix_votes ON dbo.Votes(VoteTypeId, UserId, CreationDate);
CREATE INDEX ix_posts ON dbo.Posts(OwnerUserId);
CREATE INDEX ix_badges ON dbo.Badges(UserId);

DBCC FREEPROCCACHE
DECLARE @VoteTypeId INT = 1
DECLARE @sql NVARCHAR(MAX) = N''
DECLARE @counter INT = 0

WHILE @counter < 10
	BEGIN
		WHILE @VoteTypeId <= 15
			BEGIN
				SET @sql = N'
				SELECT COUNT_BIG(DISTINCT v.PostId) AS records
				FROM dbo.Votes AS v
				WHERE VoteTypeId = ' + CONVERT(NVARCHAR(5), @VoteTypeId) + N'
				AND NOT EXISTS (SELECT * FROM dbo.Posts AS p JOIN dbo.Badges AS b ON b.UserId = p.OwnerUserId WHERE p.OwnerUserId = v.UserId)
				AND v.CreationDate >= DATEADD(YEAR, -1, ''2011-01-01'')
				'
				PRINT @sql
				EXEC(@sql)
				
				SET @VoteTypeId += 1
			END
			SET @counter += 1
			SET @VoteTypeId = 1
		END

USE StackOverflow2010

CREATE INDEX ix_votes ON dbo.Votes(VoteTypeId, UserId, CreationDate);

CREATE INDEX ix_posts ON dbo.Posts(OwnerUserId);

CREATE INDEX ix_badges ON dbo.Badges(UserId);

DBCC FREEPROCCACHE

DECLARE @VoteTypeId INT = 1

DECLARE @sql NVARCHAR(MAX) = N''

DECLARE @counter INT = 0

WHILE @counter < 10

BEGIN

WHILE @VoteTypeId <= 15

BEGIN

SET @sql = N'

SELECT COUNT_BIG(DISTINCT v.PostId) AS records

FROM dbo.Votes AS v

WHERE VoteTypeId = ' + CONVERT(NVARCHAR(5), @VoteTypeId) + N'

AND NOT EXISTS (SELECT * FROM dbo.Posts AS p JOIN dbo.Badges AS b ON b.UserId = p.OwnerUserId WHERE p.OwnerUserId = v.UserId)

AND v.CreationDate >= DATEADD(YEAR, -1, ''2011-01-01'')

PRINT @sql

EXEC(@sql)

SET @VoteTypeId += 1

END

SET @counter += 1

SET @VoteTypeId = 1

END

That finishes pretty quickly, and we can dive right into the plan cache.

EXEC sp_BlitzCache @DatabaseName = 'StackOverflow2010', @Top = 15;

1	EXEC sp_BlitzCache @DatabaseName = 'StackOverflow2010', @Top = 15;

We end up with a cached plan per Vote Type. Costs are all over the place.

Some of the plans end up being the same, but here’s a comparison between the highest and lowest cost plans.

Clearly some different choices were made.

We even have different sets of warnings…

Rather amusingly, even the estimated impact of the missing indexes swings all over the place from plan to plan.

It’s amusing because it’s always the same exact index definition.

The metrics are all different…

Some get memory, and one spills…

The point is that each query got an “appropriate” plan. Not perfect. We have some tuning to do, I think.

Casuals

I know what you’re thinking at this point: This doesn’t seem so bad. Plan cache pollution? Eh…

It gets cleared every night when you rebuild every index 500 times anyway. Who cares?

And you’re sort of right. This is a much easier problem to have than parameter sniffing.

Let’s look at how Forced Parameterization changes things.

Take Two

I’m going to do everything exactly the same, except I’m going to run this command to turn on Forced Parameterization.

ALTER DATABASE StackOverflow2010 SET PARAMETERIZATION FORCED;

1	ALTER DATABASE StackOverflow2010 SET PARAMETERIZATION FORCED;

Then I’m going to run the loop, and see what BlitzCache tells me.

The first thing I notice is that this is taking way longer to finish. The first loop finishes in around 20 seconds.

This one has been going on for a while. It ended up taking over a minute.

Now there’s only one plan in the cache.

You did nothing.

That’s the big plan from before. We can change things up a bit and start the loop with a different Vote Type — like one that has almost no usage.

I’ll clear the cache and start it with 15 — that’ll start us with the little plan from before.

DBCC FREEPROCCACHE
DECLARE @VoteTypeId INT = 15

1 2	DBCC FREEPROCCACHE DECLARE @VoteTypeId INT = 15

This one runs even longer than the last loop — it takes about a minute and a half.

Most of that seems to be due to the fact that we spilled a lot more!

TUFF LUQ

We also had some Nested Loops Joins run a pretty good chunk of times for the larger plans.

This doesn’t seem like a winning scenario.

There’s No Such Thing As A Free Feature

With databases in general, we often end up trading one problem for another.

Wanna fix plan cache pollution? Hope you like fixing parameter sniffing!

This is when people start doing all sorts of things that they think fix parameter sniffing, that really just disables it.

Stuff like recompile hints, optimize for unknown, local variables…

Could I tune queries and indexes to make our code less sensitive to parameter sniffing?

Of course I could, but if you don’t have that person, and no one wants to be that person, maybe spending a little more on RAM to hold your dirty, filthy plan cache ain’t such a bad trade.

Thanks for reading!

Thoughts On Microsoft’s Azure Outage Post-Mortem

Should You Use the New Compatibility Modes and Cardinality Estimator?

7 Comments. Leave new

Wil Hutton
September 11, 2018 9:25 am

How about Forced Parameterization. But still get the plan cache pollution? Because we get the worst of both worlds where I’m at!

Reply
- Erik Darling
  September 11, 2018 9:33 am
  
  I’ve seen that in some cases, like this one.
  
  Reply
Wil
September 11, 2018 2:22 pm

That’s actually one of the things we have happening. We basically have it all happening. Can’t say coming to work is boring 😀

Reply
Andrew Tobin
September 11, 2018 7:55 pm

In the battle of “Who to listen to” between Erik and Brent, the answer is always “What would Richie do?” Would he give Brent access to change his servers to Forced Parameterization?

Reply
André Cardoso
October 10, 2018 3:53 am

Wouldn’t the optimize for ad hoc workloads alleviate the memory pressure?

Reply
- Erik Darling
  October 10, 2018 4:01 am
  
  Andre – go ahead and read the first link in the post 😉
  
  Reply
- Brent Ozar
  October 10, 2018 5:53 am
  
  André – with really small volumes (like ad hoc workloads, where users run a few dozen queries per minute max), sure. But as you approach larger volumes, where you’re talking about hundreds or thousands of queries per second coming in, optimize for ad hoc doesn’t really help the compilations problem.
  
  Reply