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

GrphPlan_1

GrphPlan_2

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

GrphPlan_3

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%'

PlanCache

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

   

About Kanchan Bhattacharyya

Kanchan is an astute IT professional, a seasoned SQL Database Administrator with 13+ years of industry experience. A calculated risk taker with deep technical knowledge and has gained proficiency in database consulting and solutions across different environments. Kanchan holds MBA degree in IT and an International Executive MBA in Project Management. He is very passionate about SQL Server and holds MCSE (Data Platform), MCSA – SQL 2012, MCITP – SQL 2008 certifications. Currently he is focusing on cloud and latest releases of SQL Server. When not working, Kanchan likes to spend his time reading on new technical developments specifically on SQL Server and other related technologies.

View all posts by Kanchan Bhattacharyya →

Leave a Reply

Your email address will not be published.