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.

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.

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.

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

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

1_SQL_Server2012_DENALI_EXECUTION_PLAN_warnings_Part1

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