Whitespace, Comments, and Forced Parameterization in SQL Server

Last Updated 9 years ago

A question came up in our company chat room the other day: does the forced parameterization database setting help with plan cache bloat caused by dynamic comments or whitespace?

I love the topic of parameterization in SQL Server, probably because it’s one of those things that’s really pretty weird (but seems like it’d be straightforward). So I immediately wanted to test it out and see if SQL Server would do what I thought it would do.

Parameterization reduces compilation and plan cache bloat

For anything but the simplest queries, SQL Server takes literal values in your query literally. If you don’t parameterize the query, SQL Server won’t do it for you. That means that each of these three queries will get its own execution plan (even if the execution plans are all identical):

SELECT TOP 1 Id INTO #byebye FROM dbo.Posts WHERE Id=1
GO
SELECT TOP 1 Id INTO #byebye FROM dbo.Posts WHERE Id=2
GO
SELECT TOP 1 Id INTO #byebye FROM dbo.Posts WHERE Id=3
GO

SELECT TOP 1 Id INTO #byebye FROM dbo.Posts WHERE Id=1

SELECT TOP 1 Id INTO #byebye FROM dbo.Posts WHERE Id=2

SELECT TOP 1 Id INTO #byebye FROM dbo.Posts WHERE Id=3

If you do this a lot, it can impact performance, particularly in a busy transactional database. It costs CPU to compile each query and SQL Server has to work much harder to manage an execution plan cache which is churning through frequent queries that are never-reused.

(For super-simple queries, simple parameterization may be applied. This is also sometimes called “auto parameterization” by people who worked with SQL Server 2000.)

White space, formatting, and comments can increase compilation and plan cache bloat for parameterized queries

I’m going to use spaces as examples in this post, but basically this is just a shorthand for talking about any variance in the syntax for executing a query.

Basically, these three queries will each get their own execution plan in SQL Server because the string being executed is different– even though it’s only different inside of a comment bracket, and even though the difference is simply the number of spaces in that comment:

SELECT TOP 1 Id /* Comment =   */ INTO #byebye FROM dbo.Posts WHERE Id=@Id
GO
SELECT TOP 1 Id /* Comment =    */ INTO #byebye FROM dbo.Posts WHERE Id=@Id
GO
SELECT TOP 1 Id /* Comment =     */ INTO #byebye FROM dbo.Posts WHERE Id=@Id
GO

SELECT TOP 1 Id /* Comment = */ INTO #byebye FROM dbo.Posts WHERE Id=@Id

The differing format in the whitespace give you just as much compilation burn and plan cache bloat as if the query were not parameterized.

‘Forced parameterization’ reduces execution plan bloat for non-parameterized queries

You can’t always re-write your whole application. The ‘forced parameterization’ database setting can help out sometimes — it tells SQL Server to look for those literal values and to try to treat them like parameters. This database setting can dramatically reduce compilation work by enabling execution plan reuse in some databases.

Like anything else, it has some limitations:

It impacts the whole database. If you only want to do individual queries, you have to use Plan Guides.
It won’t do anything if the database compatibility level is set to 80. (But plan guides will work.)

The Question: Does the ‘forced parameterization’ setting help with whitespace in query strings?

If SQL Server is smart enough to find those parameters in the string and remove them, shouldn’t it be smart enough to find the whitespace / dynamic comments and ignore them, too?

To figure this out I wrote up some test TSQL and executed it against a restored copy of the StackOverflow database.

Forced parameterization test: non-parameterized queries

For this test, I set up some simple code to execute non-parameterized dynamic SQL which also had different whitespace for each query. I ran the query 1,000 times in a loop, and then checked the execution plan cache to see how many copies of the query I had. Here’s what the code looks like:

/* This code is for test systems only, not production */
ALTER DATABASE StackOverflow SET PARAMETERIZATION FORCED;
GO

USE StackOverflow;
GO

DBCC FREEPROCCACHE;
GO

SET NOCOUNT ON;

DECLARE
    @dsql NVARCHAR(MAX)= N'SELECT TOP 1 Id /* Comment = %REPLACE-COMMENT% */ INTO #byebye FROM dbo.Posts WHERE Id=%REPLACE-ID%',
    @dsql2 NVARCHAR(MAX)= N'',
    @i INT = 1;

