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.

USE tempdb
GO
 
-- create a new table in tempdb
 
SELECT * INTO tempCusotmers
from Northwind2.dbo.Customers
where PostalCode = '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.

-- when you execute the following code, there are no warnings becuase, there is a single row in the table.
select * from tempCusotmers
where PostalCode = 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.

Let us try again and insert a few records.

-- lets try again with more rows..
DROP TABLE tempCusotmers
 
-- inset more records this time..
 
SELECT * INTO tempCusotmers
from Northwind2.dbo.Customers
where PostalCode IN ('12209', '05021', '05023', '68306', '67000', '28023', '13008', '05022')

After the table is created again with some records, try selecting again. (Include Actual Execution Plan should be ON)

-- now select again with implict conversion
 
select * from tempCusotmers
where PostalCode = 12209

This time you shall find a warning in the SELECT operator of the execution plan, which says that implicit conversion may affect cardinality estimation.

There are some more improvements and I shall talk about them in my next posts.