Forced Parameterization Enabled

Blitz Result: Forced Parameterization

Some applications send all of their queries to SQL Server as unique strings rather than using parameters. SQL Server assumes these incoming strings are unique, so it normally doesn’t bother to parse them out looking for parameters. By turning on Forced Parameterization at the database level, we can tell SQL Server to examine each string, parameterize everything, and thereby cache similar queries with the same execution plan. This isn’t the default. This part of our SQL Server sp_Blitz script checks sys.databases looking for databases where forced parameterization has been turned on.

You can learn more about forced parameterization in the Microsoft SQL Customer Advisory Team (SQLCAT) blog post about the Top 10 Hidden Gems of SQL Server 2005. Forced parameterization is #5.

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