WHILE @i < 1001
BEGIN
    SET @dsql2=REPLACE(@dsql,N'%REPLACE-COMMENT%',REPLICATE(N' ',@i));
    SET @dsql2=REPLACE(@dsql2,N'%REPLACE-ID%',@i);

    PRINT @dsql2;
    EXEC sp_executesql @dsql2;

    SET @i=@i+1;
END

SELECT TOP 25
	qs.[query_hash] ,
	COUNT(DISTINCT plan_handle) AS number_of_plans ,
	COUNT(DISTINCT query_plan_hash) as distinct_plans,
	SUM(qs.[execution_count]) AS execution_count
FROM     sys.dm_exec_query_stats qs
GROUP BY qs.[query_hash]
ORDER BY number_of_plans DESC;
GO

/* This code is for test systems only, not production */

ALTER DATABASE StackOverflow SET PARAMETERIZATION FORCED;

USE StackOverflow;

DBCC FREEPROCCACHE;

SET NOCOUNT ON;

DECLARE

@dsql NVARCHAR(MAX)= N'SELECT TOP 1 Id /* Comment = %REPLACE-COMMENT% */ INTO #byebye FROM dbo.Posts WHERE Id=%REPLACE-ID%',

@dsql2 NVARCHAR(MAX)= N'',

@i INT = 1;

WHILE @i < 1001

BEGIN

SET @dsql2=REPLACE(@dsql,N'%REPLACE-COMMENT%',REPLICATE(N' ',@i));

SET @dsql2=REPLACE(@dsql2,N'%REPLACE-ID%',@i);

PRINT @dsql2;

EXEC sp_executesql @dsql2;

SET @i=@i+1;

END

SELECT TOP 25

qs.[query_hash] ,

COUNT(DISTINCT plan_handle) AS number_of_plans ,

COUNT(DISTINCT query_plan_hash) as distinct_plans,

SUM(qs.[execution_count]) AS execution_count

FROM sys.dm_exec_query_stats qs

GROUP BY qs.[query_hash]

ORDER BY number_of_plans DESC;

Here’s what the queries looked like:

Forced-Parameterization-Comments-Non-Parameterized-String-Queries

Here’s how many execution plans I got:

Forced-Parameterization-Comments-Non-Parameterized-String

Forced parameterization “worked”, and it overcame the different whitespace for the queries! I had one plan run 1,000 times.

Forced parameterization test: parameterized queries

So, what if you have a parameterized query, but it has a problem with dynamic comments/ whitespace? Will turning on forced parameterization help that, too?

/* This code is for test systems only, not production */
USE master;
GO
ALTER DATABASE StackOverflow SET PARAMETERIZATION FORCED;
GO

DBCC FREEPROCCACHE;
GO

USE StackOverflow;
GO

SET NOCOUNT ON;

DECLARE @dsql NVARCHAR(MAX)= N'SELECT TOP 1 Id /* Comment = %REPLACE-COMMENT% */ INTO #byebye FROM dbo.Posts WHERE Id=@Id',
 @dsql2 NVARCHAR(MAX)= N'',
 @i INT = 1;

WHILE @i < 1001
BEGIN
 SET @dsql2=REPLACE(@dsql,N'%REPLACE-COMMENT%',REPLICATE(N' ',@i));

 PRINT @dsql2;
 EXEC sp_executesql @dsql2, N'@Id int', @Id=@i;

 SET @i=@i+1;
END

SELECT TOP 25
 qs.[query_hash] ,
 COUNT(DISTINCT plan_handle) AS number_of_plans ,
 COUNT(DISTINCT query_plan_hash) as distinct_plans,
 --MIN(qs.creation_time) AS min_creation_time ,
 --MAX(qs.last_execution_time) AS max_last_execution_time ,
 SUM(qs.[execution_count]) AS execution_count
FROM sys.dm_exec_query_stats qs
GROUP BY qs.[query_hash]
ORDER BY number_of_plans DESC;
GO

/* This code is for test systems only, not production */

USE master;

ALTER DATABASE StackOverflow SET PARAMETERIZATION FORCED;

