SQL Server 2012 EXECUTION PLAN warnings – Part 1

Hi Friends,

Continuing with my DENALI series, today I would like to talk about the new SQL Server 2012 EXECUTION PLAN warnings. There are many new warnings that have introduced in execution plans, primarily, to help DBAs & developers alike to identify issues in their T-SQL code.

In this post, I want to talk about implicit conversion warnings that may lead to incorrect cardinality estimation:

Let us create a temporary table in tempDB which will have some records from the customers table in Northwind2 database.

Transact-SQL

1

2

3

4

5

6

7

8

USEtempdb

GO

-- create a new table in tempdb

SELECT*INTOtempCusotmers

fromNorthwind2.dbo.Customers

wherePostalCode='51100'

Note in the above script; PostalCode column in the customers table is of type varchar and I am only inserting a single record in the new table.

Now select from the new table. Make sure you enable “Include actual execution plan” from the toolbar or press Ctrl + M.

Transact-SQL

1

2

3

-- when you execute the following code, there are no warnings becuase, there is a single row in the table.

select*fromtempCusotmers

wherePostalCode=51100

In the above code, observe that we are doing an implicit conversion of PostalCode from varchar type to int type. Now if you observe the execution plan, you will see no warnings. Primarily because there is a single record so the optimizer does not bother about any cardinality estimation errors.