Over and over again, I run into applications that don’t parameterize their queries, and as a result:
- Compilations/sec is nearly as high as Batch Requests/sec
- CPU is high due to all those compilations
- Plans rarely last for more than a few hours in the cache
- Monitoring tools that track resource-intensive queries are useless because every query looks “unique” to them
- Optimize for Ad-Hoc Workloads doesn’t help because compilations still cause high CPU, and the queries aren’t grouped in the plan cache (and age out so quickly)
Normally, this is where Forced Parameterization saves the day. Hit this one switch, and bam, CPU falls down to nothing. I have a love/hate relationship with Forced Parameterization, though, because there is a long list of scenarios where Forced Parameterization doesn’t work:
- INSERT…EXECUTE statements.
- Statements inside the bodies of stored procedures, triggers, or user-defined functions. SQL Server already reuses query plans for these routines.
- Statements that contain XQuery method calls, where the method appears in a context where its arguments would typically be parameterized, such as a WHERE clause. If the method appears in a context where its arguments would not be parameterized, the rest of the statement is parameterized.
- Statements inside a Transact-SQL cursor. (SELECT statements inside API cursors are parameterized.)
- Deprecated query constructs.
- Any statement that is run in the context of ANSI_PADDING or ANSI_NULLS set to OFF.
- Statements that contain more than 2,097 literals that are eligible for parameterization.
- Statements that reference variables, such as WHERE T.col2 >= @bb.
- Statements that contain the RECOMPILE query hint.
- Prepared statements that have already been parameterized on the client-side application.
- (And more, but I’ll stop there)
That last one struck me recently, and in a way I wasn’t expecting. The code was partially parameterized, like this contrived example:
DECLARE @StringToExecute NVARCHAR(500);
SET @StringToExecute = N'SELECT * FROM dbo.Users WHERE Age = @Age AND Reputation = 0;';
EXEC sp_executesql @StringToExecute, N'@Age INT', 99;
Note that the @Age one is parameterized, but the Reputation value is not. SQL Server looks at the string and says, “Well, it’s already parameterized – my work here is done, no need to force parameterization.” I get one plan in the cache per hard-coded Reputation value:
Diabolical. The moral of the story: either parameterize EVERYTHING in your strings, or don’t parameterize ANYTHING.
Believe it or not, it gets worse. Some of the queries were built with dynamic SQL, but didn’t use the parameters – they just reused the same framework to build the strings. So they had hard-coded Reputation numbers like this, but didn’t use the @Age parameter at all:
And yet, they didn’t get parameterization forced. SQL Server just assumed the query had already been parameterized since we were using sp_executesql and passing in a parameter – even if that parameter was completely irrelevant to the query.
Just to be clear: the app involved wasn’t doing something quite this simple, but rather building lists of “in” strings in Entity Framework with a technique like this. <soapbox>Do not copy/paste code from Stack Overflow into production without analyzing the performance impact.</soapbox>
Is it a bug? I’d say yes – but not a SQL Server bug, rather a bug in the query-building app code. It doesn’t make me angry at Forced Parameterization – it’s a really cool tool, and I’m just so thankful for the times when it works. (Did that sound convincing? I hope it sounded convincing.)