Esat Erkec
SQL Server execution plan and parameterization

SQL Server Database Parameterization option and its Query Performance effects

February 18, 2022 by

The goal of this article is to give details about the database query parameterization feature and explain its effects on query performance.

Introduction

A query plan is a set of instructions that describes how the storage engine accesses the data. When we execute a query in SQL Server the first time, it compiles the query and then the query plan is generated after this compile operation. At this point, SQL Server stores the compiled query plan in the plan cache so that it can use this query plan again and again. The main idea behind the usage of the plan cache is to avoid recompilation and plan generation for queries that have an already compiled query plan. Thus, SQL Server saves resources and time during the re-execution of the queries, and this approach also reflects positively on query performances.

SQL Server ad-hoc query and plan reuse

As we stated in the introduction section, SQL Server stores compiled query plans and these plans can be reused in the second execution of the same queries. However, SQL Server needs an exact query text match for the ad-hoc queries to use their stored query plans. To monitor this behavior of the ad-hoc queries, we will use an extended event session. In this extended event session, we will use three different events that help to capture when the sample query is compiled and plan reuse has occurred.

  • The query_post_compilation_showplan event occurs after a query is compiled and returns the estimated query plan
  • The uncached_sql_batch_statistics event occurs when a Transact-SQL batch is executed that was created for a specific situation and was not in the query cache
  • The query_post_execution_showplan event occurs after the execution of a SQL statement and returns the actual execution plan of a query

The following query creates an event session that includes the above events and will also capture the events running for a single session only.

Now, we start the extended event.

In order to monitor the event data, we will choose the Watch Live Data option on the menu.

SQL Server extended event watch live data

In this step, we will execute the following sample query and we know that this query is not placed in the plan cache. For this reason, we expect that the query is compiled and it will store in the query plan cache.

As seen on the event data, three different events have occurred, and explain them line by line.

Analyzing SQL Server extended event data

  1. This event occurs because the executed query is not found in the query plan cache. For this reason, this event occurs after the SQL statement is compiled.

Tip: After clicking on this event we can find the estimated execution plan on the Query Plan tab and this option can be beneficial to analyze the query performance.

uncached_sql_batch_statistics event usage details

  1. This event shows us that the executed query execution plan could not be found in the query plan cache.
  2. This event has occurred because a query is executed and it captures the actual execution plan of a query.

Now, we will execute the same query again and re-analyze the extended event data.

query_post_execution_showplan usage details and its query performance effects

Only a single event has occurred because a compiled query plan exists in the plan cache for this query and SQL Server has decided to use it. At the same time, we can use the sys.dm_exec_cached_plans DMV to see which query plans are stored in the query plan cache. The usecounts column shows how many times this query plan is used and for our sample, it will show 2 because we execute the sample query two times.

Report the cached query plans

When we change any parameter of the sample query, the SQL Server query optimizer will generate a new query plan.

sys.dm_exec_cached_plans dmv details

The drawback of this working mechanism is it causes excessive query compilations and this situation can affect the query performance.

Now, we will add only one extra space to the end of our query and re-execute it.

Ad-hoc queries plan reuse

After rechecking the query plan, we will see that the query which has extra space.

Ad-hoc queries and query plan reuse

SQL Server Simple Parameterization

The SQL Server query optimizer may decide to parameterize some simple query plan so that any literal values that are contained in a query are replaced with parameters. This parameterized query plan is stored in the query plan cache and reused for the next execution of the same query for the different values. The default value of this option is SIMPLE and in this option, the optimizer decides which query is parameterized and not parameterized. However, we can not determine which queries are parameterized and which queries are not. For example, when we check our sample query’s actual execution plan the literal values can be seen in a clear manner.

SQL Server and missing index in the execution plan

On the other hand, the optimizer recommends an index to improve the query performance.

After creating the index the actual query plan will be changed and will perform the index seek operation to access the data.


The extended event shows the parameterized statement.

uncached_sql_batch_statistics  event details

Now we will execute the same query with a different ProductId.

In this query execution, SQL Server does not compile the query and it has used the parameterized execution plan. This case can be seen in the actual execution plan details of the query. Parameter Compiled Vales is different than the Parameter Runtime Value. Query parametrization can improve query performances because it saves compilation times.

Analyzing a SQL Server execution plan

We may ask a question about why the sample query is not parameterized before creating the index by the optimizer. Actually, this query answer is hidden in the Optimization Level of the query plan. SQL Server applies the simple parameterization feature if the plan is trivial. Trace flag 8757 that disables the use of the trivial plan. Now we will apply this trace flag to our query and re-check its actual execution plan.

SQL Server trivial query plans and query parameterization

We can see that the Optimization Level attribute is FULL therefore optimizer did not parametrize the query plan.

SQL Server Forced Parameterization

We can change the SIMPLE parameterization option of a database to FORCED. This time, the query optimizer replaces the literals with parameters, with some exceptions, during the compilation phase of the query. Enabling the forced parameterization may improve the query performance because of eliminating unnecessary query compilations. Now we will remove the index which was created on the WorkOrder table.

After dropping the index the sample query re-start to use literal values. To enable the FORCE parametrized option in a database we can use alter statement.

As an alternative method, we can right-click on the database then go to Properties, Options, Parameterization as shown below:

SQL Server database Parameterization option and query performance

To observe this case, we will enable the actual execution plan and execute the following query.

SQL Server execution plan and parameterization

As seen in the execution plan, the optimizer has decided to perform the index seek operation to find the matched rows.

SQL Server Forced Parameterization and Query Performance

The main disadvantage of forced parameterization is it may cause to choose a sub-optimal query plan for queries. To analyze this issue, we will create a very simple email table and insert 1M rows.

After populating the data, we will create an index for the mailadress column.

Now, we will execute the following query and analyze the execution plan.

SQL Server query plan index seek operator

When we execute the same query for a high-density value, a different query plan is created as can be seen below.

SQL Server query plan index scan operator

SQL Server query performance

Now, we will enable the forced parameterization option in the database and re-execute the same queries. At this time the following query execution plan will change and will perform more logical reads.

The cached query is not suitable for this query and this situation has affected the query performance.

Analyze an execution plan of a parameterized query

I/O statistics of a query

Conclusion

In this article, we have explored the query parameterization option with all aspects and analyzed the query performance effects.

Esat Erkec
2,551 Views