DBCC FREEPROCCACHE;

USE StackOverflow;

SET NOCOUNT ON;

DECLARE @dsql NVARCHAR(MAX)= N'SELECT TOP 1 Id /* Comment = %REPLACE-COMMENT% */ INTO #byebye FROM dbo.Posts WHERE Id=@Id',

@dsql2 NVARCHAR(MAX)= N'',

@i INT = 1;

WHILE @i < 1001

BEGIN

SET @dsql2=REPLACE(@dsql,N'%REPLACE-COMMENT%',REPLICATE(N' ',@i));

PRINT @dsql2;

EXEC sp_executesql @dsql2, N'@Id int', @Id=@i;

SET @i=@i+1;

END

SELECT TOP 25

qs.[query_hash] ,

COUNT(DISTINCT plan_handle) AS number_of_plans ,

COUNT(DISTINCT query_plan_hash) as distinct_plans,

--MIN(qs.creation_time) AS min_creation_time ,

--MAX(qs.last_execution_time) AS max_last_execution_time ,

SUM(qs.[execution_count]) AS execution_count

FROM sys.dm_exec_query_stats qs

GROUP BY qs.[query_hash]

ORDER BY number_of_plans DESC;

Here’s what the queries looked like:
Forced-Parameterization-Comments-Parameterized-String-Queries

Here’s how many execution plans I got:

Forced-Parameterization-Comments-Parameterized-String

Oh, ouch. Everything does hash out to the exact same query hash, but I got 1 plan for each execution, 1,000 plans total. For a parameterized query, the ‘Forced Parameterization’ database setting didn’t clean up the dynamic comments / whitespace for me.

Recap: what we saw

The ‘forced parameterization’ database setting is smarter than you might think, but it doesn’t fix every problem.

For our test query, it was able to force parameterize a query even if there were comments with different amounts of whitespace in it.

However, for query that was already parameterized, it didn’t fix the issue of dynamic comments/whitespace causing extra compilation and plan cache bloat.

How to Configure Ola Hallengren’s IndexOptimize Maintenance Script

Read Committed Snapshot Isolation: Writers Block Writers (RCSI)

18 Comments. Leave new

Daniel H
December 18, 2014 9:28 am

“If you don’t parameterize the query, SQL Server won’t do it for you.” I thought that parameter sniffing would do just that – replacing hard-coded values with parameters in the cached plan?

Reply
- Kendra Little
  December 18, 2014 9:34 am
  
  Great question, Dan — this is stuff is so confusing.
  Let’s take these two queries, for example, with hardcoded values:
  SELECT DISTINCT City FROM Person.Address where StateProvinceID=80; SELECT DISTINCT City FROM Person.Address where StateProvinceID=79;
  
  Those two queries don’t have parameters, and they will each get their own execution plan. There is no parameter to be “sniffed”. You can see the plans here: https://www.brentozar.com/archive/2013/06/optimize-for-unknown-sql-server-parameter-sniffing/
  
  Reply
  - Daniel H
    December 18, 2014 9:41 am
    
    Aha, got it. Thanks! 🙂
    
    Reply
Steve LaRochelle
December 18, 2014 11:39 am

Very interesting. Was there a client case that led to this testing & discovery? I’d be interested to hear the story behind the question.

Reply
- Kendra Little
  December 18, 2014 11:43 am
  
  Oh, you know, I’m not sure. The question came up in our company chat room and I don’t really remember the context, I just thought it was interesting enough to test.
  
  Reply
Gorden
December 19, 2014 6:13 am

First I would like to thank you and all of Brent Ozar et al for what you do and do so well! I’m a big fan!

How did the last stmt in the example even execute? it looks like a string with no parameters and as such much the same as a string with hard coded values?

