SQL Server offers several query optimization features designed to improve execution plan quality and overall query performance. Two of these features are Forced Parameterization and Interleaved Execution for Multi-Statement Table-Valued Functions (MSTVFs).
At first glance, these features seem unrelated. Forced Parameterization aims to increase plan reuse by replacing literal values in eligible queries with parameters. This technique reducesplan cache bloat and compilation overhead. Interleaved Execution, introduced in SQL Server 2017, addresses notoriously poor cardinality estimates associated with MSTVFs. By executing the MSTVF, and using its actual row count to continue optimization, SQL Server can generate better execution plans.
There is an interesting interaction between these two features: enabling PARAMETERIZATION = FORCED prevents Interleaved Execution from being applied to MSTVFs.
This creates a rather unpleasant trade-off. A database-level setting intended to improve plan reuse can disable an optimization feature that produce better execution plans. Depending on the workload, the resulting cardinality estimation errors can lead to suboptimal query performance.
What Is Forced Parameterization in SQL Server?
Forced Parameterization is a database-level setting in SQL Server that instructs the query optimizer to automatically parameterize eligible queries. SQL Server replaces those literals with parameters and can reuse a cached execution plan. The primary goal is to reduce unnecessary query compilations, improve execution plan reuse, and minimize plan cache bloat. You can enable it using the following command:
ALTERDATABASE[YourDatabase] SETPARAMETERIZATION FORCED [WITHNOWAIT];
Although it is beneficial for workloads with many ad-hoc queries, it also changes how SQL Server processes and optimizes statements. This configuration option has unexpected consequences when combined with features such as Interleaved Execution for Multi-Statement Table-Valued Functions (MSTVFs).
The Demo Query: Joining Customers, Orders, and an MSTVF
Let’s start with a query that joins customer and order information with the result of a Multi-Statement Table-Valued Function (MSTVF).
SELECT c.c_custkey,
c.c_name,
MAX(o.o_orderdate) AS last_order_date,
COUNT_BIG(o.o_orderkey) AS max_orders
FROM dbo.customers AS c
INNER JOIN demo.orders AS o
ON (c.c_custkey = o.o_custkey)
INNER JOIN demo.get_c_custkey_list_by_nation('AUSTRIA') AS ci
ON (c.c_custkey = ci.c_custkey)
GROUP BY
c.c_custkey,
c.c_name
ORDER BY
last_order_date,
c.c_name;
The query retrieves customer information together with the date of their most recent order and the total number of orders. To restrict the result set, it joins the customers and orders tables with the result of the MSTVF demo.get_c_custkey_list_by_nation. The interesting part for our demonstration is the function invocation:
Notice that the function receives the literal string value 'AUSTRIA' as its input parameter. This is intentional. We want SQL Server to encounter a literal value inside the query. Now that we can investigate how Forced Parameterization affects the statement. Furthermore we can check , if Interleaved Execution is still available for the MSTVF.
Running the Demo with SIMPLE Parameterization
Let’s start by executing our demo query with two different input values: 'AUSTRIA' and 'PORTUGAL'. These two values are deliberately chosen because they produce significantly different result sets when passed to the MSTVF:
- AUSTRIA: Returns 3,776 rows.
- PORTUGAL: Returns 786 rows.
With PARAMETERIZATION = SIMPLE, SQL Server does not automatically parameterize the literal value passed to the MSTVF in this scenario. Each execution is compiled independently, using the respective literal value.
Plan Cache Analysis
The SQL Server plan cache contains three entries: one compiled plan for the MSTVF demo.get_c_custkey_list_by_nation and two separate Adhoc plans for the outer query. The two Adhoc plans correspond to the executions using the literals 'AUSTRIA' and 'PORTUGAL', each with a usecounts value of 1.
Notice that the MSTVF itself has only one cached plan, with a usecounts value of 2. This demonstrates an aspect of SIMPLE parameterization: SQL Server creates separate execution plans for the two outer queries while reusing the compiled plan of the MSTVF. We get separate optimization of the outer statements without requiring multiple compiled plans for the table-valued function.
No Parameter Sniffing Between Executions
Since SQL Server generates separate execution plans for the two statements, the execution plan compiled for 'AUSTRIA' is not reused for 'PORTUGAL', and vice versa. This avoids parameter-sniffing-related issues caused by reusing a plan that was optimized for a different input value.
Accurate Cardinality Information Through Interleaved Execution
Interleaved Execution can kick in for the MSTVF. SQL Server executes the function during optimization, captures its actual number of returned rows, and uses that information when optimizing the remaining parts of the query.
As a result, the optimizer can work with the actual cardinalities of 3,776 rows for 'AUSTRIA' and 768 rows for 'PORTUGAL', rather than relying on the traditional fixed cardinality estimates associated with MSTVFs. This means that both executions can benefit from execution plans optimized with knowledge of the actual MSTVF result cardinality.
One technical nuance
The separate plans are a consequence of the different literal statements not being parameterized, rather than a general guarantee of PARAMETERIZATION = SIMPLE. Also, Interleaved Execution is subject to eligibility requirements.
So far, everything looks good: no unwanted plan reuse between the two executions and, thanks to Interleaved Execution, much better cardinality estimates for the MSTVF.
Running the Demo with FORCED Parameterization
Here we can see the impact of Forced Parameterization on the execution plans for our two test cases. The top execution uses 'AUSTRIA', which actually returns 3,776 rows from the MSTVF, while the bottom execution uses 'PORTUGAL', returning only 786 rows.
An interesting observation is that SQL Server estimates 100 rows for the MSTVF in both cases, regardless of the actual number of rows returned. An analysis of the highlighted Clustered Index Scan operators clearly expose this discrepancy.
Because Forced Parameterization prevents Interleaved Execution from being applied in this scenario, SQL Server cannot use the actual MSTVF cardinality during optimization. Instead, it falls back to the traditional fixed cardinality estimate of 100 rows.
Plan Cache Analysis
Looking at the plan cache under Forced Parameterization, we can observe an interesting difference compared to SIMPLE parameterization.
SQL Server still maintains two separate Adhoc entries for our original queries containing the literals 'AUSTRIA' and 'PORTUGAL'. Both entries have a usecounts value of 1. However, there is an additional entry that deserves our attention: the Prepared plan in line 4. This is the automatically parameterized version of our query. SQL Server has replaced the original literal values with a parameter (@0 varchar(8000)) and uses this parameterized statement for both executions.
Notice that the Prepared plan has a usecounts value of 2. This indicates that both Adhoc queries ultimately reuse the same parameterized execution plan.
The MSTVF itself also has a single cached plan (objtype = Proc), which is used by both executions.
This illustrates the fundamental difference between SIMPLE and FORCED parameterization: while SIMPLE parameterization gives us separate execution plans for the two literal values, FORCED parameterization allows SQL Server to consolidate both executions into a single reusable Prepared plan.
And this is where we could run into problems when we expect interleaved execution of the MSTVF. We gain execution plan reuse, but we also lose the ability to benefit from Interleaved Execution for the MSTVF.
Why Does FORCED Parameterization Affect Interleaved Execution?
The reason lies in how SQL Server transforms a query during compilation. When PARAMETERIZATION = FORCED is enabled, SQL Server replaces eligible literal constants with automatically generated parameters. This changes the expression tree presented to the Query Optimizer and allows syntactically equivalent statements to share a parameterized execution plan. Microsoft describes this behavior in its Query Processing Architecture Guide.
Interleaved Execution, introduced in SQL Server 2017, requires the Query Optimizer to identify an eligible MSTVF during optimization. It then pauses optimization, executes the applicable function subtree, captures the actual number of returned rows, and resumes optimization using the revised cardinality estimate. One of Microsoft’s documented eligibility requirements is that the MSTVF must use runtime constants. See Intelligent Query Processing Details.
Also, MSTVFs aren’t eligible for interleaved execution if they don’t use runtime constants.
The distinction is that Forced Parameterization changes the literal arguments into parameter references before optimization. In the observed scenario, this transformation causes the MSTVF reference not to qualify for Interleaved Execution. Consequently, SQL Server cannot use the actual cardinality of the function result during optimization and falls back to the traditional fixed estimate of 100 rows (for SQL Server 2014 and later).
This behavior illustrates an interaction between the two optimization features. Forced Parameterization improves plan reuse by consolidating statements into a shared parameterized execution plan, but the resulting parameterized expression can affect the eligibility of other optimization features. The benefit of reduced compilation overhead and plan cache consumption can therefore come at the expense of accurate cardinality estimates and potentially less efficient execution plans.
Final Thoughts and Conclusion
The comparison between PARAMETERIZATION = SIMPLE and PARAMETERIZATION = FORCED demonstrates a trade-off between execution plan reuse and the quality of cardinality estimates when working with Multi-Statement Table-Valued Functions (MSTVFs).
SIMPLE parameterization
SIMPLE parameterization has an important advantage in conjunction with Interleaved Execution. Since the two queries are compiled independently, SQL Server can execute the MSTVF during optimization and use its actual row count when generating the execution plan. This allows the optimizer to make better decisions based on the actual cardinality of the function result. The downside is that SQL Server maintains separate execution plans for statements with different literal values. In workloads with many ad-hoc queries, this can increase compilation overhead and consume additional plan cache memory.
FORCED parameterization
By automatically replacing eligible literal values with parameters, SQL Server can reuse a single execution plan for multiple query executions. This reduces the number of compiled plans in the plan cache and can significantly reduce compilation overhead.
However, as the demonstration shows, this benefit comes with a drawback when MSTVFs are involved. In our scenario, Forced Parameterization prevents Interleaved Execution from being applied. SQL Server therefore falls back to the traditional fixed cardinality estimate of 100 rows instead of using the actual number of rows returned by the MSTVF.
This can have a direct impact on execution plan quality. In particular, inaccurate cardinality estimates can influence join selection, memory grants, and the overall execution strategy. The resulting performance degradation can easily outweigh the benefits gained through reduced compilation overhead and improved plan reuse.
Neither parameterization setting is universally better. The right choice depends on the characteristics of your workload:
- SIMPLE parameterization can be beneficial when query-specific optimization and accurate cardinality estimates are important, particularly for queries that benefit from Interleaved Execution.
- FORCED parameterization can be beneficial for workloads with many ad-hoc queries, where reducing compilation overhead and plan cache consumption is a priority. However, its impact on Intelligent Query Processing features must be carefully evaluated.
My recommendation is to be particularly careful when enabling PARAMETERIZATION = FORCED at database level. Improving plan cache reuse is certainly desirable, but not if it comes at the expense of important query optimization features.
As always with SQL Server performance tuning, fewer execution plans do not automatically translate into better execution plans and better plan reuse does not necessarily mean better query performance.
Thank you for Reading!
