How to recognize forced parameterization queries in the procedure cache?

At the moment a query is started SQL Server will first analyze the query to find the best way to execute the query. This is called the execution plan. In the execution plan information is stored about for instance which index to use to retrieve the data. All execution plans are stored in the plan cache. If a new query is executed, SQL server will look if an execution plan is available for this query in the plan cache. If it is available it will be re-used. This will save the generation of the execution plan. For every execution plan SQL server counts how much time it is used. In your application you can parameterize your queries so SQL can re -use query plans. In case your application is not using parameterization, SQL Server can force parameterization on a database level. Analyzing the procedure cache can help you in optimizing the performance of your application. This blogpost will explain how you can recognize forced parameterized queries in the procedure cache.

Next example queries can be used on the AdventureWorks demo database. To download the AdventureWorks demo database click here.

First of all we will not use force paramerization:
ALTER DATABASE AdventureWorks SET PARAMETERIZATION SIMPLE

Execute next example of a parameterized query:
exec sp_executeSql N'Select * from HumanResources.Employee where ContactID = @P1' ,N'@P1 INT',@P1 = 1290
GO

Execute the same parameterized query for another value (1002)
exec sp_executeSql N'Select * from HumanResources.Employee where ContactID = @P1' ,N'@P1 INT',@P1 = 1002
GO

Now we can look in the procedure cache:
SELECT text,execution_count FROM sys.dm_exec_query_stats AS qs CROSS APPLY
sys.dm_exec_sql_text(sql_handle) CROSS APPLY
sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset) AS qp
order by text

You will get one row with an execution count of 2. Because the query uses a parameter for ContactID, the execution plan is available when the query for parameter value 1002 is executed.

The parameterized query can be recognized on the ContactID=@P1 (Remember the P)

If we do not use parameterization we have next 2 queries:

SELECT * FROM HumanResources.Employee WHERE ContactID = 1290
GO
SELECT * FROM HumanResources.Employee WHERE ContactID = 1002

The procedure cache will have 2 entries. One for every query.

Now we enable forced parameterization.

ALTER DATABASE AdventureWorks SET PARAMETERIZATION FORCED
Execute a non parameterized queries:
SELECT * FROM HumanResources.Employee WHERE ContactID = 1290

go
SELECT * FROM HumanResources.Employee WHERE ContactID = 1002

The procedure cache will have one result set:

The forced parameterization can be recognized on the
ContactID = @0. (So without the P)

Enjoy analyzing your procedure cache to further improve the performance of your application.

Photo Credit: Suchitra Prints