Reply
- Kendra Little
  December 19, 2014 8:02 am
  
  Hi Gorden,
  
  Thanks for the kind words!
  
  In the last example I only printed the “inner” code run by sp_executesql. This code snippet may help:
  
  [code]
  SET @dsql2=REPLACE(@dsql,N’%REPLACE-COMMENT%’,REPLICATE(N’ ‘,@i));
  
  PRINT @dsql2;
  EXEC sp_executesql @dsql2, N’@Id int’, @Id=@i;
  [/code]
  
  So passing in the values for the parameters was handled outside of where you see the print statement. (I just wanted to keep the printed statement relatively narrow so it would fit on the page easier and be easy-ish to read.)
  
  Reply
  - Gorden
    December 19, 2014 8:45 am
    
    I was keenly interested in this b/c prior to Sql Server we had and still have an Oracle db with an App. that used un-parameterized strings which in Oracle translated into “Hard Parse” counts through the roof. So very much the same thing here in SS land. Appreciate you nifty code now that I dived in! Thanks again. Happy Holidays!
    
    Reply
Wilfred van Dijk
December 21, 2014 3:35 pm

Any idea how the new optimizer is handling non-parameterized queries? Is it smarter, so we don’t have to use the forced parmeterization?

Reply
- Kendra Little
  December 21, 2014 4:04 pm
  
  The new cardinality estimator doesn’t automatically force-parameterize.
  
  I personally wouldn’t say that doing different plans for different literal values is a bad thing or not smart of the optimizer– having the option to leverage this can be really powerful. I actually like the way parameterization works in SQL Server (with the exception of how spacing/comments are handled, which is kind of a weird thing). But to each their own!
  
  Reply
Brett Westover
December 22, 2014 5:09 pm

Awesome explanation and demonstration as always, thank you so much Kendra!

I usually like to run your examples myself (for some reason the copy/paste and running it on my test systems really helps solidify it for me).

While doing that I noticed one issue where your syntax highlighter didn’t render the ‘less than (<)' character.

I see:

WHILE @i < 1001

instead of:

WHILE @i < 1001

Could be my computer… thought I'd mention in case its not just me.

Reply
- Kendra Little
  December 22, 2014 5:11 pm
  
  Thank you for letting you know! It’s me. And my eternal battle with wordpress. I’m going to fix it right now, hopefully it won’t creep back in!
  
  Reply
Brett Westover
December 22, 2014 5:18 pm

Confirmed, fixed from my view. You are awesome!

As you can see from my failure to escape the “wrong” HTML tags in my original comment, none of us are immune to these kinds of formatting issues!

Reply
- Kendra Little
  December 22, 2014 5:21 pm
  
  Ha! I read your comment in the wordpress comment view in the administrative panel and it actually showed the HTML, not the formatted value. WordPress works great, but code samples are just crazy. (We’re actually in process working on a new way to insert and format code samples that may be a little less painful. Maybe. Hopefully someday.)
  
  Reply
Iana R
September 29, 2015 7:34 am

Hello!

I have the following situation:
– the database has the Parametrization set to FORCE
– if i run a query like:
SELECT * FROM TableT WHERE col1 = ‘aaa’ and col2 = ‘bbb’
, where the values of ‘aaa’ and ‘bbb’ can change, it does reuse the execution plan (which is good)
– if I run a query like:
exec sp_executesql N’SELECT * FROM table WHERE col1 = ”aaa” and col2 = @colb’, N’@colb nvarchar(50)’, @colb=’bbb’
, where again the valus of “aaa” and “bbb” can change, it does not reuse the execution plan.

Is this behavior as expected? If yes, could you please explain why it does not reuse the execution plan or how could I somehow enforce it to use it?

Thank you!

Reply
- Kendra Little
  September 29, 2015 8:51 am
  
  Forced parameterization has a bunch of exceptions. One of these is that it won’t work for “Prepared statements that have already been parameterized on the client-side application.”
  
  In other words, what you’re seeing is by design.
  
  Edit: you can read all the limits here: https://technet.microsoft.com/en-us/library/ms175037(v=sql.105).aspx
  
  Reply
  - Iana R
    September 29, 2015 9:14 am
    
    Thank you very much!
    
    Reply
Pelin ATICI
February 2, 2016 11:24 am

Hello Kendra,
Forced parameterization works for white spaces, that’s right but what about capitalization changes? Shouldn’t it work for them also? When I check the exception list, I couldn’t see anything about capitalization. But according to my tests,
SELECT * FROM Person.Address WHERE ADDRESSID = 1
SELECT * FROM Person.Address WHERE AddressID = 1 are compiled twice.
What do you think about that?
Thank you.

Reply