SQL Forced parameterization recommendation from Azure

Question

I am using Azure SQL database in my project and in which some same set of queries are being executed very frequently. Recently I received a performance recommendation saying - Non-Parameterized queries are causing performance issues. and are suggesting to execute the following statement in my database.

ALTER DATABASE [TestDB] SET PARAMETERIZATION FORCED

I came to know that Forced parameterization may improve the performance of certain databases by reducing the frequency of query compilations and recompilation. Also, it is known that stored procedures are executable code and are automatically cached and shared among user and it can prevent recompilations.

Please help me with below-listed questions.

1)Do turning database into Forced PARAMETERIZATION would work better than making frequently used queries into stored procedures?

2)Is it safe performing the Forced Parameterization option in my database?

score 2 · Accepted Answer · answered Jul 10 '17 at 13:35

1)Do turning database into Forced PARAMETERIZATION would work better than making frequently used queries into stored procedures?

No. Forced Parameterization is a workaround for applications that don't properly parameterize queries. It's better to use parameters for frequently-run queries, and hard-coded values where you want the plan to be based on individual value.

EG

select * 
from Orders
where CustomerId = @customerID
and Active = 1

score 1 · Answer 2 · answered Jul 10 '17 at 11:52

It's hard to say if it will work better or not without further testing, but if the advisor is telling you that enabling Parameterization will improve performance, then you should definitely try it. Here's why:

You can apply this recommendation quickly and easily by clicking on the Apply command. Once you apply this recommendation, it will enable forced parameterization within minutes on your database and it starts the monitoring process which approximately lasts for 24 hours. After this period, you will be able to see the validation report that shows CPU usage of your database 24 hours before and after the recommendation has been applied. SQL Database Advisor has a safety mechanism that automatically reverts the applied recommendation in case a performance regression has been detected.

More info here:

https://learn.microsoft.com/en-us/azure/sql-database/sql-database-advisor#parameterize-queries-recommendations

SQL Forced parameterization recommendation from Azure

2 Answers2