I think it was sometime in the last century that I mentioned I often recommend folks turn on Forced Parameterization in order to deal with poorly formed application queries that send literal rather than parameterized values to SQL Server.
And then just like a magickal that, I recommended it to someone who also has a lot of problems with Local Variables in their stored procedures.
They were curious about if Forced Parameterization would fix that, and the answer is no.
But here’s proofs. We love the proofs.
Especially when they’re over 40.
A Poorly Written Stored Procedure
Here’s this thing. Don’t do this thing. Even the index is pretty dumb, because it’s on a single column.
CREATE INDEX i ON dbo.Users (Reputation) WITH (SORT_IN_TEMPDB= ON, DATA_COMPRESSION = PAGE); GO CREATE PROCEDURE dbo.humpback ( @Reputation int ) AS BEGIN SET NOCOUNT, XACT_ABORT ON; /*i mean don't really do this c'mon*/ DECLARE @ReputationCopy int = ISNULL(@Reputation, 0); SELECT u.DisplayName, u.Reputation, u.CreationDate, u.LastAccessDate FROM dbo.Users AS u WHERE u.Reputation = @ReputationCopy; END;
If we look at the statement parameterization type, available with Query Store enabled, it returns 0. That’s true whether or not we enable simple or forced parameterization:
ALTER DATABASE StackOverflow2013 SET PARAMETERIZATION FORCED; GO EXEC dbo.humpback @Reputation = 11; GO ALTER DATABASE StackOverflow2013 SET PARAMETERIZATION SIMPLE; GO EXEC dbo.humpback @Reputation = 11; GO
For now, you’ll have to do a little more work to fix local variable problems.
Thanks for reading!
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.