SQL Server Database Parameterization option and its Query Performance effects

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.

CREATE EVENT SESSION TrackAdHocQueries ON SERVER

ADD EVENT sqlserver.query_post_compilation_showplan(

WHERE ([package0].[equal_uint64]([sqlserver].[session_id],(127)))),

ADD EVENT sqlserver.query_post_execution_showplan(

WHERE ([package0].[equal_uint64]([sqlserver].[session_id],(127)))),

ADD EVENT sqlserver.uncached_sql_batch_statistics(

ACTION(sqlserver.query_hash,sqlserver.sql_text)

WHERE ([package0].[equal_uint64]([sqlserver].[session_id],(127))))

WITH (TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)

Now, we start the extended event.

1	ALTER EVENT SESSION TrackAdHocQueries ON SERVER STATE = START

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

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.

1	SELECT W.DueDate FROM Production.WorkOrder W where ProductID <722 AND DueDate='2011-06-14 00:00:00.000' ;

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

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.

This event shows us that the executed query execution plan could not be found in the query plan cache.
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.

1	SELECT W.DueDate FROM Production.WorkOrder W where ProductID <722 AND DueDate='2011-06-14 00:00:00.000' ;

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.

SELECT cp.objtype

,cp.cacheobjtype

,cp.usecounts

,st.TEXT

,qp.query_plan

FROM sys.dm_exec_cached_plans cp

OUTER APPLY sys.dm_exec_sql_text(cp.plan_handle) st

OUTER APPLY sys.dm_exec_query_plan(cp.plan_handle) qp

WHERE st.TEXT LIKE '%Production%'

AND st.TEXT NOT LIKE '%dm_exec_cached_plans%';

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

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.

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

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.

1 2	SELECT W.DueDate FROM Production.WorkOrder W where ProductID <722 AND DueDate = '2011-06-14 00:00:00.000'

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

1 2	CREATE NONCLUSTERED INDEX IX_WorkOrder_001 ON [Production].[WorkOrder] ([DueDate],[ProductID])

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

1 2	SELECT W.DueDate FROM Production.WorkOrder W where ProductID <722 AND DueDate = '2011-06-14 00:00:00.000'

The extended event shows the parameterized statement.

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

1 2	SELECT W.DueDate FROM Production.WorkOrder W where ProductID <1022 AND DueDate = '2011-06-14 00:00:00.000'

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.

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.

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.

1	DROP INDEX [IX_WorkOrder_001] ON [Production].[WorkOrder]

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.

1	ALTER DATABASE AdventureWorks2019 SET PARAMETERIZATION FORCED

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

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

1 2	SELECT W.DueDate FROM Production.WorkOrder W where ProductID <722 AND DueDate = '2011-06-14 00:00:00.000';

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.

CREATE TABLE EmailList(

Id INT PRIMARY KEY IDENTITY(1,1)

,usermailname VARCHAR(100)

, mailadress VARCHAR(100))

DECLARE @I AS INT =1

WHILE @I<= 1000000 BEGIN IF @I%2=0 OR @I%3=0 OR @I%5=0 OR @I%7=0 BEGIN INSERT INTO EmailList

VALUES('defaultmail','defaultmail@mail.com') END ELSE BEGIN INSERT INTO EmailList

VALUES(CONCAT(@I,'mail_adress'),CONCAT(@I,'mail_adress@mail.com')) END SET @I=@I+1 END

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

1	CREATE INDEX IX_mailadress_001 ON EmailList (mailadress)

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

1	SELECT * FROM EmailList WHERE mailadress = '13mail_adress@mail.com'

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

SET STATISTICS IO ON

SELECT * FROM EmailList WHERE mailadress = 'defaultmail@mail.com'

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.

SET STATISTICS IO ON

SELECT * FROM EmailList WHERE mailadress = 'defaultmail@mail.com'

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

Conclusion

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

Author
Recent Posts

Esat Erkec

Esat Erkec is a SQL Server professional who began his career 8+ years ago as a Software Developer. He is a SQL Server Microsoft Certified Solutions Expert.

Most of his career has been focused on SQL Server Database Administration and Development. His current interests are in database administration and Business Intelligence. You can find him on LinkedIn.

View all posts by Esat Erkec