sql-serverentity-frameworkazureparameterization

SQL Forced parameterization recommendation from Azure

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?

Solution

  • 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