Auto parameterization in SQL Server

Dear Friends,

Today we are going understand auto parameterization in SQL Server. In simple words SQL Server query optimizer may return the same plan in the cases where it doesn’t matter which parameter value is supplied in a query. So query optimizer may decide to parameterize queries in cases where the specific parameter does not impact the selection of execution plan.

Query optimizer uses this option very carefully to ensure performance of the queries are not negatively impacted and when it is safe to do so. Parameterized plan can be reused by similar queries which only differs on their parameters.

As an example, following two SQL statements will produce different execution plans even though both the queries are syntactically identical and only parameters are different.

USE [AdventureWorks2012]

SELECT * FROM [Purchasing].[PurchaseOrderDetail]
WHERE PurchaseOrderDetail.ProductID = 876

SELECT * FROM [Purchasing].[PurchaseOrderDetail]
WHERE PurchaseOrderDetail.ProductID = 319

From the above queries and execution plans, query optimizer decides it isn’t safe to auto-parameterize these queries so opted to reuse an existing plan. Now, let me run a different query that will be auto-parameterized irrespective of different values.

USE [AdventureWorks2012]

SELECT * FROM HumanResources.Employee
WHERE Employee.BusinessEntityID = 250

In the above example BusinessEntityID is the primary key of Employee table so it is guaranteed to be unique. In addition query predicate using an equality operator so at maximum there will be only one record. By considering all these, query optimizer decides it is safe to auto-parameterize this plan using a Clustered Index Seek operator. We can verify the parameterized version of the plan from plan cache.

SELECT TEXT FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE TEXT LIKE '%BusinessEntityID%'

That’s all for today, watch out this space for more.

Regards,

Kanchan

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook | Follow me on Twitter | Follow me on FaceBook