Forced Parameterization

sp_BlitzCache™ Result: Forcing Execution Plans

There are one or more queries that are bypassing SQL Server’s normal execution plan generation.

Some applications send all of their queries to SQL Server as unique strings rather than using parameters. SQL Server assumes these incoming strings are unique, so it normally doesn’t bother to parse them out looking for parameters. Enterprising DBAs may notice the large number of execution plans and enable forced parameterization at the database level. This tells SQL Server to automatically parameterize all of the queries as they are coming in.

In other scenarios, developers may provide a plan guide hint to configure SQL Server to use a specific execution plan. In these cases, a developer or DBA may have been concerned about plan stability and decided to generate a specific set of hints that will cause SQL Server to generate an optimal execution plan. Plan guides made 5-6 years ago may be completely irrelevant today.

Finally, someone may be using an option that forces query optimization like SET FORCEPLAN ON or OPTION (FORCEORDER). Both of these statements override the logic that the SQL Server query optimizer uses to produce execution plans. While that version of the execution plan was good a year ago, nothing says it’s any good today. By bossing around the execution plan, we’re less likely to get a good execution plan when the underlying data changes.

How to Fix the Problem

Test the affected queries with and without the settings that are forcing plans.

You should worry about this solution because it isn’t easy to see why your tuning efforts are not being used as effectively. You can read more about query hints and forced parameterization to help you determine the best way to solve your problem, or if it even is a problem.

SELECT  st.text,
        qp.query_plan,
        qs.*
FROM    (
    SELECT  TOP 50 *
    FROM    sys.dm_exec_query_stats
    ORDER BY total_worker_time DESC
) AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
CROSS APPLY sys.dm_exec_plan_attributes(qs.plan_handle) AS pa
WHERE   pa.attribute = 'set_options'
        AND (CAST(pa.value AS INT) & 131072 = 131072
             OR CAST(pa.value AS INT) & 4 = 4)

SELECT st.text,

qp.query_plan,

qs.*

FROM (

SELECT TOP 50 *

FROM sys.dm_exec_query_stats

ORDER BY total_worker_time DESC

) AS qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st

CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp

CROSS APPLY sys.dm_exec_plan_attributes(qs.plan_handle) AS pa

WHERE pa.attribute = 'set_options'

AND (CAST(pa.value AS INT) & 131072 = 131072

OR CAST(pa.value AS INT) & 4 = 4)

Forced Parameterization

sp_BlitzCache™ Result: Forcing Execution Plans

How to Fix the Problem

Save your cart?