SQL Server: Increase Query Performance using a Forced Parameterization

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

Since SQL Server 2005, we have a concept of Forced Parameterization of SQL Query.

It looks like a very old concept, but the use of Forced Parameterization improves the performance of Query Optimizer which responsible for the best execution plan.

SQL Server provides two types of query parameterization, one is Simple Parameterization and second is Forced Parameterization.

Why it is important and How it works?

When you are executing any query, SQL Server Query Optimizer analyzes your query and prepares the best execution plan for it.

If you are executing the same query again, previously generated execution plan may use for the same query. We can also say that It caches your query execution plan.

It is doing this using a Simple Parameterization which is default in SQL Server.

Let me add one more thing,
Your frequent SQL Query uses the cache query plan, but what If you change the value in WHERE clause.

If you are changing just column filter value in WHERE clause, It uses the cache query plan and If you are modifying your parameter list, It requires to generate a new query plan.

This is about the Simple Parameterization.

You can see in the below image:

I have executed two different queries with passing different values for TrackingEventID.

But You can see TrackingEventID = @1 in query execution plan, means It uses same query plan for both the query which increase your query performance.

Now check below image:

I have used ISNULL() in query and It skipped to put parameter in the query of execution plan.
You can see static date value instead of parameter values.

If you are executing this kind of queries frequently, It prepares and generates an execution plan every time which decreases the query performance.

Now, enable the Force Parameterization and check the same query.

You can check below image where you can see parameter values instead of static date value.

Once you enable Force Parameterization, It forcefully adds the parameter in the query of execution plan.

Which improves the query performance by reducing the cost of preparing and compiling an execution plan again and again.

But we should also take care about this because It might generate SQL Parameter Sniffing Issue so we should keep database statistics up to date.

Script to change the database in Forced Parameterization:

Script to change the database in Simple Parameterization:

Other related articles:

SQL Server: The Importance of Statistics and Why It is important

SQL Server Parameter Sniffing