Forced Parameterization Enabled

Blitz Result: Forced Parameterization

Some applications send all of their queries to SQL Server as unique strings rather than using parameters, like this:

SELECT * FROM dbo.Users WHERE DisplayName = 'Brent Ozar';
SELECT * FROM dbo.Users WHERE DisplayName = 'Erin Stellato';
SELECT * FROM dbo.Users WHERE DisplayName = 'Randolph West';

SELECT * FROM dbo.Users WHERE DisplayName = 'Brent Ozar';

SELECT * FROM dbo.Users WHERE DisplayName = 'Erin Stellato';

SELECT * FROM dbo.Users WHERE DisplayName = 'Randolph West';

SQL Server sees these 3 incoming strings and sees them as separate, unique queries. By default, it doesn’t bother to parse them out looking for parameters, and it doesn’t realize that all three of them are really the same query, just looking for different values.

When you turn on the database-level setting Forced Parameterization, SQL Server strips literals out of the queries, turning all 3 of them into something like this:

SELECT * FROM dbo.Users WHERE DisplayName = @p1;

1	SELECT * FROM dbo.Users WHERE DisplayName = @p1;

This means each new version of the query is recognized as the same thing, so SQL Server can start executing the query faster without compiling a new plan for it. This helps you in 3 ways:

Less CPU used to compile “new” plans (that aren’t really new anyway)
Less memory used to cache query plans that will never be seen again anyway
Better monitoring visibility since we can group queries together and see their overall impact

This sounds like a no-brainer that everyone should turn on, but it does have a dark side: you’re now vulnerable to parameter sniffing. All 3 of those queries will get the same query plan, whereas before they were getting different plans.

This part of our SQL Server sp_Blitz script checks sys.databases looking for databases where forced parameterization has been turned on. To learn more:

To Fix the Problem

This may not be a problem: it might actually be a solution! If you’re dealing with an app that just sends in all of its queries as strings, this might be saving your life – or at least your CPU power. However, if you want to change it, you can. In SQL Server Management Studio, you can right-click on each database and go into its properties to change Forced Parameterization to Disabled.

After changing this option (either enabling or disabling it), watch your CPU use carefully. If it’s helping, then CPU use will go down when forced parameterization is enabled.

SELECT d.name, 
       N'ALTER DATABASE ' + 
       QUOTENAME(d.name) + 
       N' SET PARAMETERIZATION FORCED;' AS command
FROM sys.databases AS d
WHERE d.is_parameterization_forced = 0
AND d.database_id > 4;

SELECT d.name,

N'ALTER DATABASE ' +

QUOTENAME(d.name) +

N' SET PARAMETERIZATION FORCED;' AS command

FROM sys.databases AS d

WHERE d.is_parameterization_forced = 0

AND d.database_id > 4;

Return to sp_Blitz or Ask Us Questions